ODBC-based to OLE DB-based data transfer

I would like to transfer selected data from an ODBC-based table to a OLEDB-based table. However, there isn't a data flow source on the Data Flow Design screen to accomodate such an action. Please help!

Roberto,

You need to create a package with a DataFlow task in it; then inside of the DataFlow; then from the toolbox you can use a Data Reader component to use an ODBC connection. Additionally you will need to add any data transformation logic/tasks and an OLE DB destination component.

There is not such 'all-in-one' task to perform what you want; but you have many components that you can combine together to get it

Rafael Salas

ODBC-based to OLE DB-based data transfer

I would like to transfer selected data from an ODBC-based table to a OLEDB-based table. However, there isn't a data flow source on the Data Flow Design screen to accomodate such an action. Please help!

Roberto,

You need to create a package with a DataFlow task in it; then inside of the DataFlow; then from the toolbox you can use a Data Reader component to use an ODBC connection. Additionally you will need to add any data transformation logic/tasks and an OLE DB destination component.

There is not such 'all-in-one' task to perform what you want; but you have many components that you can combine together to get it

Rafael Salas

ODBC/OLE DB Error Code 0

Hey All:
Getting Code 0 errors in ActiveX scripts within DTS & can't trap the decription(s).
I remember seeing an OLD Thread on expanding the Code 0 errors but can't find it.
RobbieDcopy the contents of your dts into a notepad, save it as vbs file, after putting msgbox in places where you know or think errors occur, and go get them, tiger! happy debugging :)|||I did something similar. Copied the code into InterDev & got what I was looking for.

Also, realized that the procedure was flawed (BAD Code, BAD!) in that I was attempting to capture the error from one ActiveX module in a SECOND on Failure. Obviously, the err.Description would no longer exist!!!

ODBC vs OLE DB ??

Here is an except from the book "SQL Server 2000 Weekend Crash Course"
OSQL-A command-line utility used to connect to SQL Server and execute
Transact-SQL statements (covered in Session 8). The results of the executed
commands will be displayed in the DOS console window. OSQL uses the ODBC
(Open Database Connectivity) interface to connect to SQL Server.
ISQL-Uses the DB-Library interface to communicate with SQL Server. It is a
database-specific interface and is not portable. In addition to this, ISQL
does not support many of the new features in Microsoft SQL Server 2000.
Another Excerpt: In SQL Server 2000, all utilities except isql use the ODBC
API and the SQL Server ODBC Driver.
The implication being that ODBC is the way forward. I read another
newsgroup article that said the recommend way forward is in fact OLE DB and
not ODBC. I have come from Sybase where I have been using Ct-Lib almost
exclusively and not needed anything like OLE DB. What advantage does it
offer over ODBC? Is it genuinly easy to get up and running in a matter of
hours, or am I going to spend a week trying to understand it, and another
week getting it to function. Does any existing coding have to change?
Whats my upside/downside for switching to OLE-DB?
Thanks
What should drive this decision is your choice of client application.
ODBC is no longer cutting-edge, having been superceded by OLE DB
(ADO). This is what you'd use with a VB6 or older COM client. However,
if you are working in any of the .NET technologies, then you'll want
to use ADO.NET (coding in either C# or VB.NET) using the SqlClient
provider.
--Mary
On Wed, 19 May 2004 20:20:14 +0200, "Grant Reid"
<grant.reid@.telefonica(dot)net> wrote:

>Here is an except from the book "SQL Server 2000 Weekend Crash Course"
>OSQL-A command-line utility used to connect to SQL Server and execute
>Transact-SQL statements (covered in Session 8). The results of the executed
>commands will be displayed in the DOS console window. OSQL uses the ODBC
>(Open Database Connectivity) interface to connect to SQL Server.
>ISQL-Uses the DB-Library interface to communicate with SQL Server. It is a
>database-specific interface and is not portable. In addition to this, ISQL
>does not support many of the new features in Microsoft SQL Server 2000.
>Another Excerpt: In SQL Server 2000, all utilities except isql use the ODBC
>API and the SQL Server ODBC Driver.
>The implication being that ODBC is the way forward. I read another
>newsgroup article that said the recommend way forward is in fact OLE DB and
>not ODBC. I have come from Sybase where I have been using Ct-Lib almost
>exclusively and not needed anything like OLE DB. What advantage does it
>offer over ODBC? Is it genuinly easy to get up and running in a matter of
>hours, or am I going to spend a week trying to understand it, and another
>week getting it to function. Does any existing coding have to change?
>Whats my upside/downside for switching to OLE-DB?
>Thanks
>

ODBC vs OLE DB ??

Here is an except from the book "SQL Server 2000 Weekend Crash Course"
OSQL-A command-line utility used to connect to SQL Server and execute
Transact-SQL statements (covered in Session 8). The results of the executed
commands will be displayed in the DOS console window. OSQL uses the ODBC
(Open Database Connectivity) interface to connect to SQL Server.
ISQL-Uses the DB-Library interface to communicate with SQL Server. It is a
database-specific interface and is not portable. In addition to this, ISQL
does not support many of the new features in Microsoft SQL Server 2000.
Another Excerpt: In SQL Server 2000, all utilities except isql use the ODBC
API and the SQL Server ODBC Driver.
The implication being that ODBC is the way forward. I read another
newsgroup article that said the recommend way forward is in fact OLE DB and
not ODBC. I have come from Sybase where I have been using Ct-Lib almost
exclusively and not needed anything like OLE DB. What advantage does it
offer over ODBC? Is it genuinly easy to get up and running in a matter of
hours, or am I going to spend a week trying to understand it, and another
week getting it to function. Does any existing coding have to change?
Whats my upside/downside for switching to OLE-DB?
ThanksWhat should drive this decision is your choice of client application.
ODBC is no longer cutting-edge, having been superceded by OLE DB
(ADO). This is what you'd use with a VB6 or older COM client. However,
if you are working in any of the .NET technologies, then you'll want
to use ADO.NET (coding in either C# or VB.NET) using the SqlClient
provider.
--Mary
On Wed, 19 May 2004 20:20:14 +0200, "Grant Reid"
<grant.reid@.telefonica(dot)net> wrote:

>Here is an except from the book "SQL Server 2000 Weekend Crash Course"
>OSQL-A command-line utility used to connect to SQL Server and execute
>Transact-SQL statements (covered in Session 8). The results of the executed
>commands will be displayed in the DOS console window. OSQL uses the ODBC
>(Open Database Connectivity) interface to connect to SQL Server.
>ISQL-Uses the DB-Library interface to communicate with SQL Server. It is a
>database-specific interface and is not portable. In addition to this, ISQL
>does not support many of the new features in Microsoft SQL Server 2000.
>Another Excerpt: In SQL Server 2000, all utilities except isql use the ODBC
>API and the SQL Server ODBC Driver.
>The implication being that ODBC is the way forward. I read another
>newsgroup article that said the recommend way forward is in fact OLE DB and
>not ODBC. I have come from Sybase where I have been using Ct-Lib almost
>exclusively and not needed anything like OLE DB. What advantage does it
>offer over ODBC? Is it genuinly easy to get up and running in a matter of
>hours, or am I going to spend a week trying to understand it, and another
>week getting it to function. Does any existing coding have to change?
>Whats my upside/downside for switching to OLE-DB?
>Thanks
>

ODBC vs OLE DB

Hi All, does anybody know the pros and cons of using ODBC to connect an Access front end to an SQL backend, vs using Access projects.
ThanksAre you thinking of migrating an existing application or starting a new project. In case of migration, ODBC gets things running much faster and you have all that nifty access-features you are used to work with. Porting to adp ends up with a lot of rewriting. On the other hand, you have immediate access to stored procedures, views, triggers, transactions etc. that are more then useful.
ADP is, uhm, not-so-well documented imo. You are forced to use ADO which is somehow inconvinient in the beginning but more robust in my experience.
So if you are migrating, it mainly depends on your need to use SQL-Server features.
If you are starting a new project and have the time (and nerve) to play around with something new, I'd recommend to go for adp.|||My case is the second one. I am thinking of starting a new Project.
The only think that made me think twice about it is that when I connect to my SQL backend, its faster than my adp file that is connected to the same SQL database from the same machine. Maybe thats because I don't have a lot of data at this point.

Thanks

Originally posted by chrisp_999
Are you thinking of migrating an existing application or starting a new project. In case of migration, ODBC gets things running much faster and you have all that nifty access-features you are used to work with. Porting to adp ends up with a lot of rewriting. On the other hand, you have immediate access to stored procedures, views, triggers, transactions etc. that are more then useful.
ADP is, uhm, not-so-well documented imo. You are forced to use ADO which is somehow inconvinient in the beginning but more robust in my experience.
So if you are migrating, it mainly depends on your need to use SQL-Server features.
If you are starting a new project and have the time (and nerve) to play around with something new, I'd recommend to go for adp.|||ODBC connections seem to be faster locally. My expieriences for network connections are exactly the opposite.

You should use Acess 2002 (adp is new for 2000 and not that stable;) and you should make yourself familiar with Enterprise Manager and Queray analyzer (if you aren't already). Then you have data and presentation separated, a fairly good ide and admin environment and all MSSQL features at hand.

We migrated a large project late 2001 and are since working with adp and I'm quite happy with it.

ODBC vs OLE DB

The ODBC processing via DTS is slower than OLE DB which resulted following
inquiries:
- Why it so slow (ODBC)?
- Any idea how to overcome it
I use ODBC to communicate between IBM AS/400 DB2 and MS-SQL Server 2000.
Please advise.
Thanks.Might be your ODBC driver that is not performing OK, check out the
StarQuest products (www.starquest.com), they have ODBC drivers and
fast DB2 to SQL Server replication solutions.
Bob
"samuel" <samuellai@.ajikl.com.my> wrote in message news:<O05WtWbAEHA.3824@.TK2MSFTNGP09.phx.
gbl>...
> The ODBC processing via DTS is slower than OLE DB which resulted following
> inquiries:
> - Why it so slow (ODBC)?
> - Any idea how to overcome it
> I use ODBC to communicate between IBM AS/400 DB2 and MS-SQL Server 2000.
> Please advise.
> Thanks.

ODBC to linked server

I have created a linked server using an OLE DB service in a SQL Server 2000
database running on Windows NT 4.
I can see all the tables and views of the linked server in Enterprise
manager, but when I go to a client machine and try to setup a DSN to the
linked server using an SQL Server driver all I can see for a default
database is the example Microsoft databases (Northwind, Pub, etc.). How do I
get the ODBC driver to use the linked server as a default database?
Any help would be greatly appreciated.ODBC cannot "see" linked servers. You can issue queries using the fully qua
lified server.db.dbo.tablename to be able to select from the table using a p
XXX-through query

ODBC lookup

Hi,

I have to do a lookup in a table that I can only access through ODBC. However it seems the lookup component only allows an OLE DB connection manager.

Is there any way to do an ODBC lookup ?

thanks in advance ...

Not that I have seen. You have 2 other options though: 1. Use a script component and write code (connect and pull the table in memory in the preexecute, use lookups to the table in memory in the ProcessInputRow section). 2. Add a DataReader source and sort + join with the join operator. Which one is best to use depends on your situation. Neither of them is ideal - option 1 in my experience is usually the fasted but obviously more work to develop.