Running Password Protected Packages

A recent post on the forums was asking about running packages programmatically. The poster’s package was password protected, and when running the package, they were receiving a message that one of the connection managers was failing to connect.

The code being used was similar to this:

        Dim app As Microsoft.SqlServer.Dts.Runtime.Application

        Dim pkg As Microsoft.SqlServer.Dts.Runtime.Package

        app = New Microsoft.SqlServer.Dts.Runtime.Application()

        pkg = app.LoadPackage(“C:ProjectsTestPackage.dtsx”, Nothing)

        pkg.PackagePassword = “password”

        pkg.Execute()

This code compiles fine, but does have a small error. The password is being set on the Package object, after it has been loaded. In this case, the Package’s ProtectionLevel property was set to EncryptSensitiveWithPassword, so the error didn’t show up until the package was executed. The problem is that the password needs to be set on the Application object, before the package is loaded. This makes sense, since encrypted values are decrypted when the package is loaded. The following code works fine:

        Dim app As Microsoft.SqlServer.Dts.Runtime.Application

        Dim pkg As Microsoft.SqlServer.Dts.Runtime.Package

        app = New Microsoft.SqlServer.Dts.Runtime.Application()

        app.PackagePassword = “password”

        pkg = app.LoadPackage(“C:ProjectsTestPackage.dtsx”, Nothing)

        pkg.Execute()

This particular problem is a lot easier to see if the Package ProtectionLevel property is EncryptAllWithPassword, because you’ll get an error as soon as you load the package.

I’m not positive of this (the documentation is a little lacking), but I’m guessing you’d set the Password property on the Package object to encrypt a package that you had created programmatically. Evidently, setting it will not decrypt any existing encrypted values.