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

  • 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]

  • does the agent account have access to the destination?

  • 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.

  • 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
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • 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?

  • 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

  • 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.

  • 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
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • 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.

  • 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.

  • 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.

  • 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

  • Hi all

    Not sure if any one is still having this issue but the one change below seemed to resolve the problem for me.


    *** NOTE - I did not have to make any changes to the job properties etc ****


    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

    http://social.msdn.microsoft.com/Forums/en-NZ/sqlintegrationservices/thread/71cd55a0-b32d-4b2f-99c8-52d5e3d8cd7c

    Cheers

    Vani 🙂

  • 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