CODEWITHSUNDEEP
ssis
Ankit Tyagi
Ankit Tyagi

Reputation: 175

SSIS Script Task fails when run from sql job

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

Upvotes: 1

Views: 1734

Answers (1)

Ferdipux
Ferdipux

Reputation: 5256

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.

Upvotes: 1

Related Questions