SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
fellmeth
fellmeth
Valued Member
Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)

Group: General Forum Members
Points: 58 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]
steveb.
steveb.
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4386 Visits: 7195
does the agent account have access to the destination?
fellmeth
fellmeth
Valued Member
Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)

Group: General Forum Members
Points: 58 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.
WayneS
WayneS
SSCrazy Eights
SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)

Group: General Forum Members
Points: 9862 Visits: 10572
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
Author - SQL Server T-SQL Recipes
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

fellmeth
fellmeth
Valued Member
Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)

Group: General Forum Members
Points: 58 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?
happycat59
happycat59
SSCarpal Tunnel
SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)

Group: General Forum Members
Points: 4744 Visits: 3218
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



fellmeth
fellmeth
Valued Member
Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)

Group: General Forum Members
Points: 58 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.
WayneS
WayneS
SSCrazy Eights
SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)

Group: General Forum Members
Points: 9862 Visits: 10572
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
Author - SQL Server T-SQL Recipes
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

fellmeth
fellmeth
Valued Member
Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)

Group: General Forum Members
Points: 58 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.
Sunny1
Sunny1
SSC Journeyman
SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)

Group: General Forum Members
Points: 89 Visits: 745
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search