posted on Monday, December 19, 2005 11:02 AM by bknight

SSIS Packages are Encrypted by Default

By default, SSIS files in development are encypted to prevent an unauthorized person from seeing your SSIS package. The type of encyrption is seamless behind the scene and is at a workstation and user level. So, if you were to send a package that you're developing to another developer on your team, he would not be able to open it by default. The same would apply if you logged in with a different user. You would receive the below error:

There were errors while the package was being loaded.
The package might be corrupted.
See the Error List for details.

The error is very misleading. In truth, you can't open the package because the originating user encrypted the package whether on purpose or not. To fix this, the owner of the package can open the package and in the Properties pane, select a different option (like a package password) for the ProtectionLevel option. The default option is EncryptSensitiveWithUserKey. To protect the entire package with a password, select the EncryptAllWithPassword option.

An option that I like is that a SSIS designer encrypts all packages with the default option and when he's ready to send to production, he can develop a batch file to loop through a directory's .dtsx file and set a password. The batch file would use Dtutil.exe and look like this: 

for %%f in (*.dtsx) do Dtutil.exe /file %%f /encrypt file;%%f;3;newpassword

This would loop through each .dtsx file in yoru directory and assign the password of newpassword. The production support group could then use the same batch file to reset the password to a production password.

-- Brian Knight

Comments

# re: SSIS Packages are Encrypted by Default

Wednesday, December 21, 2005 9:30 AM by Adrian Crawford
Brian,
Is there a way that you know of to enable 1 password for all packages within an SSIS project. I have about 7 packages within a project and am loving entering the same package passord 7 times when opening the project or building ;). I work in a team environment where we use a lot of shared data sources within the project, so we have had to use the package password option. Any thoughts?

# re: SSIS Packages are Encrypted by Default

Wednesday, December 21, 2005 6:50 PM by bknight
Adrian, your best bet is to do a batch file as was shown above. So, before you check in a package to SourceSafe or on a public share, just reset all the packages' password using the dtutil program. Not great but works!

# re: SSIS Packages are Encrypted by Default

Thursday, July 27, 2006 5:05 AM by Hennie
Brian,

When i execute this command line it will overwrite the existing XML file of the package without any enters (in other words one line). Consequently an error appears in the BIDS about slow loading because of one line in the XML File.

Hennie