Last updated on August 1st, 2022 at 03:07 pm
I’m using SQL Server Integration Services (SSIS) to clone a database. I’m doing this for a customer in order to off-load the querying and reporting functions from their main Configuration Manager database server.
When I built the SSIS package, I needed to embed a few usernames and passwords. SSIS encrypts these details, so that they are not accessible by anyone. By default, only the user who creates the package can run the package. Unfortunately, in this case, that wasn’t desirable. After following the advice of a SSIS guru, I changed the package to encrypt everything using a password instead. My hope was that this would allow anyone with the password to run the package.
When I set the password on the package and executed it, however, the following error occurred:
Like any good Admin, I searched for the answer to this error message. Not finding any online help, I even cracked open a reference book for SQL Server Integration Services 2012. This also turned out to be a dead end because I was using SQL Server Integration Services 2015.
While outside, trying to squeeze in my daily 10,000-step walk, I had a thought. Maybe you need to set the encryption level someplace else. Sure enough when I got back to the office I checked and it turns out that there are TWO places that you need to set the encryption level! First on the project itself and then on the package.
Below are the steps that I took.
First, in Microsoft Visual Studio (SSDT), locate the project name. Right-click on the project name and select Properties.
Next, adjust the ProtectionLevel to match your SSIS project ProtectionLevel. Once they both match-up, the error message will disappear and you can execute your SSIS package.
Note: I found that I needed to set the project’s protection level first before I could set the package’s protection level. If I didn’t do it in that order, I continued to get the error message.
Now anyone can run the SSIS package as long as they know the password.
If you have any questions about how to set SQL Server Integration Services (SSIS) protection levels, please feel free to contact me, @GarthMJ.