I thought I would document my exploits in SQL Server SSIS 2005 conversion of DTS packages and the scheduling.
I had a DTS package that used the ProtectionLevel “Encrypt Sensitive Data with User Key” that I did not design or create. When I migrated them from SQL 2000 to SQL 2005, I used BI studio to bring them to the file system and then modify them in SSIS and put them back into SQL 2005 into a SSIS Store.
What I did not realize is that when you have the Encrypt with User Key, in 2005 the only option to run an SSIS package is to run it as SQL Server Agent, which in our case is running as Local System. Now the User Key would not be the same as the one that created it, nor would it have Admin rights (per se) to be part of Administrators Group to run this package.
I went about looking for the solution and found a KB article (918760) that indicates that I can put it in a SQL Server storage protection level and then it would use SQL Server roles to get the data out of encryption.
I promptly went into the BI Studio and opened the package and tried to change the protection level to SQL Server Storage and it would not let me save the package because it could not verify that it was secure. But there is no way to put it into SQL Server Storage to use that Protection Level. So I had to change it to Encrypt SensitiveInfo with Password.
Here is the fun part.
I went into BI studio and Added a Package from SSIS store (because the packages were already in there) and then modified the option and then saved it. What I did not know was that it saved it to the file system and did not save it back to the SSIS Store, so the package still failed to run under SQL Agent.
I figured out that I still needed to import it into the SSIS store and I did so, but it still would not run. I could not figure out why, but then after digging and looking at all the options, I saw this dialog and decided to click on the button circled.
I put the password into the package and then saved it out to the File System, then imported it into SSIS Store. I needed to schedule this package to run and so I went into SQL Agent jobs and created a job with a step that indicated to run an SSIS Package. I knew that I had to specify a password so I looked up the dtexec utility page and found the /DECRYPT option. Now here is what threw me. I thought that in the first place that BI Studio saved the package or that the import of the package saved the option of Encrypt with Password option so I went into the CommandLines tab and edited the option and put in /DECRYPT with the password in the Edit Manually option, and then clicked OK. I ran the package and it worked. Well I found out later that it had not, but another weird part is that when I edited the job step the option in CommandLines was set back to Restore original options and I did not see the DECRYPT option in the window below.
So I battled over this for a while, then tried the import again and clicked on the dialog button to change the protection level and chose the Password option, set the password and then it imported. After doing this I changed the job and an interesting thing happened. It prompted me for the password for the package this time. I went into the Commandlines and it did not show any difference, but I went ahead and left it.
I found out that the DECRYPT option was in the CommandLines window, but there was no password specified, which means that it was hiding it from the UI, which is OK I guess, but I had never seen it in there saved before when I manually edited it.
I also found out that when I right clicked on the job and scripted it as CREATE to a new query window, that the option in Command showed the DECRYPT option with the password I specified when I created the Job Step.
So wild stuff and I have survived and the job actually runs now under SQL Agent so I am happy now. Just more wiser too as I spent a great amount of time working this one out.
Sorry if this does not make complete sense. I wanted to get this out now so that I did not forget it.
Have fun and I will have more SSIS stuff to share after the next couple of days.