ODBC Data Source

I am new to SSIS packages and want to use an ODBC data source connected to an old Btrieve set of data files. I set up the ODBC DSN, and my SQL Server Data Connection, and can browse the tables in the Server Explorer. However with regards to my SSIS package, I'm not sure whether to use an OLE DB Source or Data Reader Source. I have futzed with both of them but haven't had any luck at seeing the tables. Can someone please give me some tips.

Thanks,
Carson

Carson,

Use the OLE DB Source. That's generally regarded as the default option and, I think, will be quicker than datareader source.

-Jamie

|||

Then this is my issue with the OLE DB Source. I go into the OLE DB Source Editor, nothing is listed in my OLE DB Connection Manager dropdown, so I click the "New..." button.

The Configure OLE DB Connection Manager window pops up, but nothing is listed under Data Connections, the listbox is blank. So I click the "New..." button on this popup window. (Keep in mind, in my Server Explorer I have two Data Connections my localhost and the ODBC DSN i explained in my first post. Also, in the bottom of the package, under the tab Connection Managers I also have defined an ODBC DSN connection by right clicking and selecting New Connection and then ODBC.)

Then up comes the Connection Manager popup, yet ODBC is not in the drop-down list of Providers like it was when I created a Connection Manager by right-clicking in the section Connection Manager found underneath the package DataFlow.

I do not have an OLE driver for this old Btrieve data source, only a working ODBC driver.

Please advise.

Thanks!

|||

Carson,

The only way to use ODBC connections in SSIS is by using ADO .NET provider for ODBC. That implies you have to use Data Reader source with it. There is no a custom UI for the Data Reader source component so you would need to type your query (select * from <tableName> or equivalent one) directly into the SQLCommand property. You will not be able to see available tables.

HTH.

|||

Thank you for this explanation, that helps. I'm assuming that before I can set the SQLCommand property, I have to set the Connection Manager in the first tab of the Advanced Editor. When I do, I get the following error: "Error at Data Flow Task [DataReader Sourcer [175]]: Cannot acquire a managed connection from the run-time connection manager." Can someone please explain what to do with this error?

I created the connection manager for my ODBC data source by right-clicking in the area directly under the package data flow UI, and selecting "New Connection", then selecting "ODBC", then I create a new connection manager by setting the system data source name to the ODBC DSN that I created. The same DSN I can browse the tables with in the Server Explorer.

Please advise.

Thanks!!

|||

You have to choose "New ADO .NET Connection..." from the menu, click on the "New..." button of the next dialog, select .NET Providers/Odbc Data Provider in the first drop-down of the following dialog and then choose your DSN.

This way created connection can now be used in the advanced UI of the Data Reader Source.

Thanks.

|||Bob - You 'Da Man!! That worked!|||

Bob, where is the option for new ado .net connection?

Thanks

|||

Right-click in the "Connection Managers" window and then follow my previous post.

HTH.