CODEWITHSUNDEEP
ssisconfiguration-files
gembird
gembird

Reputation: 14053

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

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?

enter image description here

Upvotes: 5

Views: 15473

Answers (3)

RonVibbentrop
RonVibbentrop

Reputation: 311

This is the case also when package has been set at first to use EncryptSensitiveWithUserKey, then changed to DontSaveSensitive -protection level. You must manually edit .conmgr-file and remove DTS:Password element and put the password into connection string, as user dee has wisely written above.

Upvotes: 0

praveen
praveen

Reputation: 12271

SSIS will take the connection string from the Config File only during the runtime.Even if you mark the checkbox Save my Password , SSIS won't save the password value.This option is vald only during the BIDS session. So the next time when you open the package using BIDS ,again you need to enter the credentials in Visual Studio else the package won't compile ,But the package will always execute properly during run time if you have specified the connection string in config file.

As per MSDN it clearly states that

Do not save sensitive : prevents properties that are marked sensitive from being saved with the package and therefore makes the sensitive data unavailable to other users.

Upvotes: 0

gembird
gembird

Reputation: 14053

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.

Upvotes: 5

Related Questions