November 24, 2010 at 12:44 pm
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]
November 24, 2010 at 4:11 pm
does the agent account have access to the destination?
November 24, 2010 at 6:40 pm
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.
November 24, 2010 at 6:59 pm
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
November 24, 2010 at 7:47 pm
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?
November 24, 2010 at 8:50 pm
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
November 24, 2010 at 9:37 pm
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.
November 25, 2010 at 4:54 am
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
November 25, 2010 at 1:37 pm
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.
January 19, 2011 at 10:44 am
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.
January 19, 2011 at 10:50 am
Thanks, Grasshopper. I'd forgotten about this post. I'll keep your solution in mind for the future. What I discovered a few weeks ago was that we'd been copying existing working packages and then modifying them for the new server. SSIS really does not like this copying and modifying. When we finally gave that up and rebuilt the packages from scratch, all the problems we were having vanished.
September 12, 2012 at 6:25 pm
Hope this link helps you
http://www.sqlserverbox.com/ssis-package-in-sql-server-agent-job-fails.html
http://sqlism.blogspot.com/2012/08/ssis-package-in-sql-server-agent-job.html
SSIS Package in SQL Server Agent Job Fails
Error:
Argument "xyz" for option "connection" is not valid. The command line parameters are invalid. The step failed.
Solution:
If your SQL Server is 64 bit
In SQL Server JOB-->Properties-->Steps--->
In the Execution Option Tab -- Check "Use 32 bit runtime"
In the Data Sources Tab -- Uncheck the checkboxes of the connection managers if already checked.
Schedule the Job and run it
October 2, 2012 at 11:25 pm
Hi all
Not sure if any one is still having this issue but the one change below seemed to resolve the problem for me.
Follow below steps :
1. Right click Solution
2. Select properties
3. Go to Debugging tab
4. Set 64 Bit Run Time to False
5. click Ok and close out
6. Save package
7. Deploy / run package as per normal
Web site address if needed for above steps
Cheers
Vani 🙂
September 30, 2014 at 3:26 pm
October 1, 2014 at 7:37 am
Does it make a difference if you load this into Integration services on the server and secure it with a password?
Most likely Visual studio is taking care of the security of sensitive data (connection strings) for you.
I have run into this issue before even when using Native security etc...
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply