Solved

SSIS Excel connection failure only in SQL Agent

Posted on 2011-09-08
5
332 Views
Last Modified: 2016-11-15
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. Error thrown in SQL Agent
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.
0
Comment
Question by:STOCRIC
  • 3
5 Comments
 
LVL 3

Accepted Solution

by:
jawaharroy earned 500 total points
ID: 36501695
May be the your sql server running on 64 bit. you need to execute 32 bit version of the dtexecui. Please refer the link below http://social.msdn.microsoft.com/forums/en-US/sqlintegrationservices/thread/949915a3-dde7-4a0a-ba68-99dece3ba86b

also verify the excel version you have selected while creating connection manager. may be  version differs in server.
0
 

Author Comment

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

thanks
0
 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 36502492
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.
0
 

Author Comment

by:STOCRIC
ID: 36502870
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...
0
 

Author Comment

by:STOCRIC
ID: 36502945
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.
0

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question