How to use DontSaveSensitive and xml configuration file during SSIS package development

Question

Is it somhow possible to set the ProtectionLevel of SSIS package to DontSaveSensitive and to use connection string with password from configuration file during package development in Visual Studio?

I have package e.g. Package1 with ProtectionLevel = DontSaveSensitive. This package is using connection from connection manager e.g. Connection1.

Package1 has configuration enabled using configuration file file1.dtsConfig with connection string specified. This connection string has the password in it:

<DTSConfiguration>
    <DTSConfigurationHeading>
        <DTSConfigurationFileInfo GeneratedBy="..." GeneratedFromPackageName="..." GeneratedFromPackageID="..." GeneratedDate="20.3.2013 12:08:27"/>
    </DTSConfigurationHeading>
    <Configuration ConfiguredType="Property" Path="\Package.Connections[Destination].Properties[ConnectionString]" ValueType="String">
        <ConfiguredValue>Data Source=.;Password=Password123;User ID=MyUser;Initial Catalog=Catalog;Provider=SQLNCLI10.1;Persist Security Info=True;Auto Translate=False;</ConfiguredValue>
    </Configuration>
</DTSConfiguration>

Now when opening the connection from connection manager in Visual Studio, the text field for Password is left blank and the package doen't execute. Why wasn't used the password specified within the connection string in configuration file file1.dtsConfig?

Answer 1

Finally we found a way how to do it:

• Change ProtectionLevel to DontSaveSensitive
• Create configuraton file with connection string
• Manually edit this configuration file: add the password to the connection string in case you connect with SQL Server authentication

Then SSIS package will load the connection string inclusive password from the configuration file even in Visual Studio. In connection manager-connection dialog the password will not be shown, but the package runs using connection string from configuration.