posted on Thursday, April 27, 2006 8:37 PM by admin

Using ODBC and ADO.Net to Connect SSIS to DB2

I needed to connect SSIS to a DB2 database via ODBC today and learned something worth blogging about: There are different ways to use ODBC connections in SSIS. It seems I learn some cool, new, flexible function about SSIS every time I take it out for a spin!

I had already installed the IBM DB2 client for Windows and tested the ODBC connection it created, so I knew I had good credentials and could access the data I wanted. It's important to check this before proceeding.

I created a new project to test different connection flavors.

I right-clicked in the Connection Managers workspace and selected "Create Connection."

 

Next, I selected ODBC as the type of connection:

I then clicked the New button on the "Configure ODBC Connection Manager":

The Connection Manager opened and I entered the name of the test server's data source name; followed by my login credentials:

I then clicked the "Test Connection" button. It failed because I mistyped my password, displaying the following dialog:

When I corrected my password, the test succeeded:

I clicked OK to close the Connection Manager...

...and the clicked OK again to close the "Configure ODBC Connection Manager" dialog. A new connection appeared in the connection managers workspace:

I like to rename the connection to something more descriptive, so I appended a dot and the connection type (i.e. ".ODBC") to the connection name:

Next I created a new ADO.Net connection:

When the dialog appeared, I selected the "Odbc Data Provider" provider type: 

I then supplied the DSN and credentials as before:

After configuring the connection, I clicked OK and dragged a Data Flow onto the workspace:

I next opened the Data Flow and dragged a DataReader Source onto the data flow:

I double-clicked the DataReader to edit it, and the Advanced Editor. I then toggled between the two connections, hitting the Refresh button to update the feedback:

When set to the ODBC connection, the DataReader cannot acquire a managed connection. I could not make the DataReader work under these conditions:

When I changed the connection to the Ado.net connection and clicked the Refresh button, the feedback vanished, indicating the DataReader is prepared to use this connection object:

That's it! In sum, I was able to use the ODBC connection - but only through the ADO.Net connection.

:{> Andy

Technorati Tags: SSIS Sql Server DB2 Developer Community Richmond Virginia

 

Comments