Solved

SSIS SQL Server Agent schedule Job

Posted on 2014-10-15
6
1,497 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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how the fundamental information of how to create a table.

803 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