Solved

SSIS SQL Server Agent schedule Job

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
T-SQL:  I Want "Summary"--Not "Detail" 6 22
Columnstore Indexes - real-time operational analytics 1 15
Sql query 107 30
Help Extract Specific in SQL 8 27
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
A couple of weeks ago, my client requested me to implement a SSIS package that allows them to download their files from a FTP server and archives them. Microsoft SSIS is the powerful tool which allows us to proceed multiple files at same time even w…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

862 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

23 Experts available now in Live!

Get 1:1 Help Now