SSIS Script Task fails when run from sql job

1
votes

I have a created a SSIS package to load excel file into database but before Data Flow Task, i m using a script task to change data type of one the excel columns using interop.excel dll.

While running the package in VS2015 on the server itself, it completes with success, but when i am running it through sql server agent it is getting failed and giving following error message

"Script Task Error : Exception has been thrown by the target of an invocation."

i have already registered the dll in GAC.

What others steps should i try to resolve the issue?

Edit :-

Error Message :

Retrieving the COM class factory for component with CLSID {000209FF-0000-0000-C000-000000000046} failed due to the following error: 80070005 Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED)).

1
ssis
Did you register a 32 or 64 bit DLL? How are you running your SSIS package - the default of 64 bit mode or 32 bit mode? – billinkc
Also, you shouldn't have to register interop.excel.dll to the GAC unless you're trying to put the Office bits onto the machine without going through a proper installation. If so, that is probably a bad idea for a pair of reasons: licensing - you're putting your company at legal risk from a MS audit and security - that server won't be notified of patches to Office given the atypical install – billinkc
Could be permissions. Whatever the account running agent doesn't have permissions to the file used in the script task, maybe. I would suggest adding a try catch to your script task to get a more meaningful error. The accepted answer here shows how to do that: stackoverflow.com/questions/52651367/… – Tim Mylott
Hi Tim, Thanks for the link, i am able to get the error message, Below is the error message i am getting, Retrieving the COM class factory for component with CLSID {000209FF-0000-0000-C000-000000000046} failed due to the following error: 80070005 Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED)). – Ankit Tyagi
did you research that error? Here's a couple I found with quick search you could explore: stackoverflow.com/questions/17785063/… or this one is word but I'm sure would be similar for excel stackoverflow.com/questions/3477086/… – Tim Mylott

1 Answers

1
votes

Hmm. As you succeed in running the package under your account and it fails run by SQL Job - the most probable case is that SQL Server Agent service account does not have an access to the Excel file.

I would create a job proxy, as described here How to add SQL Job Proxy with an account which has access to the Excel file.