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