SQL Server Integration Services (SSIS) : Zero to Hero

This was an excellent talk on Integration Servcies for SQL 2005/2008. Integration Services is the new DTS and should be easier and quicker to use.



The Business Intelligence Development Studio





SSIS solutions are designed within part of the BI Dev Studio which is a variation of Visual Studio. The graphical approach used for DTS has been brought to SSIS but has a more Visual Studio drag and drop approach where you can just drag a connection from the toolbox into your SSIS Project.





Components of SSIS

• Control Flow - Specifies the logical flow of a package, defines the steps in a package and defines precedence amongst steps.


• Control Flow Maintenance Plan Tasks - Behind the scenes of Database Maintenance Plans.


• Control Flow Tasks (eek thats smaller than I was expecting)
• Control Flow Containers - Loops through datasets/file systems and links items and tasks together in sequences.
• Data Flow Items - Defines how data flows from source to destination and defines transformations.
• Data Flow Sources - Defines the source of data.
• Connection Manager - Defines source, destination and auxillary connections.

Converting DTS 2000 to SSIS 2005

There is a SQL Updgrade Advisor for this process and the biggest problems seem to be with encapsulation.

Execution of Packages

SSIS packages can be executed from:-

• Execution from within BIDS
• Execution using DTEXECUI
• Execution using DTEXEC
• Execution using SQL Server Agent Jobs

Each of these have advantages/disadvantages over one another. One big advantage I can see of execution through BIDS (Business Intelligence Dev Studio) is the ability to Debug your SSIS projects. This is new and should be really useful when designing and testing your SSIS projects.

DTS is dead long live SSIS apparently...