Tuesday, November 15, 2011

Running SSIS packages in 32 bit mode in 64 bit server

The impetus for this blog is recent issue faced by use in running SSIS package from a job created in SQL Server agent. The package was locally developed and tested fine. After deploying package to server and trying to run it from SQL server agent however gave us the below error

The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine

We investigated the cause and found out that the reason was OS version is 64 bit and ACE provider was not supported in it. We had no clue how to fix it before somebody noticed the below property available inside the sql agent job step window



This fixed the error as setting the above would run the package under 32 bit mode and will support ACE provider used inside.

BIDS
-----------------------------
Similarly if you're running the package inside bids you can find similar property inside project properties


Setting this would also have same effect as above

Command line
------------------------------------------
If you're choosing to execute the package from command line using dtexec and have to run it in 32 bit mode then you need to call 32 bit version of dtexec. Under default installation, it can be found at path C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn

4 comments:

  1. The documentation for dtexec indicates that the /x86 switch is only valid for SQL Agent.

    "This option is only used by SQL Server Agent. This option is ignored if you run the dtexec utility at the command prompt."

    One must start with the correct "bit-ed" dtexec to get the proper results from the command line.

    ReplyDelete
    Replies
    1. Yes thats correct. Didnt explain it properly here I guess. While you call from sql agent you need to set /x86 switch and while calling from command line you need to call the 32 bit version of dtexec. I have already explained this at

      http://visakhm.blogspot.in/2011/12/dtexec-issues-running-ssis-packages-in.html

      I will do correction here as well.
      Thanks for the catch

      Delete
  2. Super thanks a million times. Been struggling with this issue for 2 days and been searching for an answer. Kudos for sharing. Can breath a sigh of relief now.

    ReplyDelete
  3. You're welcome
    Glad to hear that this post helped you in fixing the issue.

    ReplyDelete