Solved

SSIS SQL Server Agent schedule Job

Posted on 2014-10-15
6
1,452 Views
Last Modified: 2016-02-11
Hello Experts Exchange
I have several SSIS packages that Import data from Excel into SQL Server, when I run the packages manually in Microsoft Visual Studio they run fine and import the data.

I have now scheduled the packages to run in SQL Server Agent Job and have the following error;

Message
Executed as user: DMUKAD\plgrrock. Microsoft (R) SQL Server Execute Package Utility  Version 11.0.5058.0 for 64-bit  Copyright (C) Microsoft Corporation. All rights reserved.    Started:  16:27:44  Error: 2014-10-15 16:27:44.34     Code: 0xC0209303     Source: Lost_Time_H1 Connection manager "SourceConnectionExcel"     Description: The requested OLE DB provider Microsoft.Jet.OLEDB.4.0 is not registered. If the 64-bit driver is not installed, run the package in 32-bit mode. Error code: 0x00000000.  An OLE DB record is available.  Source: "Microsoft OLE DB Service Components"  Hresult: 0x80040154  Description: "Class not registered".  End Error  Error: 2014-10-15 16:27:44.34     Code: 0xC020801C     Source: Data Flow Task 1 Source - 'H1$' [50]     Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager "SourceConnectionExcel" failed with error code 0xC0209303.  There may be error messages posted before this with more information on why the AcquireConnection method call failed.  End Error  Error: 2014-10-15 16:27:44.34     Code: 0xC0047017     Source: Data Flow Task 1 SSIS.Pipeline     Description: Source - 'H1$' failed validation and returned error code 0xC020801C.  End Error  Error: 2014-10-15 16:27:44.34     Code: 0xC004700C     Source: Data Flow Task 1 SSIS.Pipeline     Description: One or more component failed validation.  End Error  Error: 2014-10-15 16:27:44.34     Code: 0xC0024107     Source: Data Flow Task 1      Description: There were errors during task validation.  End Error  DTExec: The package execution returned DTSER_FAILURE (1).  Started:  16:27:44  Finished: 16:27:44  Elapsed:  0.172 seconds.  The package execution failed.  The step failed.

Has anyone see this error before, how do I fix the error?

Regards

SQLSearcher
0
Comment
Question by:SQLSearcher
  • 4
6 Comments
 
LVL 65

Expert Comment

by:Jim Horn
ID: 40382451
>Connection manager "SourceConnectionExcel"     Description: The requested OLE DB provider Microsoft.Jet.OLEDB.4.0 is not registered.
If an SSIS package is executed in SQL Server Agent, then all DSN's / drivers for connetions that are on your dev box need to also be installed on the server.  Otherwise, it will throw a connection error like above.
0
 

Author Comment

by:SQLSearcher
ID: 40383770
Hello Jim
I developed the SSIS packages on the same server as I'm trying to run the SQL Server Agent.

Where will I find and how do I install Microsoft.Jet.OLEDB.4.0?

Regards

SQLSearcher
0
 

Author Comment

by:SQLSearcher
ID: 40383795
Hello Experts Exchange
I now have a new problem which is telling me the file does not exist, but the file does exist on a mapped network drive.

Executed as user: DMUKAD\plgrrock. Microsoft (R)
SQL Server Execute Package Utility  Version 11.0.5058.0 for 32-bit  Copyright (C)
Microsoft Corporation. All rights reserved.    Started:  08:41:55  Error: 2014-10-16 08:41:57.93    
Code: 0xC0202009     Source: Lost_Time_H1 Connection manager "SourceConnectionExcel"    
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: "'Y:\Summary\Lost Time.xls' is not a valid path.  
Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides.".
 End Error  Error: 2014-10-16 08:41:57.93     Code: 0xC020801C    
Source: Data Flow Task 1 Source - 'H1$' [50]    
Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  
The AcquireConnection method call to the connection manager "SourceConnectionExcel" 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: 2014-10-16 08:41:57.93     Code: 0xC0047017     Source: Data Flow Task 1 SSIS.Pipeline    
Description: Source - 'H1$' failed validation and returned error code 0xC020801C.  
End Error  Error: 2014-10-16 08:41:57.93     Code: 0xC004700C     Source: Data Flow Task 1 SSIS.Pipeline    
Description: One or more component failed validation.  End Error  Error: 2014-10-16 08:41:57.93    
Code: 0xC0024107     Source: Data Flow Task 1      Description: There were errors during task validation.  
End Error  DTExec: The package execution returned DTSER_FAILURE (1).  Started:  08:41:55  Finished: 08:41:57  
Elapsed:  2.356 seconds.  The package execution failed.  The step failed.


How do I resolve?

Regards

SQLSearcher
0
 

Accepted Solution

by:
SQLSearcher earned 0 total points
ID: 40391966
Hello Experts Exchange
I have resolved the error messages.
The first error message was resolved by following the action on the following web site;
http://www.sqlhammer.com/blog/running-ssis-packages-in-32-bit/

The second error was resolved by entering a full UNC path to my file, instead of using a mapped network drive letter path to my file.

Regards

SQLSearcher
0
 

Author Comment

by:SQLSearcher
ID: 40391978
I've requested that this question be closed as follows:

Accepted answer: 0 points for SQLSearcher's comment #a40391966

for the following reason:

Found my own solutions.
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

In this article I will describe the Copy Database Wizard 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.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

759 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

17 Experts available now in Live!

Get 1:1 Help Now