November 29, 2015 at 10:22 pm
Hi Folks,
I am facing issue with Excel upload utility of SSIS. When i run the packgae using BIDS/ or from the package solution file then it executes properly , my file imports in table. But when i run the job using SQL server agent it skips the file import. While checking the catalog log through Integration catalog report it says no file found. I tried checing the connection everything is fine plus i set the packcage property 'Run64bitruntime' to false, delay validation to false but nothing working.
PLease help!
November 30, 2015 at 1:49 am
Any one?? Please reply!
November 30, 2015 at 2:56 am
HI,
Yes.
From the Same account id i have created the package , deployed the package and confiure to run by SQl server agent.
I don't know what i am missing 🙁
November 30, 2015 at 3:12 am
Can you post the exact error returned?
If you go to the job, click 'view job history', then expand the latest run and select the relevant step, is there an error in the row details pane? Or does it say it succeeded? (This info will include the account that executed the job too, might be worth double checking this is the account you expect it to be)
November 30, 2015 at 3:20 am
Hi,
I am not facing any error or failure while executing the SQL JOB.
See what is happening when i execute my package using BIDS or from open solution file directly then the EXCEL file is getting picked proprly whereas when i configure package and try running though SQL job then my job executed properly but file remain in the same folder.
While checking the Integration catalog log i saw it says file emumerator doesnt find the specified path. please look into the screenshot and help wth the way forward.
November 30, 2015 at 3:31 am
It really does sound like a permissions issue to me, did you check in the job details that it was executed by the user account you'd expect it to? And that it read "success"?
I can't think of any other reason it would fail to pick the file up if it works in SSIS.
November 30, 2015 at 3:47 am
Have checked the job details and config multiple times...
user account is same..
🙁 🙁 🙁
November 30, 2015 at 4:12 am
Do u mean VNC? Virtual Network Connection?
November 30, 2015 at 5:16 am
Yes!
My file name is dynamic.. There is setup done in the master table from where i am picking the path and file name and passing through the variable.
December 1, 2015 at 5:14 am
Most likely the path is not accessible to the service account running the SQL server agent ... it is not just the SQL login to the database you need to be the same. Try adding an "Operating System (CmdExec)" step that just does a "dir" of the path you expect the file to be on and check what that is showing. A "Operating System (CmdExec)" step running "whoami" is also useful for checking exactly which account your job is running as.
The other common issue I see people have with jobs like that is assuming a mapped drive they normally use would be available to SQL server agent - earlier question about UNC paths is on the correct line here - so e.g. if you expected the file to be on U:\Folder\file.xls but U: was actually a mapping to \\AnyServer\Anyshare you are best using \\AnyServer\AnyShare\Folder\file.xls as the path to load the file from.
Then you also need to make sure that the service account running SQL Server agent has the appropriate rights to both the share AND the folder and file itself (i.e. network access rights to the share and file access rights to the folder and file).
James Horsley
Workflow Consulting Limited
December 1, 2015 at 6:27 pm
Also for safe measure, make sure you do not have the file open or the package in an open BIDS window locking it at the same time. Likely though, as mentioned, you are seeing the agent account not having access to the file location.
----------------------------------------------------
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply