Slowly Changing Dimension Transformation

The Slowly Changing Dimension transformation coordinates the updating and inserting of records in data warehouse dimension tables. For example, you can use this transformation to configure the transformation outputs that insert and update records in the DimProduct table of the AdventureWorksDW2008R2 OLAP database with data from the Production.Products table in the AdventureWorks2008R2 OLTP database. Read more..

Also check this article

SQL Server Integration Services (SSIS) 15 Best Practices

Listed below are some SQL Server Integration Services (SSIS) best practices:

1.Keep it simple.

Avoid using components unnecessarily. For example:

Step 1. Declare the variable varServerDate.

Step 2. Use ExecuteSQLTask in the control flow to execute a SQL query to get the server date-time and store it in the variable

Step 3. Use the dataflow task and insert/update database with the server date-time from the variable varServerDate.

This sequence is advisable only in cases where the time difference from step 2 to step 3 really matters. If that doesn't really matter, then just use the getdate() command at step 3, as shown below:


--create table #table1 (Lap_Id int, LAP_Date datetime)

Insert into #table1 (Lap_Id, LAP_Date) values (1, getdate())2.Calling a child package multiple times from a parent with different parameter values.

When a child package is executed from a master package, the parameters that are passed from the master need to be configured in the child package. For this, you can use the ‘Parent Package Configuration’ option in the child package. But, for using the ‘Parent Package Configuration’, you need to specify the name of the ‘Parent Package Variable’ that is passed to the child package. If you want to call the same child package multiple times (each time with a different parameter value), declare the parent package variables (with the same name as given in the child package) with a scope limited to ‘Execute Package Tasks’.

SSIS allows declaring variables with the same name but the scope limited to different tasks – all inside the same package!

3.SQL job with many atomic steps.

For the SQL job that calls the SSIS packages, make multiple steps, each doing small tasks, rather than a single step doing all the tasks. In the first case, the transaction log grows too big, and if a rollback happens, it may take the full processing space of the server.

4.Avoid unnecessary typecasts.

Avoid unnecessary type casts. For example, the flat file connection manager, by default, uses the string [DT_STR] data type for all the columns. In case you want to use the actual data types, you have to manually change it. Better to change it at the source-level itself to avoid unnecessary type castings.

5.Transactions.

Usually, the ETL processes handle large volumes of data. In such a scenario, do not attempt a transaction on the whole package logic. However, SSIS supports transaction, and it is advisable to use transactions where the atomicity of the transaction is taken care of.

For example, consider a scenario where a source record is to be spitted into 25 records at the target - where either all the 25 records reach the destination or zero. In this scenario, using a transaction, we can ensure either all the 25 records reach the destination or zero.


6.Distributed transaction spanning multiple tasks.

The control flow of an SSIS package threads together various control tasks. It is possible to set a transaction that can span into multiple tasks using the same connection. To enable this, the “retainsameconnection” property of the Connection Manager should be set to “True”.

7.Limit the package names to a maximum of 100 characters.

When an SSIS package with a package name exceeding 100 chars is deployed into SQL Server, it trims the package name to 100 chars, which may cause an execution failure. So, limit the package names to a maximum of 100 characters.

8.Select * from…

Make sure that you are not passing any unnecessary columns from the source to the downstream. With the OLEDB connection manager source, using the ‘Table or View’ data access mode is equivalent to ‘SELECT * FROM ’, which will fetch all the columns. Use ‘SQL command’ to fetch only the required columns, and pass that to the downstream. At each down-stream component, filter out the unnecessary columns.

9.Sorting.

Sorting in SSIS is a time consuming operation. At the places where we know that data is coming from database tables, it’s better to perform the sorting operation at the database query itself.

10.Excel Source and 64-bit runtime.

The Excel Source or Excel Connection manager works only with the 32 bit runtime. When a package using the Excel Source is enabled for 64-bit runtime (by default, it is enabled), it will fail on the production server using the 64-bit runtime. Go to the solution property pages\debugging and set Run64BitRuntime to False.

11.On failure of a component, stop/continue the execution with the next component.

When a component fails, the property failParentonFailure can be effectively used either to stop the package execution or continue with the next component - exception - stop/continue with the next component in a sequence container. The value of the constraint connecting the components in the sequence should be set to "Completion", and the failParentonFailure property should be set to False (default).

12.Protection.

To avoid most of the package deployment error from one system to another system, set the package protection level to ‘DontSaveSenstive’.

13.Copy pasting the Script component.

Once you copy-paste a script component and execute the package, it may fail. Just open the script editor of the pasted script component, save the script, and execute the package – it will work.

14.Configuration filter – Use as a filter.

It is a best practice to use the package name as the configuration filter for all the configuration items that are specific to a package. It is especially useful when there are so many packages with package-specific configuration items. For the configuration items that are general to many packages, use a generic name.

15.Optimal use of configuration records.

Avoid the same configuration item recorded under different filter/object names. For example, if two packages are using the same connection string, you need only one configuration record. To enable this, use the same name for the connection manager in both the packages. Also, use a generic configuration filter. This is quite convenient at the time of porting from one environment to another (e.g.: from UAT to production).

Courtesy:CodeProject

SQLCat

Saving and Running Packages

Once created a SSIS package, you're probably ready to run it and see what it does. But first, let's look at the options for saving SSIS packages. When you work in BIDS, your SSIS package is saved as an XML file (with the extension dtsx) directly in the normal Windows file system. But that's not the only option. Packages can also be saved in the msdb database in SQL Server itself, or in a special area of the file system called the Package Store.

Storing SSIS packages in the Package Store or the msdb database makes it easier to access and manage them from SQL Server's administrative and command-line tools without needing to have any knowledge of the physical layout of the server's hard drive.

Saving Packages to Alternate Locations

To save a package to the msdb database or the Package Store, you use the File > Save Package As menu item within BIDS.

To store copies of the package you've developed, follow these steps.

1. Select File > Save Copy of Package.dtsx As from the BIDS menus.
2. Select SSIS Package Store as the Package Location.
3. Select the name of your test server.
4. Enter the package path.
5. Click OK.
6. Select File > Save Copy of Package.dtsx As from the BIDS menus.
7. Select SQL Server as the Package Location.
8. Select the name of your test server and fill in your authentication information.
9. Enter ExportDepartments as the package path.
10. Click OK.

Running a Package

You can run the final package from either BIDS or SQL Server Management Studio. When you're developing a package, it's convenient to run it directly from BIDS. When the package has been deployed to a production server (and saved to the msdb database or the Package Store) you'll probably want to run it from SQL Server Management Studio.

SQL Server also includes a command-line utility, dtsexec, that lets you run packages from batch files.

Running a Package from BIDS

With the package open in BIDS, you can run it using the standard Visual Studio tools for running a project. Choose any of these options:

• Right-click the package in Solution Explorer and select Execute Package.
• Click the Start Debugging toolbar button.
• Press F5.

To run the package that you have loaded in BIDS, follow these steps:

1. Click the Start Debugging toolbar button. SSIS will execute the package, highlighting the steps in the package as they are completed. You can select any tab to watch what's going on. For example, if you select the Control Flow tab, you'll see tasks highlighted, as shown in Figure. 

     2. When the package finishes executing, click the hyperlink underneath the Connection    Managers pane to stop the debugger.

    3. Click the Execution Results tab to see detailed information on the package, as shown in Figure.

All of the events you see in the Execution Results pane are things that you can create event handlers to react to within the package. As you can see, DTS issues a quite a number of events, from progress events to warnings about extra columns of data that we retrieved but never used.

Running a Package from SQL Server Management Studio

To run a package from SQL Server Management Studio, you need to connect Object Browser to SSIS.

Try It!

1. In SQL Server Management Studio, click the Connect button at the top of the Object Explorer window.
2. Select Integration Services.
3. Choose the server with Integration Services installed and click Connect. This will add an Integration Services node at the bottom of Object Explorer.
4. Expand the Stored Packages node. You'll see that you can drill down into the File System node to find packages in the Package Store, or the MSDB node to find packages stored in the msdb database.
5. Expand the File System node.
6. Right-click on the package and select Run Package. This will open the Execute Package utility, shown in Figure .

7. Click Execute.
8. Click Close twice to dismiss the progress dialog box and the Execute Package Utility.
9. Browse for the inserted data

10. Click the Execute toolbar button to verify that the package was run. You should see one entry for when the package was run from BIDS and one from when you ran it from SQL Server Management Studio.

YSR... THE LEADER

Scheduling a SSIS Package with SQL Server Agent

To schedule a SSIS package  with SQL Server Agent  please  Click here

SSIS Multicast Transformation vs Conditional Split Transformation

In a data warehousing scenario, it's not rare to replicate data of a source table to multiple destination tables, sometimes it's even required to distribute data of a source table to two or more tables depending on some condition. For example splitting data based on location etc. So how we can achieve this with SSIS? SSIS provides several built-in transformation tasks to achieve these kinds of , for details click here.

Working with SQL Server Service Broker (SSBS) When the Initiator and Target are on the Same Database

Introduction

In my previous article, I introduced you SQL Server Service Broker, what it is, how it works, what its different components are and how they are related to each other. Now it's time to roll up our sleeves and write some SSBS applications. In this article, I will be creating an application in which Initiator and Target both are in the same database. In the next couple of articles, I will be helping you to write SSBS applications if the Initiator and Target are not in the same database.

Problem Statement

There are two applications, one is Order application and the other one is Inventory application. Before accepting any order from the users, the Order application needs to make sure that the product, which is being ordered, is available in the store but for that, the Order application does not want to wait. The Order application will request to check the product stock status asynchronously and will continue doing its other work. On the other side, the Inventory application will listen to the request coming into the queue, process it and respond back to the Order application, again asynchronously.

All in one database

Depending on the arrangement of Initiator and Target, the architecture can be grouped into three categories:

                •Initiator and Target in same database

                •Initiator in one database and Target in another database on same instance

              •Initiator and Target in separate instances

In this example, I will demonstrate how to create an SSBS application if both Initiator and Target are in same database.

Though we can enable Service Broker for an existing database and create SSBS objects in it, for simplicity I will be creating a new database for this demonstration.

USE master;
GO
--Create a database for this learning session, it will help you to do 
--clean up easily, you can create SSBS objects in any existing database
--also but you need to drop all objects individually if you want to do
--clean up of these objects than dropping a single database
IF EXISTS(SELECT COUNT(1) FROM sys.databases WHERE name = 'SSBSLearning')
 DROP DATABASE SSBSLearning
GO
CREATE DATABASE SSBSLearning
GO
 
--By default a database will have service broker enabled, which you can verify 
--with is_broker_enabled column of the below resultset
SELECT name, service_broker_guid, is_broker_enabled, is_honor_broker_priority_on 
FROM sys.databases WHERE name = 'SSBSLearning'
 
--If your database is not enabled for Service Broker becuase you have 
--changed the default setting in Model database, even then you can enable
--service broker for a database with this statement
ALTER DATABASE SSBSLearning
      SET ENABLE_BROKER;
      --WITH ROLLBACK IMMEDIATE
GO
----To disable service broker for a database
--ALTER DATABASE SSBSLearning
--      SET DISABLE_BROKER;
--GO

Once you have created a database or enabled the Service Broker for an existing database, you need to create the service broker objects; first Message Types then Contracts, which will use the created message types. Then you need to create Queues and finally you would be required to create Services, which are nothing but endpoints that sit on top of queues to send and receive messages.

USE SSBSLearning;
GO
--Create message types which will allow valid xml messages to be sent
--and received, SSBS validates whether a message is well formed XML 
--or not by loading it into XML parser 
CREATE MESSAGE TYPE
       [//SSBSLearning/ProductStockStatusCheckRequest]
       VALIDATION = WELL_FORMED_XML;
CREATE MESSAGE TYPE
       [//SSBSLearning/ProductStockStatusCheckResponse]
       VALIDATION = WELL_FORMED_XML;
GO
 
--Create a contract which will be used by Service to validate
--what message types are allowed for Initiator and for Target.
--As because communication starts from Initiator hence 
--SENT BY INITIATOR or SENT BY ANY is mandatory
CREATE CONTRACT [//SSBSLearning/ProductStockStatusCheckContract]
      ([//SSBSLearning/ProductStockStatusCheckRequest]
       SENT BY INITIATOR,
       [//SSBSLearning/ProductStockStatusCheckResponse]
       SENT BY TARGET
      );
GO
 
--Create a queue which is an internal physical table to hold 
--the messages passed to the service, by default it will be 
--created in default file group, if you want to create it in 
--another file group you need to specify the ON clause with 
--this statement. You can use SELECT statement to query this 
--queue or special table but you can not use other DML statement
--like INSERT, UPDATE and DELETE. You need to use SEND and RECEIVE
--commands to send messages to queue and receive from it
CREATE QUEUE dbo.SSBSLearningTargetQueue;
GO
 
--Create a service, which is a logical endpoint which sits on top 
--of a queue on which either message is sent or received. With 
--Service creation you all specify the contract which will be
--used to validate message sent on that service
CREATE SERVICE
       [//SSBSLearning/ProductStockStatusCheck/TargetService]
       ON QUEUE dbo.SSBSLearningTargetQueue
       ([//SSBSLearning/ProductStockStatusCheckContract]);
GO
 
--A Target can also send messages back to Initiator and hence
--you can create a queue for Initiator also
CREATE QUEUE dbo.SSBSLearningInitiatorQueue;
GO
 
--Likewsie you would need to create a service which will sit 
--on top of Initiator queue and used by Target to send messages
--back to Initiator
CREATE SERVICE
       [//SSBSLearning/ProductStockStatusCheck/InitiatorService]
       ON QUEUE dbo.SSBSLearningInitiatorQueue;
GO

Once you are done creating all of the required SSBS objects for both Initiator (Order application) and Target (Inventory application), you will be sending messages between Initiator and Target. A dialog or conversation is always started by Initiator and therefore Initiator will be sending the request message first (to check the inventory stock for product as mentioned in problem statement) to the Target something
like this.

--To send message, first you need to initiate a dialog with 
--BEGIN DIALOG command and specify the Initiator and Target
--services which will be talking in this dialog conversation
DECLARE @SSBSInitiatorDialogHandle UNIQUEIDENTIFIER;
DECLARE @RequestMessage XML;
BEGIN TRANSACTION;
 BEGIN DIALOG @SSBSInitiatorDialogHandle
   FROM SERVICE
    [//SSBSLearning/ProductStockStatusCheck/InitiatorService]
   TO SERVICE
    N'//SSBSLearning/ProductStockStatusCheck/TargetService'
   ON CONTRACT
    [//SSBSLearning/ProductStockStatusCheckContract]
   WITH ENCRYPTION = OFF;
 SELECT @RequestMessage =
     N'<Request>
    <ProductID>316</ProductID>
    <LocationID>10</LocationID>
   </Request&g';
 --To send message you use SEND command and specify the dialog
 --handle which you got above after initiating a dialog  
 SEND ON CONVERSATION @SSBSInitiatorDialogHandle
   MESSAGE TYPE 
   [//SSBSLearning/ProductStockStatusCheckRequest]
   (@RequestMessage);
 SELECT @RequestMessage AS RequestMessageSent;
COMMIT TRANSACTION;
GO

The request message sent using the above statements is stored in the Target queue until it is processed. You can query the Target queue using a SELECT statement.

--You can query the Target queue with SELECT statement
SELECT * FROM dbo.SSBSLearningTargetQueue;
GO
--If in case message cannot be put into Target queue becuase
--Target queue is not enabled or because of any other reasons
--it will be temporarily put into transmission queue until 
--its delivery to Target queue
SELECT * FROM sys.transmission_queue
GO

Next the Target (Inventory application) will pick up the request messages from its queue, process it and respond back to the Initiator (Order application) with the product inventory status; at the end it will end the dialog conversation, which Initiator initiated as it is no longer required.

--To retrieve a message from a queue you use RECEIVE command,
--With every message you also get dialog handle which you can 
--use to reply back to sender of the message
DECLARE @SSBSTargetDialogHandle UNIQUEIDENTIFIER;
DECLARE @RecvdRequestMessage XML;
DECLARE @RecvdRequestMessageTypeName sysname;
BEGIN TRANSACTION;
 --WAITFOR command is used to wait for messages to arrive
 --on the queue, TIMEOUT is specified in miliseconds
 WAITFOR
 ( RECEIVE TOP(1)
  @SSBSTargetDialogHandle = conversation_handle,
  @RecvdRequestMessage = CONVERT(XML, message_body),
  @RecvdRequestMessageTypeName = message_type_name
   FROM dbo.SSBSLearningTargetQueue
 ), TIMEOUT 1000;
 
 SELECT @RecvdRequestMessage AS RequestMessageReceived;
 --If the message type is request from Initiator, process the request.
 IF @RecvdRequestMessageTypeName = N'//SSBSLearning/ProductStockStatusCheckRequest'
 BEGIN
   DECLARE @ReplyMessage NVARCHAR(max);
   DECLARE @Quantity smallint
   SELECT @Quantity = Quantity 
   FROM AdventureWorks.Production.ProductInventory
   WHERE ProductID = @RecvdRequestMessage.value('(/Request/ProductID)[1]', 'int')  
   AND LocationID = @RecvdRequestMessage.value('(/Request/LocationID)[1]', 'int') 
   SELECT @ReplyMessage = 
   N'<Reply>
   <Quantity>' + CONVERT(VARCHAR(10), @Quantity) + '</Quantity>
   </Reply>';
   --To send message back to sender you again use SEND command and specify the dialog
   --handle which you got above while retrieving the message from the queue
   SEND ON CONVERSATION @SSBSTargetDialogHandle
     MESSAGE TYPE 
     [//SSBSLearning/ProductStockStatusCheckResponse]
     (@ReplyMessage);
   --To end a dialog you use END CONVERSATION command, here the dialog
   --is being ended by Target, and then a message of 
   -- http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog message type 
   --will be sent to Initiator
   END CONVERSATION @SSBSTargetDialogHandle;
 END
 SELECT @ReplyMessage AS ReplyMessageSent;
COMMIT TRANSACTION;
GO

The response message (which Target sends) comes and resides in the Initiator queue, which you can see by querying the Initiator queue with a SELECT statement as you did for Target queue above.

--You can query the Initiator queue with SELECT statement
--Here you will see two records, one is the response and another
--one is the end dialog intimation with NULL in its message_body column
SELECT * FROM dbo.SSBSLearningInitiatorQueue
GO
--Again if there is any error during transmission for messages from Target to 
--Initiator, the messages will be temporarily put into transmission queue until 
--its delivery to Initiator queue
SELECT * FROM sys.transmission_queue
GO

At this point, the Initiator (Order application) can retrieve the product status response message from its
queue, which was written by Target (Inventory application). Please note, if you use the above statement to see the Initiator queue, you will see two records; the first one is the response message from the Target and second one message is for dialog end, which was ended by Target after sending the response. To end a
dialog successfully, it has to be ended by both Target and Initiator. Hence, you need to execute the script given below twice; the first time to process the response and the second time to end the conversation as it is already ended by Target.

--At this point the Initiator queue will hold two records, first
--one is a response message for the request and another one is for 
--intimation that dialog has been ended by the Target.
--You need to execute below piece of code twice to retrive both the 
--records from Initiator queue, if the message is of type 
-- http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog the dialog will
--be ended by Intiator also or else response message will be processed. 
DECLARE @RecvSSBSInitiatorDialogHandle UNIQUEIDENTIFIER;
DECLARE @RecvReplyMessage NVARCHAR(100);
DECLARE @RecvMessageType SYSNAME
BEGIN TRANSACTION;
 WAITFOR
 ( RECEIVE TOP(1)
  @RecvSSBSInitiatorDialogHandle = conversation_handle,
  @RecvReplyMessage = message_body,
  @RecvMessageType = message_type_name
   FROM dbo.SSBSLearningInitiatorQueue
 ), TIMEOUT 1000;
 --If the message is of type http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog 
 --the dialog will be ended by Intiator also or else response message will be processed.
 IF (@RecvMessageType = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog')
  END CONVERSATION @RecvSSBSInitiatorDialogHandle;
 ELSE
  SELECT @RecvReplyMessage AS ReplyMessageRecived;
COMMIT TRANSACTION;
GO
 

Once you are done with the testing and want to drop all the objects which you created, you can use the clean-up scripts below.

--Cleanup code to drop SSBS objects individually which 
--you created above, notice the order of dropping the objects,
--its reverse of the order in which you created the objects
DROP SERVICE [//SSBSLearning/ProductStockStatusCheck/InitiatorService]
DROP SERVICE [//SSBSLearning/ProductStockStatusCheck/TargetService]
DROP QUEUE dbo.SSBSLearningInitiatorQueue
DROP QUEUE dbo.SSBSLearningTargetQueue
DROP CONTRACT [//SSBSLearning/ProductStockStatusCheckContract]
DROP MESSAGE TYPE [//SSBSLearning/ProductStockStatusCheckRequest]
DROP MESSAGE TYPE [//SSBSLearning/ProductStockStatusCheckResponse]
GO
--Drop the database which you created above
IF EXISTS(SELECT COUNT(1) FROM sys.databases WHERE name = 'SSBSLearning')
 DROP DATABASE SSBSLearning
GO

Note: Needless to say, you must learn and do thorough testing of your SSBS application (or the scripts demonstrated above) first on your development box before going to production environment.