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.
Please outline the steps that finally worked. I am having similar difficulties creating an SSIS package to copy databases. It is one error after another and mostly seems to be related to security.
I was not able to follow the steps that finally acheived the results you were after in this article.
Thanks,
Shirley
The key to this is just that if you get an error that indicates that it could not DECRYPT the secrets, then you need to change the type of security you use with the package. The default is Encrypt with the User Key, which means that it is the key of the user that created it.
The option that I used was to Encrypt the secrets with a password instead so that I can specify the password when it runs and it will allow it to run.
The process includes importing the SSIS package to the store (SSIS Store) and upon importing as seen in the graphic above, click on the button and specify Encrypt with a password and specify the password, and it will get stored like that and then when you run it, you will create a job and it will recognize that it is encrypted with a password, and it will prompt you for it and it will automatically put it in the command line for SQL Agent to run it.
Does that help?
Ben Miller
Thanks for this post. Just what I needed.
This weird stuff just happened to me! I created a SISS, deplyed and when I tried to excute Bah, Fail.. Then I just opened the Command Box, typed my pass and after that everything work right…
O missed my day! đ
Cheers
Good Article
THank you! I was having almost the exact same problem. Your post helped me figure out how to fix it!
Thanks I just spent a few hours trying to get my packages to work after deployment. I’d taken the same steps you took (including all the ones that didn’t work like SQL Server storage protection), but I hadn’t edited the command line, which has worked!
Greatly appreciate that you took the time to post the information!
I resovled my problem for which i was struggling from two days with this article .. Good one.:-) Really helpful
I’ve been troubleshooting the same issue for the past day. I was on the right track, but this post saved me a few hours and gave me the pieces of the puzzle I was still missing. Thanks!
Stupid greyed-out boxes that don’t look like they’re doing anything. I guess some MS departments think they’re too good to hire a decent UI to point these things out to them. But yeah, I spent a couple hours trying stuff, looking online for answers, and finally found your blog. I had done everything EXCEPT clicking on that stupid button to specify the protection level when importing. Funny MS SQL devs…very funny.
Your article helped me get a few steps further but now i’m stuck again. Package runs ok. security is server storage but when scheduled job runs it complains about not finding the wsdl file for my web service.
Started: 1:41:24 PM Error: 2009-03-30 13:41:41.05 Code: 0xC002F304 Source: Web Service Task 1 Web Service Task Description: An error occurred with the following error message: “Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebserviceTaskException: The input Web Services Description Language (WSDL) file is not valid. at Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebServiceTaskUtil..ctor(Object connection, String downloadedWSDL) at Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebServiceTask.executeThread()”. End Error DTExec: The package execution returned DTSER_SUCCESS (0). Started: 1:41:24 PM Finished: 1:41:41 PM Elapsed: 16.391 seconds. The package executed successfully. The step succeeded.
Running the package directly does not cause a problem with the wsdl file. I think the job can’t find it but don’t understandy why?
Anyone run into this before ?
Youre completely correct with this one…
Thank you! Your solution was perfect.
It may help someone else that I had to delete and recreate my job step for it to realize the change.