asked on

SSIS Excel connection failure only in SQL Agent

I have a working ssis package that simply imports some data from an Excel file and puts it into SQL 2008. as a package in BIDS it works fine. As a SQL Agent job it does not work. throwing the error as attached screenshot.

I have tried re registering the olejet40.dlls on the server, also I have checked the SQL Agent Service account which is Local System, I have thoroughly scanned the EE but tnothing sorts this out.

I would really appreciate any help to sort this, many thanks.

ASKER CERTIFIED SOLUTION

jawaharroy

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

STOCRIC

ASKER

I looked at the forum entry and I do not have dtsexecui.exe in the folder stated on my sql server.

thanks

Alpesh Patel

Please male sure the SQL Agent use has permission to that folder as well as to excel file to read/write.

Create proxy and execute SQL Job using proxy.

STOCRIC

ASKER

Thanks Alpesh

I created the proxy, and fired the package likie this, still get the same error.

I also changed the SQL Agent to a different user, still it does not work, here is the error message

***********************

Date            08/09/2011 15:02:48
Log            Job History (ssis CRM Order Input Forecast)

Step ID            1
Server            GBLOSBI2008
Job Name            ssis CRM Order Input Forecast
Step Name            Run package
Duration            00:00:01
Sql Severity            0
Sql Message ID            0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted            0

Message
Executed as user: EMEA\STOCRIC. Microsoft (R) SQL Server Execute Package Utility Version 10.0.4000.0 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 3:02:48 PM Error: 2011-09-08 15:02:49.64 Code: 0xC0202009 Source: crm_ssis_UK_Forecast_Order_Input_2011_09_08 Connection manager "UKForecast2011.xls" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80004005 Description: "Could not find installable ISAM.". End Error Error: 2011-09-08 15:02:49.64 Code: 0xC020801C Source: dft_Load the CRM Src xls into SQLdb Excel Source [1112] Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "UKForecast2011.xls" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed. End Error Error: 2011-09-08 15:02:49.64 Code: 0xC0047017 Source: dft_Load the CRM Src xls into SQLdb SSIS.Pipeline Description: component "Excel Source" (1112) failed validation and returned error code 0xC020801C. End Error Error: 2011-09-08 15:02:49.64 Code: 0xC004700C Source: dft_Load the CRM Src xls into SQLdb SSIS.Pipeline Description: One or more component failed validation. End Error Error: 2011-09-08 15:02:49.64 Code: 0xC0024107 Source: dft_Load the CRM Src xls into SQLdb Description: There were errors during task validation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 3:02:48 PM Finished: 3:02:49 PM Elapsed: 0.952 seconds. The package execution failed. The step failed.

***************************************

Hope this helps...

STOCRIC

ASKER

An update for you, the package runs sweet as a nut inside the ssms integration services tab 'execute package' so I guess it is definately something to do with the SQL Server Agent.