SQL Server Integration Services (SSIS) in Yukon

Many of us are familiar with Data Transformation Services (DTS) in SQL Server 2000.

SQL Server 2005 (code named Yukon) provides a completely new platform called SQL Server Integration Services (SSIS) that serves as the primary enterprise platform Extract, Transform and Load (ETL).

There are a looot of enhancements that has happened to DTS in Sql 2000. Creating packages is now separated to a new Development environment called Business Intelligence Development Studio (bienv.exe). This gives the same look n feel as our Visual Studio Dev Environment.
We have everything similar to VS - the Solution, Project, toolbox, setting configurations, etc.

The toolbox contains various tasks and componets that can be used in the editor.

There are 4 tabs provided in the editor:

1. Control flow - For controlling the task flow
2. Data Flow - For Data pumping tasks
3. Event handlers - For handling events and errors
4. Package Explorer - Gives a treeview view of the various components in the package
5. Execution results - For viewing the status during execution

The connections form a separate section that is common to the above four tabs. Connection manages for File, SQL Server, OLE DB can be added here.


Another cool feature is the Logging feature. All we have to do is just Right click the editor pane and enable Logging with a specific connection. All events now get logged in the specified server.

Every package that is a part of a SSIS project can be executed from BI. The deployment of these packages can be done either to the file system or to SQL Server. The packages deployed in SQL Server will be saved in the DTS Server.

My next article will throw more light on working with SSIS and programmability in Whidbey for SSIS.