Today’s lesson is about how to avoid those annoying messages that SSIS gives you about being unable to decrypt sensitive data in a package.
I typically run SSIS packages from batch files using the DTEXEC tool. That’s probably not the optimal way to run them, but where I work it’s safer than leaving them in the hands of the database administrators. The packages run fine, but I kept getting errors saying that sensitive data could not be decrypted from the package. It wasn’t a fatal error, mind you: I put the production database connections in a separate configuration file, so the package ran despite the errors. (Which makes one wonder why it’s an “error” and not just a “warning,” but that’s another topic.)
First, some background for those that may know this. For some reason, Microsoft felt the need to control all of the database passwords in the universe. They did this by encrypting all “sensitive” data (like database connection passwords) in the SSIS package when you save it. There does not seem to be a way to disable this feature.
By default, SSIS encrypts sensitive data in the package with your Windows user information, so it will only decrypt and work for you. That’s great if you are the only person who will ever look at, modify, or run your packages. Unfortunately, in the real world, we often have to give our SSIS package to someone else to run on a production server. Or perhaps another programmer will want to load your package to modify it. If you’ve encrypted your package with the default setting, however, anyone else who tries to load it will get a message that sensitive data cannot be decrypted, and they will have to re-enter all the passwords in the package.
One way to get around this is to set your package to encrypt sensitive information using a Package Password. This is set in the properties of the SSIS Designer. After you save a package with a password, anyone can open and modify the package without having to re-enter all the database passwords. They just have to know the package password. (A team-wide default password should be decided upon.)
That solves the problem of sharing your package between multiple programmers, but what about those pesky error messages we were talking about before? Simple: Add ”/DECRYPT password” to the DTEXEC command line parameters and the errors go away.
dtexec /FILE “MyPackage.dtsx” /DECRYPT password /CONFIGFILE “MyPackage.dtsConfig”
Much more information on the DTEXEC utility can be found on MSDN.