Solved

SSIS SQL Server Agent schedule Job

Posted on 2014-10-15
6
1,544 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

Question has a verified solution.

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

Suggested Solutions

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

740 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