December 14, 2008 at 11:09 pm
Hi Guys,
I've created a dtsx package that works a treat when running through Visual Studio 2005, and when attached to SSIS, but when i try to create a job to run the dtsx either from file or SSIS it keeps failing, i was wondering if someone could suggest anything that might be preventing it from running.
Error Message;
The job failed. The Job was invoked by User . The last step to run was step 1 (Run Package dtsx).
Cheers,
Remember
Without Change something sleeps inside of us that seldom awakens, the sleeper must awaken!!
December 15, 2008 at 12:59 am
Within 'SQL Server Management', right click on the job and select 'View History'. Drill into the first Error (Red Cross) and you should see another error appear. Have a look at the error message for this error.
this maybe a security issue, please let me know the error and I maybe able to help further.
December 15, 2008 at 8:13 am
It may be a permissions issue ultimately.
When you run the job yourself in Visual Studio window, it runs in the context of your own username/security.
When you put the job inside a scheduled job, it runs in the context of the user attached to the SQL Agent which may or may not have authority to what you’re trying to access. Take a look at the security for the SQL Server Agent that runs jobs and make sure they have permission to run the DTSX file.
December 15, 2008 at 9:24 am
If it is a permission problem, it's most likely because of the package Protection Level. See this thread for answers to a similar problem:
http://www.sqlservercentral.com/Forums/Topic619676-148-1.aspx
Greg
December 15, 2008 at 4:05 pm
Cheers guys for all the replies and you were all right it was down to a permissions issue, i was using EncryptSensitiveWithUserKey for the dtsx package.
Which as you know, Encrypts only the information in the package by using keys based on the current user. And because the SQL Server Agent was different to the dtsx creator account it failed. I've now changed the protection level to EncryptAllWithPassword. Thou now i'm not sure how to pass the password to the package with running it within a job.
Thanks 🙂
Remember
Without Change something sleeps inside of us that seldom awakens, the sleeper must awaken!!
December 16, 2008 at 12:33 pm
If you set the job step type to "SQL Server Integration Services Package", it will prompt for the password when you click on any of the tabs will insert the DECRYPT option into the command line that executes the package.
If you set the job step type to "Operating System (Cmdexec)", you can include the DECRYPT option and password in the DTEXEC command.
Greg
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply