1

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)).

Ankit Tyagi
  • 175
  • 2
  • 17
  • 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 Dec 20 '18 at 15:16
  • 1
    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 Dec 20 '18 at 15:19
  • 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: https://stackoverflow.com/questions/52651367/ssis-package-gives-error-after-deployment-sql-server-2012/52653538#52653538 – Tim Mylott Dec 20 '18 at 20:13
  • 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 Dec 21 '18 at 09:46
  • did you research that error? Here's a couple I found with quick search you could explore: https://stackoverflow.com/questions/17785063/retrieving-the-com-class-factory-for-component-error-80070005-access-is-de or this one is word but I'm sure would be similar for excel https://stackoverflow.com/questions/3477086/accessing-office-word-object-model-through-asp-net-results-in-failed-due-to-the – Tim Mylott Dec 21 '18 at 14:17
  • Yes i did and apply some changes, now error has changed to "exception from hresult 0x800a03ec " – Ankit Tyagi Dec 21 '18 at 14:19

1 Answers1

1

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.

Ferdipux
  • 5,116
  • 1
  • 19
  • 33