Solved

SSIS SQL Server Agent schedule Job

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Insert to Begin if data exists 2 33
Is there a better way to combine two queries 7 27
SQL Server / Update DB? 22 38
Parse this column 6 27
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
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.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

828 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