Re: SSIS is pathetic with ODBC connections ...

(jags_32@xxxxxxxxx) writes:
Could you clarify on "MSDASQL, OLE DB over ODBC"? What is MSDASQL, OLE
DB?

OLE DB is an general API for communicating with databases that is COM-based.
It appeared on the scene around 1998, and for a while this was the top of
the pops. However, it never became the smashing success that Microsoft
intended.

The main difference between OLE DB and ODBC as general APIs is that ODBC
assumes that the data source is relational, OLE DB does not. So you
can use OLE DB to access text files or Active Directory. However, OLE DB
is a more complex API, and applications which uses the OLE DB API directly,
are likely to become very verbose.

Now, most applications that uses OLE DB, use some other high-level API,
like ADO. Or OleDB Client in .Net.

For a data source like SQL Server, OLE DB is very much alive. But for
many smaller data sources, no one ever came around to implement an
OLE DB provider, and apparently there is not one for Double Byte Progress
that you use.

However, the first OLE DB provider that saw the light of day was MSDASQL,
which implements the OLE DB API on top of ODBC. Which means that everyhing
that has an ODBC driver still can be accessed from OLE DB. Maybe not
optimally, but it can be accessed.

Currently you use OdbcClient in .Net and you have problems with it.
I'm not going to promise that MSDASQL will fare any better, but I think
you should give it a try. I've tried using OdbcClient with SQL Server
and that did not work well.

So in your SSIS package, try replacing the DataReader source with an
OLE DB Source, and, oops! As I actually looked into BIDS and found
my way to the Connection Manager, I find that MSDASQL is not listed.
Drat! There goes my theory.

That was quite a long reply for nothing.

--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
.

Relevant Pages

  • Re: C++ API for SQL Server?
    ... Is there a C++ API for making native calls to SQLServer ... without having to go through the ODBC layer. ... implements both an ODBC driver and an OLE DB provider for SQL Server. ... The OLE DB interface is more verbose, and using the API directly in your ...
    (comp.databases.ms-sqlserver)
  • Re: C++ API for SQL Server?
    ... Is there a C++ API for making native calls to SQLServer ... without having to go through the ODBC layer. ... implements both an ODBC driver and an OLE DB provider for SQL Server. ... The OLE DB interface is more verbose, and using the API directly in your ...
    (comp.databases.ms-sqlserver)
  • Re: SSIS is pathetic with ODBC connections ...
    ... OLE DB is an general API for communicating with databases that is COM-based. ... For a data source like SQL Server, OLE DB is very much alive. ... OLE DB provider, and apparently there is not one for Double Byte Progress ...
    (comp.databases.ms-sqlserver)
  • Re: Searching code samples/Doku of SQL Server Native Client programmin
    ... The SQL Server samples (which include OLE DB and ODBC API samples) are here: ... I am searching for code samples of SQL Server Native Client ODBC or OLEDB ...
    (microsoft.public.sqlserver.programming)
  • Re: SQL Server
    ... The naked OLE DB API: leads to very verbose and repetive code. ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ...
    (comp.databases.ms-sqlserver)