posted on Monday, December 19, 2005 11:02 AM
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