Home > SSIS Best Practices > SSIS – Enable Package Configuration for Deployment

SSIS – Enable Package Configuration for Deployment

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. 

image

image

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)

image

When Package Configuration enabled

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

image

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.

image

How to create a Environment variable

Click on My Computer, then Properties.

image

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.

image

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.

image

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.

Categories: SSIS Best Practices
  1. August 16, 2012 at 8:02 am

    Thanks, good article. It helps me.

    Like

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: