Please Help Solve Why SSIS Package Fails When Run as SQL Agent Job

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » SQL Server Agent » Please Help Solve Why SSIS Package Fails When...

13 posts, Page 1 of 2

1 2 »»

Please Help Solve Why SSIS Package Fails When Run as SQL Agent Job

Rate Topic

Display Mode

Topic Options

Author

Message

fellmeth

Posted Wednesday, November 24, 2010 12:44 PM

Forum Newbie

Group: General Forum Members
Last Login: Saturday, June 01, 2013 8:05 AM
Points: 8, Visits: 70

We have SQL Server 2005 x86 locally. On it we have a SQL Agent Job that runs an SSIS package in MSDB that loads source from a local Access 2007 DB to a destination of remote SQL 2003 x86. This Job has been and continues to work just fine.

We are now trying to run the same Job to a destination of remote SQL 2008 R2 x64, and the Job keeps failing. The package runs just fine from VS and MSDB. But when it's added to the Job, it fails with following error from log. Help much appreciated.

11/24/2010 14:37:42,Daily Website Update,Error,6,SQLSRV,Daily Website Update,BOOKS Table VPS,,Executed as user: DRAMATISTS\svcsql. ... Utility Version 9.00.4035.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 2:37:42 PM Error: 2010-11-24 14:37:42.57 Code: 0xC0202009 Source: BOOKS Table VPS Connection manager "DestinationConnectionOLEDB" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040154. An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040154 Description: "Class not registered". End Error Error: 2010-11-24 14:37:42.57 Code: 0xC00291EC Source: Preparation SQL Task Execute SQL Task Description: Failed to acquire connection "DestinationConnectionOLEDB". Connection may not be configured correctly or you may not have the right permissions on this connection. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 2:37:42 PM Finished: 2:37:42 PM Elapsed: 0.469 seconds. The package execution failed. The step failed.,00:00:00,0,0,,,,0[/color][/color]

Post #1026137

steveb.

Posted Wednesday, November 24, 2010 4:11 PM

SSCrazy

Group: General Forum Members
Last Login: Today @ 4:37 AM
Points: 2,803, Visits: 7,112

does the agent account have access to the destination?

Post #1026253

fellmeth

Posted Wednesday, November 24, 2010 6:39 PM

Forum Newbie

Group: General Forum Members
Last Login: Saturday, June 01, 2013 8:05 AM
Points: 8, Visits: 70

Thanks for taking an interest, Steve. Yes, it does.

I just copied the current working package, changed the Destination to the new server, and added the new package to the Job. So, the only difference between the old package and the new package is the SQL 2008 R2 x64 destination.

I've read up a lot in the Forums on using x86 DTEXEC command if the server running the job is x64 and passing data from Access 2007. But everything here is already x86. It's only the destination server that's x64. So I'm stumped.

Post #1026286

WayneS

Posted Wednesday, November 24, 2010 6:59 PM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:21 PM
Points: 6,388, Visits: 8,290

Well, you've said it all, but you missed it also:
It works on: 32-bit
It fails on: 64-bit
It's trying to connect to: Access database.

Access databases are connected to through the JET database engine drivers. There is NOT a 64-bit version of JET, and there won't be. You will never be able to connect to an Access database (or Excel spreadsheet) through the JET drivers.

However, all is not lost. MS has graciously (after many years of waiting/prodding/bit**ing), provided a set of drivers that are available in 32-bit and 64-bit for access to these files. It is known as the Microsoft Access Database Engine 2010. The 64-bit ACE drivers are available here.

I tried to work with them myself, but it forces me to uninstall Office 2007 - something I'm not willing to do on my laptop ight now. Guess I need to set up a 64-bit VM to do some testing sometime soon!

Wayne
Microsoft Certified Master: SQL Server 2008

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings

Post #1026287

fellmeth

Posted Wednesday, November 24, 2010 7:47 PM

Forum Newbie

Group: General Forum Members
Last Login: Saturday, June 01, 2013 8:05 AM
Points: 8, Visits: 70

Thanks for the reply, Wayne. We installed the ACE drivers when they were first published and are using them sucessfully. It was necessary to do so in order to be able to use the Access source in the original package. (JET limited us to MDB, and this source was ACCDB.)

The connection strings in the original package and the new package in question are both as follows:

Data Source=\\filesrv\publishing\DPS Plays Database\DPS Plays.accdb;Provider=Microsoft.ACE.OLEDB.12.0;

As I noted above, the new package runs fine in both VS and MSDB. It is only the SQL Job that causes it to fail. But why?

Post #1026292

happycat59

Posted Wednesday, November 24, 2010 8:50 PM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 10:20 PM
Points: 2,489, Visits: 2,092

By default, the SQL Agent SSIS step type executes the 64 bit DTEXEC binary.

There may be other workarounds for this but the one that I am using the moment is to change the step to be a "CmdExec" type and explicitly execute the 32 version of DTEXEC from the x86 folder. You can get the command line for the DTEXEC from the existing job definition . The command line would look something like the following

"C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\DTEXEC.Exe" /SQL "Maintenance Plans\Daily Database Backup" /SERVER "YourServerName" /CHECKPOINTING OFF /SET "\Package\Subplan_2.Disable";false /REPORTING E

This example is for one of my SQL2008 servers. You may need to adjust the command line depending on what you have installed and where it is installed to.

Note: the quote marks are important - both for the name of the dtexec binary and in the

Post #1026306

fellmeth

Posted Wednesday, November 24, 2010 9:37 PM

Forum Newbie

Group: General Forum Members
Last Login: Saturday, June 01, 2013 8:05 AM
Points: 8, Visits: 70

Thank you, HappyCat. Are you saying that our x86 SQL 2005 Server executes a 64-bit DTEXEC binary? If that were the case, would we not also see failures with the original package, which continues to run just fine?

Our SQL Server does not even have this path: C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\DTEXEC.Exe". As far as I'm aware, the "Program Files (x86)" folder only exists on x64 machines.

Post #1026312

WayneS

Posted Thursday, November 25, 2010 4:54 AM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:21 PM
Points: 6,388, Visits: 8,290

The 32-bit is running okay... you only need to set it up that way on the 64-bit server

Wayne
Microsoft Certified Master: SQL Server 2008

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Post #1026432

fellmeth

Posted Thursday, November 25, 2010 1:37 PM

Forum Newbie

Group: General Forum Members
Last Login: Saturday, June 01, 2013 8:05 AM
Points: 8, Visits: 70

Sorry, Wayne, I don't understand your suggestion. Set up what on the 64-bit server? The 64-bit ACE drivers are installed on the 64-bit server. The 32-bit ACE drivers are installed locally on the 32-bit server, which runs the Job.

The following command line is impossible as there is no "Program Files (x86)" folder because the local server running the Job is 32-bit, so this cannot work.

"C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\DTEXEC.Exe" /SQL "\Website\BOOKS Table VPS" /SERVER SQLSRV /DECRYPT /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING E

Thoughts? Thanks.

Post #1026575

Sunny1

Posted Wednesday, January 19, 2011 10:44 AM

SSC Rookie

Group: General Forum Members
Last Login: Monday, June 10, 2013 2:16 PM
Points: 26, Visits: 474

I had the same issue on a x64 SQL 2005 server. I used the connection provider "SQL Native Client 10.0" instead of "SQL Native Client" caused the error when it was ran as a job. So I changed the connection to use SQL Native Client and the problem solved.

Post #1050208

« Prev Topic | Next Topic »

13 posts, Page 1 of 2

1 2 »»

Permissions