Solved

SSIS Excel connection failure only in SQL Agent

Posted on 2011-09-08
5
316 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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

In couple weeks ago, I encountered an extremely difficult problem while deploying 2008 SSIS packages to a new environment (SQL Server 2014 standard).  My scenario is: We have one C# application that is calling 2008R2 SSIS packages to load text fi…
Lessons learned during ten years of interviewing for SQL Server Integration Services (SSIS) and other Extract-Transform-Load (ETL) contract roles and two years of staff manager interviewing contractors.
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

744 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now