April 6, 2011 at 8:31 am
Hi,
I have a SSIS job that is executing as scheduled but not importing any data to the destination table.
It has to import the flat files from a specific path to the destination table. However if I execute the pkg manually from the server, it works fine.
Any help appreciated.
Thanks,
PSB
April 6, 2011 at 8:51 am
Sounds like it might be an issue with permissions. I take it when you say "scheduled", you mean it runs as a SQL Server Agent job? If so, does the SQL Server Agent service account have access to the destination table? What happens if you log in as that account and then run the package?
John
April 6, 2011 at 9:11 am
If I login with my windows or sa credentials and run the pkg manually from the solution , it gets imported. But when I run the pkg as a job it does not import.
How do I find out if the SQL Server Agent service account have access to the destination table?
April 6, 2011 at 9:18 am
Is the destination table on a separate instance? If so, check that the SQL Server Agent account (or a Windows group of which it is a member) is set up as a login on that instance. Then check that that login maps to a user in the database where the destination table lives. Finally, check that the user is a member of the db_datawriter database role, or that the user has explicit INSERT permission on the table.
If the destination table is on the same instance, a simple way of testing access to the table would be to create a job to insert a single row into it.
John
April 6, 2011 at 12:26 pm
Tried to run the job , but this time I changed the path of the source file. Instaed of the network folder , I set up a local folder in the server and ran the job. It behaved the way it is supposed to.
So I know it is really a security issue for SQL server agent trying to read the file from a network folder. Not sure how to set it though.
April 7, 2011 at 1:28 am
If you have SQL Server Agent running under a local account then you can't do it. You need to run it under a domain account.
John
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply