February 9, 2006 at 11:48 am
I have several SSIS packages that need to be run in sequence from within SQL Server Agent. The packages run fine from within VS2005, They run fine standalone. They fail everytime when run from SQL Server Agent.
The connection strings contain "sensitive data" and are protected with passwords.
Any words of wisdom would be greatly appreciated.
February 10, 2006 at 1:50 am
What error message do you get?
Are your passwords stored in the package?
What is the package's ProtectionLevel property set to?
This problem is usually something to do with package's protection level.
-Jamie
Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson
February 10, 2006 at 6:45 am
Error Message:
JOB RUN: 'LocalTAS' was run on 2/9/2006 at 4:00:00 AM
DURATION: 0 hours, 0 minutes, 1 seconds
STATUS: Failed
MESSAGES: The job failed. The Job was invoked by Schedule 6 (LocalTAS). The last step to run was step 1 (Download Flat Files).
Passwords are stored in the package. I can run the package standalone from within SS Management Studio/Integration Service (right click, run package)
Package Protection Level:
EncryptSensitiveWithPassword
I supply the password when installing the package.
Thanks for the reply..
February 10, 2006 at 7:20 am
Are you doinng any logging from the packages? What errors do you get from the packages?
-Jamie
Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson
February 15, 2006 at 8:03 pm
After 2 1/2 hours and 2 MS Engineers, I found the issue. It seems that when you import a package with sensitive data (i.e. passwords) there is an option to re-encrypt the file for SQL Server storage. If you do not choose an encryption option, it defaults to User key Encryption. It doesn't tell you, but it does. The dialog box where you are given this option is also greyed out even thought it is an active element. MS has promised to update the Book On Line and submit a design change to the product team.
Thank you for resoponding...
February 16, 2006 at 8:31 am
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply