brad2575
asked on
SSIS Agent Job Failing when Using Proxy Account
I have an SSIS package that access databases and network storage for flat file access/updates.
The problem I am having is setting up the package to run from the Server Agent as a job.
I had a network ID/PW created to run the job and gave this ID permissions to the folders that I am accessing with the process. I created Credentials using this ID/PW and then I created a Proxy Account using these credentials and on the Job under "Run As" I used this Proxy Account.
Doing all of this I still get errors (listed at bottom).
I changed the credentials to use my network ID/PW (as the job runs fine from my local machine) and the job ran just fine so obviously it is something with how I am setting up the job and/or permissions but I an not sure what.
I found this microsofit article: http://support.microsoft.com/kb/918760/ that covered a lot of what I am having issues with but I tried all the solutions and none of them solved my problem.
Any help/suggestions would be appreciated.
Error I am receiving (NOTE: The error says file does not exist but the connection is not to a file it is to a folder where I am reading files from dynamically or deleting existing files from so a missing file name is not the issue). I also removed the paths and ID I was using.
Message
Executed as user: IDRemoved . ...sion 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 7:49:18 AM Error: 2008-12-09 07:49:21.55 Code: 0xC001401E Source: MonthlyLetterProcessFull Connection manager "PathRemoved" Description: The file name "PathRemoved" specified in the connection was not valid. End Error Error: 2008-12-09 07:49:21.55 Code: 0xC001401D Source: MonthlyLetterProcessFull Description: Connection "PathRemoved" failed validation. End Error Error: 2008-12-09 07:49:21.61 Code: 0xC001401E Source: MonthlyLetterProcessFull Connection manager "PathRemoved" Description: The file name "PathRemoved" specified in the connection was not valid. End Error Error: 2008-12-09 07:49:... The package execution fa... The step failed.
The problem I am having is setting up the package to run from the Server Agent as a job.
I had a network ID/PW created to run the job and gave this ID permissions to the folders that I am accessing with the process. I created Credentials using this ID/PW and then I created a Proxy Account using these credentials and on the Job under "Run As" I used this Proxy Account.
Doing all of this I still get errors (listed at bottom).
I changed the credentials to use my network ID/PW (as the job runs fine from my local machine) and the job ran just fine so obviously it is something with how I am setting up the job and/or permissions but I an not sure what.
I found this microsofit article: http://support.microsoft.com/kb/918760/ that covered a lot of what I am having issues with but I tried all the solutions and none of them solved my problem.
Any help/suggestions would be appreciated.
Error I am receiving (NOTE: The error says file does not exist but the connection is not to a file it is to a folder where I am reading files from dynamically or deleting existing files from so a missing file name is not the issue). I also removed the paths and ID I was using.
Message
Executed as user: IDRemoved . ...sion 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 7:49:18 AM Error: 2008-12-09 07:49:21.55 Code: 0xC001401E Source: MonthlyLetterProcessFull Connection manager "PathRemoved" Description: The file name "PathRemoved" specified in the connection was not valid. End Error Error: 2008-12-09 07:49:21.55 Code: 0xC001401D Source: MonthlyLetterProcessFull Description: Connection "PathRemoved" failed validation. End Error Error: 2008-12-09 07:49:21.61 Code: 0xC001401E Source: MonthlyLetterProcessFull Connection manager "PathRemoved" Description: The file name "PathRemoved" specified in the connection was not valid. End Error Error: 2008-12-09 07:49:... The package execution fa... The step failed.
Does the SQL Agent service account have membership in any of the MSDB roles for SSIS (db_dtsadmin, db_dtsltduser, db_dtsoperator)? Check BOL for their usage.
Might be redundant, but are you using a fully qualified path (UNC) for the directory folder you are searching.
Also, verify what user the package is running under is correct. Above where it says Executed as User: is that the right user that has permissions to the folders.
Can you logon with that user and run the package locally?
Also, verify what user the package is running under is correct. Above where it says Executed as User: is that the right user that has permissions to the folders.
Can you logon with that user and run the package locally?
ASKER
cs97jjm3:
I would assume that the agent does because other people run jobs from the agent and I can run the job under from the agent if I change the creditinals to use my network ID/PW vs the one I had created for this purpose so any permissions the Agent would need would have to be there or else it would not work with my ID/PW.
HoggZilla:
Yes I checked and I am using the fully qualified path. It would not have worked under the credentials under my ID if it did not (which there was one instance where I was not using it and it errored out so I fixed that).
Yes the "Executed as user" is the user that I have set up for the permissions.
I just logged in under that ID/PW and checked the permissions on the folders for that ID and they are correct (I can read/write/update/delete) under that ID. My next step is to log onto my machine as that the ID/PW and run the package under that ID/PW to see if I have any issues or not.
I will post the updates on that as soon as I have them.
In the meantime any other suggestions would be appreciated.
I would assume that the agent does because other people run jobs from the agent and I can run the job under from the agent if I change the creditinals to use my network ID/PW vs the one I had created for this purpose so any permissions the Agent would need would have to be there or else it would not work with my ID/PW.
HoggZilla:
Yes I checked and I am using the fully qualified path. It would not have worked under the credentials under my ID if it did not (which there was one instance where I was not using it and it errored out so I fixed that).
Yes the "Executed as user" is the user that I have set up for the permissions.
I just logged in under that ID/PW and checked the permissions on the folders for that ID and they are correct (I can read/write/update/delete) under that ID. My next step is to log onto my machine as that the ID/PW and run the package under that ID/PW to see if I have any issues or not.
I will post the updates on that as soon as I have them.
In the meantime any other suggestions would be appreciated.
does the agent have access to the share
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.