MS BI Conference 6: Chalk Talk on SQL Server Integration Services - moving from Dev to Test to Prod

This was the best session of the day for me. The guy from SQM (Solid Quality Mentors) was Spanish with a thick accent, but I found him easy enough to understand. Again, the chalk talk venue was bad, couldn't hear or see, so I sat in the front row.

 He talked about what he had learned and put into practice for SSIS packages.

At the Dev level, you treat packages like source code. They should be in source control. You edit them with BITS (Visual Studio). You test them with local or dummy databases.

At Test and Prod, you treat packages like executables, compiled code. You never edit them directly (with VS for exmaple).

He likes to store packages in the SQL Server store (essentially, in MSDB), when you deploy them from source control to Test or Prod. That way they get backed up with MSDB.

Packages should have "Package configuration" enabled, and you should use consistent naming conventions throughout - starting with the package name itself, which should include the Solution and Project name, so that when deployed, you can trace the package back to the source control system.

Usually the connection managers in packages should be consistently named - they usually represent logical names for data sources and destinations, which are configured at execution time with physical names (according to the environment).

He uses the SQL Server configuration option to store much of the package configuration in a table in the database - you can have a configuration database in each instance of SQL Server for Dev, Int, Test, Prod, and they each have their own values for configuration. So the Test Config database has config values that point logical sources and destinations to the Test source and Test destination, and so on.

Then, he adds a second configuration which is an XML configuration file, that configures the ConnectionManager for the SQL Configuration database. The XML file on the Test instance then points the package config to the Test Configuration database, and so on. You must have the xml configuration for the Config connection manager listed above the entry for the SQL Config configuration for this to work properly.

At the customer I have been working with, we used Xml configuration files and environment variables, and stored our packages in the file system instead of MSDB. We ran into a roadblock because they have both Test and Prod SQL instances on the same server, and SSIS does not have the concept of named instances. I was trying to use Xml config files like source controlled files, and that doesn't quite work. I like the ideas he came up with and I'll start implementing them when I return to that customer next week.