32-Bit ODBC Connections with 64-Bit SSIS

I’ve been working on a project with a customer to build an ETL process that pulls data from a Polyhedra DBMS to a SQL Server 2008 R2 server.  The original plan was pretty basic:

  • create SSIS on SQL Server to pull data from Polyhedra at periodic intervals
  • land the data in a staging location on SQL Server
  • perform some transforms and business rules on the staged data
  • land the cleaned data in the data warehouse for reporting

The ETL plan is basically the same however, getting to a solution was not a smooth as originally estimated.

Technical Challenges

1- Getting Connectivity Drivers

The SQL Server is 64-bit but there are no 64-bit connectivity drivers available for the Polyhedra platform.  The manufacturer only has a 32-bit ODBC driver.  Installation and configuration of a DSN was relatively straightforward.

Always remember to use the 32-bit odbcad32.exe in the SysWOW64 folder to configure a 32-bit ODBC driver on your 64-bit Windows Server.

2- Must Use ADO.NET Source

In order to access the data in the Polyhedra database from SSIS we created a connection manager from the ODBC Data Provider.  This means the ADO.Net Source must be used in the Data Flow because OLE Sources do not recognize ODBC connection managers.

We needed the SqlCommand property to be dynamic so the date range of the WHERE clause could change at run-time.  ADO.Net sources do not allow expressions to be used on their properties nor do they allow a SqlCommand based on a variable.

Frustration ensued … we’re required to use the ADO.Net Source due to the ODBC connectivity restriction but unable to use expressions to create the dynamic query required to pull the data.

An odd solution is to create an expression in the Data Flow task that contains the ADO.Net Source.  From the Data Flow task expression property you are able to access the ADO.Net Source SqlCommand property to build a dynamic query.

3- Preview Works – Execution Fails??

Once we got the query syntax working (date formatting can be so touchy on non-SQL Server platforms, bleh) we could preview the data in the ADO.Net source and data was returned.

Next step run the package and import some rows, that fails:

System.Data.Odbc.OdbcException: ERROR [IM014] [Microsoft][ODBC Driver Manager] The specified DSN contains an architecture mismatch between the Driver and Application

The problem is the BIDS is a 32-bit application so previewing the data works great because 32-bit BIDS can access the 32-bit DNS created earlier.

SSIS packages are run using dtexec.exe which is 64-bit on a 64-bit machine, it can no longer access the 32-bit DSN.  This is easily resolved in your BIDS project by going to the project properties > Configuration Properties > Debugging > Run64BitRuntime = False

4- Don’t Forget, Job Configuration

Once the SSIS package is all done and working great in your development environment you’ll deploy the package to the server and schedule it with a Job.  The Job Step for package execution must also be configured to use the 32-Bit run-time.

Set this by opening the Job properties > Steps > select the SSIS package execution step > Edit > General page > Execution Options tab > check the Use 32 bit runtime box.

3 Responses to 32-Bit ODBC Connections with 64-Bit SSIS

  1. Eric Lawson says:

    Hi DBA Dave,

    I am trying to access a Tandem NonStop SQL / MP database from SSIS and seem to be having similar issues to you. I have managed to create the ODBC connection manager, but I am struggling with the Data Flow source. You suggest using ADO.NET but I don’t seem otbe able to access the ODBC connection manager via this object type. Are you able to provide a screen shot example of how you did this or explain.

    Thanks
    Eric

  2. DBA Dave says:

    Hi Eric,
    When you create a new ADO.NET Connection Manager there is a Provider combo box at the very top of the dialogue. This is where you change from the default of SqlClient Data Provider to Odbc Data Provider. Then you can configure the ADO Source to use the ODBC connection you’ve created on your server.

  3. Chris Combs says:

    I am having trouble writing to Teradata thru odbc primarily due to the fact that SSIS will not recognize a 64-bit Teradata oDBC connection even though both 32-bit and 64-bit odbc connectivity are established.

Leave a Reply

Your email address will not be published. Required fields are marked *