SSIS Design Pattern: Using The DontSaveSensitive Package ProtectionLevel Property
I've read several posts recently on the topic on configuration and deployment management. I get these questions in most of my SSIS classes as well.
One suggestion I've been investigating is using the DontSaveSensitive ProtectionLevel property and passing the connection string or password at execution time. I'm a test-it-and-see kind of guy, so I started tinkering with making this work this evening. Here's what I did:
First, I created a new database and table and added some data to the table. I created a SQL Login for connectivity purposes. (Microsoft recommends using Windows Authentication and I agree. But I needed something with a password to test this...) Next, I created a simple package with a Data Flow containing an OLE DB Source Adapter connected to an OLE DB Connection Manager. The Connection Manager name is important:
I next set the Package ProtectionLevel property to DontSaveSensitive:
The options for ProtectionLevel are:
- DontSaveSensitive
- EncryptSensitiveWithUserKey (default)
- EncryptSensitiveWithPassword
- EncryptAllWithPassword
- EncryptAllWithUserKey
- ServerStorage
If I execute the package inside the Visual Studio debugger at this point, it fails with the following error:
[Connection manager "ANDYT64X2.ConnectionTests.SQLLogin1"] Error: An OLE DB error has occurred. Error code: 0x80040E4D. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E4D Description: "Login failed for user 'SQLLogin1'.".
(The bold blue font is the name of the Connection Manager above.)
To get the package to successfully execute now, I have to provide the password at execution time. There are two ways to do this:
- Store the value in a Package Configuration
- Pass the value into the Package at execution time
I'm not going to look at using the Package Configuration option in this post.
One method of passing the value into the package at execution time is to use the /SET switch in dtexec.exe. The dtexexui.exe utility provides a nice interface for dtexec switches, including a Set Values page for configuring package values at run-time:
Note the \Package.Connections[ANDYT64X2.ConnectionTests.SQLLogin1].Properties[Password] Property Path contains the name of my Connection Manager - again shown in bold blue font. You will need to replace this value with the name of your Connection Manager. The Value contains the password. For demo purposes, the login name and password are the same (SQLLogin1). Please don't make them the same in the field. :)
:{> Andy
Technorati Tags: