Making my SSIS package portable - how to do this?

Question

I know how to create SSIS packages and getting my data source and destinations. But what will i have to do in my package if i want to make it portable in the sense where i can change the source and destination connection strings when i move my package onto another PC?

Thanks in advanced!

Answer 1

Couple of options. Do you mean you want to make the solution portable, so that you can develop on different PC's, or do you mean you want to make the end-deployable package portable?

You can use package configurations for both. They're accessible under the SSIS / Configurations menu. You specify the properties of objects you want to make dynamic, and this gets saved to an XML file (e.g. connection strings, path locations, variable values).

You can change this xml file in a text editor, and when the package runs, it'll use those values. However, if you're developing on multiple machines, this is still a little niggly. You might need to re-specify which configuration file to use when opening the solution on a new PC.

Instead, if it's DB connection strings, look at using trusted connections. They mean you don't have to enter SQL credentials. If it's for file locations, this won't work, but you could replicate directory structures or something to get around that.

If it's for deployment, then package configurations are definitely the way to go. Make sure your project is using configuration files, then when you compile you can choose to create a "deployment package". I think this is under Build options. This creates an installer file packaged with the config file, and when you run this installer you can view / change configuration values.

Edit - found a bit of a more detailed response here : http://vyaskn.tripod.com/sql_server_2005_making_ssis_packages_portable.htm

Answer 2

Along with package configurations. You can create a deployment utility, which you can then use to do a file system deployment. When you run the deployed executable you will be able to select which configuration file to run the package with.

This setting is set to 'false' by default

Project > Properties... > Deployment Utility > CreateDeploymentUtility = true

Answer 3

I stored my connection string values in my application settings in my .NET project. The values are passed to the package programmatically, and can be configured to point to any source or destination.

Here is some code I recently slapped together to do this:

    Dim pkg As New Package
    Dim app As New Microsoft.SqlServer.Dts.Runtime.Application
    Dim pkgResults As DTSExecResult
    Dim eventListener As New EventListener()

    Try
        'create SSIS variables for dynamic parameters, retrieved from the appSettings in the [app.config] file
        Dim SSISPackagePassword As String = ConfigurationManager.AppSettings.Item("SSISPackagePassword")
        Dim SSISImportPackagePath As String = ConfigurationManager.AppSettings.Item("SSISImportPackagePath")
        Dim SSISImportPackageServerName As String = ConfigurationManager.AppSettings.Item("SSISImportPackageServerName")
        Dim SSISImportPackageServerUserName As String = ConfigurationManager.AppSettings.Item("SSISImportPackageServerUserName")
        Dim SSISImportPackageServerPassword As String = ConfigurationManager.AppSettings.Item("SSISImportPackageServerPassword")
        Dim SSISImportPackageDestinationDBName As String = ConfigurationManager.AppSettings.Item("SSISImportPackageDestinationDBName")

        'set package password
        app.PackagePassword = SSISPackagePassword
        pkg.PackagePassword = SSISPackagePassword

        'Load SSIS package
        pkg = app.LoadFromSqlServer(SSISImportPackagePath, SSISImportPackageServerName, SSISImportPackageServerUserName, SSISImportPackageServerPassword, eventListener)

        'set the package-level variable to supply the Access DB's (SSIS source) file path
        pkg.Variables("sourceDatabasePath").Value = databasePath

        'Dynamic SQL connection string values
        pkg.Variables("destinationDatabaseLocation").Value = SSISImportPackageServerName
        pkg.Variables("destinationDBUserID").Value = SSISImportPackageServerUserName
        pkg.Variables("destinationDBName").Value = SSISImportPackageDestinationDBName
        pkg.Variables("destinationDBPassword").Value = SSISImportPackageServerPassword

        'Execute the Import SSIS package, add an eventListener object for SSIS reflection
        pkgResults = pkg.Execute(Nothing, Nothing, eventListener, Nothing, Nothing)

        'Package execution results
        Select Case pkgResults

            Case DTSExecResult.Completion
                MsgBox("Data import completed!")

            Case DTSExecResult.Success
                MsgBox("Data import was successful!")

            Case DTSExecResult.Failure
                MsgBox("Data import was not successful!")

        End Select

    Catch ex As Exception
        MsgBox(ex.Message)
    End Try

My biggest advice on doing this, is to create your package first with a hardcoded connection string value. Once your package is tested, completed, verified, then remove the connection string, rewrite it with a regular expression using the variables defined as parameters to your package.

The big downside to this is when you need to modify a package. Since the connection is dynamic it loses track of metadata, mappings and the IDE just gets very unhappy, fast. I have not found a solution to this (maybe I could be overlooking something?). Once you've reverted the connection strings to hardcoded sources, you can fix your package from there. Of course you have to later re-add your dynamic settings again (so I always save them in a text file, because regular expressions arent fun to re-write over and over). P.S. Sorry this is in vb :( seriously...

Good luck, may the force be with you.