So you created this great new SSIS package in Business Intelligence Development Studio and now you want to deploy it to the SQL Server. You build a manifest (I'll actually explain how to do this in the next post), and copy it to the SQL Server and run it and validation starts. Then you notice that you are getting login failures using the credentials you specify in the connection. You go back and test them and they are correct. You try again and it still doesn't work. This is caused by the default protection level of an SSIS package.
In SSIS packages there is a property called ProtectionLevel. This property by default has a value of EncryptSensitiveWithUserKey. What this basically means is that all passwords and other data SSIS deems sensitive (i.e.: passwords) will be encrypted using a UserKey (based off the account you created the package with. This works well, but once you transfer it to a SQL Server, you are more than likely not using the same account to run the package.
To get the package to work you have to change the protection level to either EncryptSensitiveWithPassword or EncryptAllWithPassword. The difference between the two is that EncryptSensitive just encrypts the sensitive data and EncryptAll encrypts the entire package. You are required to specify a password to encrypt the password with. This same password is required to install the package and execute it.
Note, I have tried using EncryptSensitive and I have still had trouble with it storing password for connections. Therefore, you might have to use EncryptAll.
There is also another protection level called ServerStorage. When you are deploying a package into MSDB, it will make use of database security. However, I have not figured out how to use this yet since Business Intelligence Development Studio will not let you select it when saving an SSIS package to the file system. If anyone figures it out, please let me know.
Read the complete post at http://www.dotnettipoftheday.com/blog.aspx?id=276