I'm trying to import an RBASE table into SQL Server 2005. In 2000, the RBAS
E
ODBC driver was listed in the drop down list of the import wizard. It is no
t
listed in the 2005 wizard. Is there a fix for this?
Thanks,
MikeTry using the .Net Framework Data Provider for ODBC.
Then enter the DSN or the driver and connection string.
-Sue
On Fri, 26 May 2006 13:29:02 -0700, Mike
<Mike@.discussions.microsoft.com> wrote:
>I'm trying to import an RBASE table into SQL Server 2005. In 2000, the RBA
SE
>ODBC driver was listed in the drop down list of the import wizard. It is n
ot
>listed in the 2005 wizard. Is there a fix for this?
>Thanks,
> Mike|||Thanks Sue...I'll give that a try!
"Sue Hoegemeier" wrote:
> Try using the .Net Framework Data Provider for ODBC.
> Then enter the DSN or the driver and connection string.
> -Sue
> On Fri, 26 May 2006 13:29:02 -0700, Mike
> <Mike@.discussions.microsoft.com> wrote:
>
>
Showing posts with label import. Show all posts
Showing posts with label import. Show all posts
Friday, March 30, 2012
Monday, March 26, 2012
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!
sqlODBC 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!
ODBC Data Source
I'm trying to import tables from COBOL thru Relativity client. With SQL Server 2000, I used Other (ODBC Data Source) to do this. I cannot find a comparible data source in SQL Server 2005. What do I do?
Where are you looking to find this data source?
Mike
|||In the Import wizard.|||Thanks,
The Import wizard is not supported in SQL Express so this isn't the best place to get information about it. I'm going to move this thread into a forum where the folks who should be able to answer this question hang out.
Mike
|||Well, great! We use SQL Express. Won't my boss love this! Thanks for the info.|||There's some very good reasons why SQLExpress is free. You don't get much with it
-Jamie
Subscribe to:
Posts (Atom)