One of the complains I got from many people is that it’s hard to deploy SSIS packages. It is, in some sense, comparing to compiling a stored procedure directly on a SQL server. Instead, developers need to design and develop their packages in a file system, then deploy the packages to the server (Integration Services). The process of deployment seems a mystery first. I got questions, such as, how do you configure your connections, do you hard code your user id and password in connection.

When Package Configuration not enabled

For a simple deployment without Package Configuration enabled, you can certainly go ahead put your user id and password on the Data Sources tab when you set up the SQL Agent job.

An example: put your user id and password on the Data Sources tab when you set up the SQL Agent job (note: you password will be encrypted, and will not display as clear text)

When Package Configuration enabled

Enable the package configuration first. Then add two configuration strings, CONFIGPATH_Conn1 and CONFIGPATH_Conn2.

Instead of pointing the configuration string directly to a XML configuration file, will point them to environment variables. They need to be created first before you can select them from the drop down list.

How to create a Environment variable

Click on My Computer, then Properties.

On the Advanced tab, click Environment Variables. Then add the variables, CONFIGPATH_Conn1 with value H:\Conn1.DTSCONFIG, CONFIGPATH_Conn2 with value H:\Conn2.DTSCONFIG.

Now we need to create those XML configuration files

How to create XML configuration files

Here are the templates you can use.

For DB2 using id and password:

<?xml version="1.0"?> 
    <DTSConfiguration> 
        <DTSConfigurationHeading> 
            <DTSConfigurationFileInfo 
                GeneratedBy="sombody" 
                GeneratedFromPackageName="Load_Data" 
                GeneratedFromPackageID="{13A93147-DE76-488C-A901-4DYG88-161AD5}" 
                GeneratedDate="1/1/2011 10:10:40 AM"/> 
            </DTSConfigurationHeading> 
        <Configuration ConfiguredType="Property" 
            Path="\Package.Connections[Conn1].Properties[Password]" ValueType="String"> 
            <ConfiguredValue>myPassword</ConfiguredValue> 
        </Configuration> 
        <Configuration ConfiguredType="Property" 
            Path="\Package.Connections[Conn1].Properties[UserName]" ValueType="String"> 
            <ConfiguredValue>myUserID</ConfiguredValue> 
        </Configuration> 
    </DTSConfiguration>

(Note: Conn1 must be an alia that has already been created in the IBM DB2 configuration assistant tool.

For SQL Server using integrated security:

<?xml version="1.0"?> 
<DTSConfiguration> 
<DTSConfigurationHeading> 
<DTSConfigurationFileInfo GeneratedBy="somebody" GeneratedFromPackageName="MIN" GeneratedFromPackageID="{EC4398AF-6A29-47D0-A6CE-8C63478-50F548C}" GeneratedDate="1/1/2011 10:49:05 AM"/> 
</DTSConfigurationHeading> 
<Configuration ConfiguredType="Property" Path="\Package.Connections[Conn2].Properties[ServerName]" ValueType="String"> 
<ConfiguredValue>mySQLServerName</ConfiguredValue> 
</Configuration>

Last notes:

1. with the package configuration enabled, you will still need to type in your password in the Connection Manager edit window.
2. your SQL integration services server needs to have the same environment variables, and the XML configuration files (with production server name and generic user id and password)
3. once you deploy your package to your production SSIS server, nothing special needs to be done on the Data Sources tab.