Re: SSIS is pathetic with ODBC connections ...

On Dec 3, 5:39 pm, Erland Sommarskog <esq...@xxxxxxxxxxxxx> wrote:
(jags...@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, esq...@xxxxxxxxxxxxx

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

I am going around in circles here, lodged a ticket with DataDirect
(ODBC Provider) who pointed me to Progress technologies (DB Provider)
who in turn pointed me to QAD (ERP Application Provider) who is now
pointing me to Microsoft!! I figured someone, somewhere should be
using this technology.

Thanks for your post though. If we cannot do this via SSIS, I
basically explore and recommend another ETL tool that would accomplish
this task.
.

Relevant Pages

  • Re: Yukon BI UI not generating the proper connection string for the SPSS OLE DB Provider.
    ... schemaName in this OLE DB Provider). ... connection = new OleDbConnection; ... When I tried to create a data source view (using ... SPSS OLE DB Provider. ...
    (microsoft.public.sqlserver.datamining)
  • Single Cube from Multiple sources
    ... Provider=OraOLEDB.Oracle.1 (Oracle Provider for OLEDB) ... Error Text: OLE DB error: OLE DB or ODBC error: Cannot initialize the ... name>' secondary data source is not a relational data source, ...
    (microsoft.public.sqlserver.olap)
  • Re: Visual Totals functionality when not using the MSOLAP provider
    ... >>I support an OLAP OLE DB provider for a third party data source. ... >>Doing the same operation with the Analysis Services OLE DB provider MSOLAP.2 ... >>causes OWC11 to generate MDX using the VisualTotals function. ...
    (microsoft.public.office.developer.web.components)
  • Re: How do I format a mergefield?
    ... Changing the data provider to OLE DB has ... "Peter Jamieson" wrote: ... you first need to download the OLE DB provider ... Then, when you connect to your data source, in the Select ...
    (microsoft.public.word.mailmerge.fields)
  • 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. ...
    (comp.databases.ms-sqlserver)