Can''t run SSIS package via Agent

I've seen many postings on various forums on how to get SQL agent to execute an SSIS package. I have one that was originally created via the import wizard in SSMS, later modified in VS2005, and then re-imported in SSMS using the Object Explorer interface to load the dtsx file into SQL storage. I've tried several package protection options when importing the package: "Don't save sensitive data" (the package has no passwords in it), server storage and roles, and a specified package password (which I entered as a /DECRYPT command line parameter in the Agent job step). Agent is running using a domain admin account. No matter what I've tried, I still get an error during job execution that it failed to decrypt the password XML node. The package runs just fine when executed manually in the SSIS Object Explorer.

How can I resolve this?

Are you sure you have been talking about the same packge? You have clearly tried the options, including those which mean there is no encryption to decrypt, so it makes no sense. I have often got confused between versions or a similar mix up when frustated and trying to debug an issue like this.

|||

And by this, I believe that darren means versions of the same package. I have had similar problems in the past where I will continue to bang my head against the wall knowing that something should be working before remembering that I didn't copy the latest version of the package over to the release folder, or from the release folder to the sql package storage etc...

Anywho, I would try pushing it out with encrypt sensitive with password and if it asks you for a password when you are setting up the package in the job step you know that you have the correct version... If you use the sql package store on your ssis server, I would use the serverstorage method of encryption.

|||

Bear with me -- this is my first SSIS project; I've only used DTS in the past. I have only one version of the dtsx file. I've been setting the protection options when I import the file into SSIS via the object explorer. When you say "push it out with 'encrypt sensitive with password'", do you mean there is some way to set that option in the VS designer IDE? When I tried the pw option on import to SSIS, I did not get prompted for the pw when I created the Agent job step. Instead, I put a /DECRYPT switch in the job step's command line options. Please explain.

(And thanks very much for your help!)

|||yes there is a way to set this in the visual studios designer. Right click on the background of the control flow task of your package and select properties. About half way down the list there is a property for "protection level" where there is a drop down for the different types of encryption. If you choose encrypt sensitive w/ password it will prompt you for a password and a confirm password to encrypt the package with. After you have filled this out, any time that you go to the job step maintenance screen it will prompt you for the password (to verify your right to see the connection strings and whatnot).|||

That worked! I found the property setting in the designer and set it as you suggested. When I re-imported the dtsx file into SSIS on the production server, I also set the same protection option and entered the package pw set in the designer. I then deleted the old Agent job step and created a new one. It did not prompt for the package password, but it did execute the SSIS package this time. I saw no place in the various settings tabs for a package pw, nor did it prompt me for one, and I did not alter the command line defaults. Should it have prompted for a pw?

At any rate, the Agent job now works, and I am most grateful!

edit: BTW, when I set the 'Encrypt sensitive with password' option in the designer, it did not prompt for a package pw. There was a line in the property sheet a few lines up for entering a password.