I don’t know if the heading means anything, it just popped out of my mind. ok… so we are talking about pulling data from hetero systems into SQL Server using 3rd party drivers.

we all know how to get connected using OLE DB Providers, but what about ODBC and that too working against MainFrame DB2 and NonStop Tandem.. wow!

I am going to talk about about HP NONSTOP TANDEM particularly in this scenario. There are a few drivers i worked with.. Attunity software / ODBC-MX and Sequelink software. While Attunity and Sequelink are direct competitors and provide the luxury of OLE DB and ODBC drivers, which ODBC-MX as the name suggests is a Pure ODBC only.

But ODBC-MX is a version 3.X of the ODBC Family while Attunity and Sequelink are still at version 2.

So does this affect us? Well Microsoft is moving away from version 2.x of ODBC drivers starting its release of Excel 2010 versions and will only support version ODBC version 3.X.. This is my GUESS as i had been working with EXCEL and Power Pivot.

Now back to the main Topic of getting “ODBC connected through SSIS for ETL.”

First create a ODBC connection (DSN) in Administrative Tools –> ODBC

I Created a new connection in connection manager in BID-SSIS using ADO.NET Connection, then click on NEW..

I will not be able to Reveal all the settings, (you know why).. against company Law… but this is still as close as i get get to present it.

so select ODBC from the Provider List.

now select the dsn (ODBC) you created initially.

using the username and pass, test your connection. (Mine Works all the times)

Goto DataFlow task and Pull “DataReader Source” as your Source connection.

In the Connection Manager Field use the connection you created using the SSIS connection Manager.

Now go to Component Properties: Click on the SQLCommand and we will write a sweet command, because as you guessed right, sorry no direct table selections and column mapping unless you provide column names as a select stm.

I am going to write a simple select as a example for this tutorial. (but the code is hidden, and you know why)

After the sql command is complete, then click on column mapping and LO!, you have a column mapping..

now go ahead a dump this data into a txt target or a OLEDB (Database Table) as a target. Rest is history.