January 1, 2016 at 3:36 pm
Happy New Years fellas!
So I have a SSIS package that creates a report with the destination of the excel file being my local drive. I introduced a file system task to my solution to copy the file from my local drive to a share drive "K:\Dep\Corp10\Finance\Reporting\Report Extracts. In Bids the package works fine and copies the file, however when I run the job via sql server agent to automate the process, the job fails the copy portion due to invalid path.
I have been trying to research this on my own, and from my search I kept seeing that I might need to add a proxy to run the job. I'm not familiar with proxies.
I would appreciate some insight on how to best resolve my issue aforementioned.
Thanks in advance.
January 3, 2016 at 5:45 pm
Briceston (1/1/2016)
Happy New Years fellas!So I have a SSIS package that creates a report with the destination of the excel file being my local drive. I introduced a file system task to my solution to copy the file from my local drive to a share drive "K:\Dep\Corp10\Finance\Reporting\Report Extracts. In Bids the package works fine and copies the file, however when I run the job via sql server agent to automate the process, the job fails the copy portion due to invalid path.
I have been trying to research this on my own, and from my search I kept seeing that I might need to add a proxy to run the job. I'm not familiar with proxies.
I would appreciate some insight on how to best resolve my issue aforementioned.
Thanks in advance.
Whatever account the SSIS package runs as will need access to drive K: - chances are drive K: does not exist for this account. For you, it is probably a drive that is mapped for you (perhaps when you login). Personally, I would change the folder to a UNC path (e.g. \\servername\sharename\Dep\Corp10\Finance\Reporting\Report Extracts).
Once you have done this, you need to check what account you are using to run the SSIS package - if you don't have a proxy setup then this will be the account that SQL Server Agent is running as. Grant this account read permission on the folder and you should be OK.
January 4, 2016 at 11:06 am
happyCat59, thanks for replying. I will look into your advise and reply back with my outcome.
February 1, 2016 at 7:18 am
Changing the mapping to the UNC path resolved my issue. Thanks for the insight and my apologies for the delayed response.
February 1, 2016 at 9:46 am
happycat59 (1/3/2016)
Once you have done this, you need to check what account you are using to run the SSIS package - if you don't have a proxy setup then this will be the account that SQL Server Agent is running as. Grant this account read permission on the folder and you should be OK.
If you're copying a file you'll need more than read permission
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply