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