ODBC Data Source error

As other contributors, all I am trying to do is import data from an ODBC source (spelled 'non-Microsoft data source') into a SQL 2005 table. I can easily do this in SQL 2000 with DTS, but when I use the same DSN in VS 2005 it doesn't work.

I created an integration project, and made a connection in Connection Manager to the DSN and clicked Test Connection. It succeeded, or so it claimed.

Click OK and drag a DataReader Source onto the Data Flow surface Doubleclick it and select the connection manager per above. Note the error: Error at Data Flow Task [DataReader Source[50]]: Cannot acquire a managed connection from the run-time connection manager.

What does that mean? More to the point, how to fix it?

I wonder if you are using an ODBC connection manager? That will not work with the Data Reader Source.

The ODBC connection manager uses native ODBC and can be used with the ExecuteSQL Task.

The DataReader source needs an ADO.Net Connection Manager which can in turn reference a DSN through the ODBC Data Provider for .Net.

Donald Farmer

|||

Donald, thanks for your reply. Clearly I am missing something, though. In the designer, the Execute SQL task is in the toolbox for Control Flow, and the Ole Db Destination task is in the toolbox for Data Flow. There is no task in Data flow that I can find that will map columns from the ODBC source to the OLE DB destination, and there doesn't appear to be a task in Control Flow to do this either.

Again, I am trying to append records from an ODBC table to a SQL 2005 table. The tables have identical column names and equivalent data types. What do I need to do?

Thanks,

Steve

|||

ODBC is supported in the data flow by the Data Reader source adapter. I think the original problem may have been that you were trying to use the Data Reader source with an ODBC connection manager.

To extract data from an ODBC source, try the following:

Add an ADO.Net Connection Manager.|||

Thank you so much - that was the problem. I have another problem, but it may be the ODBC driver itself - for some reason, it is showing all the string fields as nvarchar, and the destination fields are varchar. Is there a way of globally converting Unicode to non-Unicode?

|||

Your suggestion works for establishing the connection in the dataflow, but the data transfer rate is extremely slow compared to SQL 2000 DTS. In SQL 2000 DTS, we can retrieve just under half a million records from Lotus Notes in about 13 minutes using the NotesSQL ODBC driver 3.02g in a System DSN. Utilizing the technique you describe above with the same DSN on the same machine as SQL 2000 DTS, the same transfer takes about 57 minutes in SQL 2005 SSIS.

Is there anything that can be done to improve the performance in SSIS to retrieve data from NotesSQL via ODBC? Thanks!

sql