SQL Server Integration Services – Beyond the Wizard – Part 1

In a method not entirely unusual for me, and I'm sure many of you, I'm picking up a technology 'on-the-fly'. SQL Server Integration Services (SSIS) 2005. Since I have a fairly solid understanding of Extract-Transform-Load and other integration topics, and experience with the predecessor tool - Data Transformation Services (DTS), most of the learning curve has been the nuances of the tool.

I'm finding I'm learning it in essentially a 'refactoring' approach. I've been able to get done the work I need to, but not always in the most elegant way.

This blog series will highlight some topics that will be of interest to the intermediate developer - You've been able to build a basic data flow, import/export, or other common routine, possibly only what the import/export wizard out of SQL Server Management Studio generates. Perhaps a number of them following a similar pattern. The next task is to polish it off to make it more dynamic and maintainable on a long-term basis.

One of the first issues I noticed with my SSIS project was that the connection strings were stored in each package - This is what you will get if the results of the Import/Export Wizard are saved as an SSIS package. This export shows a routine import of a CSV file into an AdventureWorks database on the local SQL Server.




I didn't see an obvious way to move the 'Connection Managers' from the package to the project level, the actual connection string property of a Connection Manager seemed like the next bet, but couldn't find a way to dynamic-ize this. While I had seen the Data Sources container under the project - I couldn't figure out how to reference these inside a package.

To change an SSIS package from using a per-package 'Connection Manager' to using a per-project 'Data Source' (useful if many SSIS packages use the same database) involves:

• Creating a new project-level data source


• Associating each Control Flow and Data Flow Task that uses the package's Connection Manager with the package source


• Deleting the package-level connection manager
  

1. Create a Project-Level Data Source

a. Right-Click the Data Sources folder under the project

b. Choose 'New Data Source'

c. Click 'Next ->'

d. Select the appropriate Server, Database, and Authentication

e. Choose 'OK'

2. Associate each Control Flow and Data Flow Task that uses the Package's Connection Manager

a. Where it now shows 'DestinationConnectionOLEDB' (the default name)

b. Choose the drop-down -> New Connection…

c. Select the data source created in Step 1 above

d. Repeat for any task objects that reference 'DestinationConnectionOLEDB'

3. Delete the Connection Manager

To tidy things up, from the connections managers tab, delete 'DestinationConnectionOLEDB'

Summary

These steps demonstrated converting from a per-package connection manager to a data source. The Data Source can now be referenced in other packages, providing a single home for the Server Name, Database Name, and Authentication.

In this case, only the target data source - AdventureWorks was created. The source file could have been created as a data source as well, but there is less value if one side or the other of the integration is relatively unique to that package. Where I was doing multiple connections between the same databases, it would make sense to create both as Data Sources.