March 23, 2009 at 8:46 am
I have created a package, basically for importing data, I created it using wizard so it is quite simple, just follow the instruction step by step, I run it, no problem, data can be imported.
Nightmare begins when I try to schedule it as a job. it always failed!
No specific reason was given, Job history just said the job failed, it was invoked by XXX. The last step to run was step 1.
Anyone knows what's going on there? I feel so frustrating to use DTS in SQL 2005 than in 2000.
March 23, 2009 at 9:10 am
When you run the package manually it runs in the context of your permissions if you run it as a job it runs in the context of the Agent permissions which must be admin which could be a prxy account.
http://support.microsoft.com/kb/912911
http://support.microsoft.com/kb/918760/
Kind regards,
Gift Peddie
March 23, 2009 at 11:04 am
I like to log on to the server as the service account and test to make sure I can access resources, such as shares.
Is your sql agent using a domain account? If it is not, it may have trouble getting at resources off the machine.
March 23, 2009 at 12:16 pm
Have you set up logging to a file in the job step's logging tab? This could be useful in getting more information about where the failure is. It could be security, it could it cannot find a file, it could just be a misspelling in something.
Chris
Learning something new on every visit to SSC. Hoping to pass it on to someone else.
March 23, 2009 at 1:19 pm
OK, this is a little tricky:
My credential is ABC\DEF
The job (SSIS) needs to fetch a file on network, which ABC\DEF has access to.
Once ABC\EDF got the file, it will be passed to SQL 2005 server, where ABC\DEF is the owner of the package.
There is no problem for ABC\DEF to run the package, but it fails when it is executed as "SQL Agent Service Account"
The SSIS package is saved on a server different than the SQL 2005 server. ABC\DEF has full access to it.
ABC\DEF has no control on the location of the original data file, but has full control on SQL 2005 as well as the server where the SSIS package is stored.
What should I do now?
Thanks lots in advance.
March 23, 2009 at 1:54 pm
I believe you need to find out what account sql agent service is running under and grant the appropriate permissions to it. Otherwise you need to create a 'credential' using ABC\DEF or an equivalent account and use that as a proxy under 'job step'.
March 23, 2009 at 2:17 pm
Hi,
I think the problem is clear now: I need to make sure the account running the package has the same privilege as ABC\DEF, because that's the account set up for retrieving original data file.
Currently in the "step", it is Run as SQL Agent Service Account, apparently it can't access the original data file (I've confirmed this).
Now, how do I do to make this package run as ABC\DEF?
Thanks.
March 23, 2009 at 2:21 pm
credential:
http://msdn.microsoft.com/en-us/library/ms190703.aspx
proxy:
http://msdn.microsoft.com/en-us/library/ms189064.aspx
But note that the password is stored in the credential, so this shouldn't be your personal account, rather, a service account with a non-expiring password.
March 23, 2009 at 2:34 pm
Thanks Sam, it is working now.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply