Re: SSIS is pathetic with ODBC connections ...

On Dec 4, 8:37 am, jags...@xxxxxxxxx wrote:
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.

You could try Pentaho Data Integration (http://kettle.pentaho.org/),
formerly known as Kettle. We have been able to query Progress
databases (version 10.0B) using the DataDirect ODBC driver. You could
also play around with the JDBC Progress driver if you want, though I
have had trouble getting it to work. Kettle is Open Source but very
well supported with a decent set of documentation.
.

Relevant Pages

  • Re: Reading mm datasource
    ... Word is trying to connect using the OLE DB provider ... (this is what Word generates when you try to connect manually using ODBC) ... both the OLE DB provider and ODBC driver that I have here (as ...
    (microsoft.public.word.mailmerge.fields)
  • Re: Ole db provider for an odbc data source
    ... We now wish to wrap this odbc driver to be able to provide an ole db ... That is an OLE DB provider that comes with ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ...
    (microsoft.public.data.oledb)
  • Re: World change
    ... I hear you and I too am concerned for I have been a Baha'i now for 41 years and when I declared I was very depressed to see all the problems in society at that time and I recognized Baha'u'llah gave us the answer and here we are today where I am still seeing and hearing the "same ole, same ole", so to speak and yet I also see there has been some progress. ... We do have less wars than any time in history but the deaths, injury etc. from our current wars are far more because new weapons of destruction. ...
    (soc.religion.bahai)
  • Re: VB Oracle Error : 80004005 SQL Data type out of range <2>
    ... I don't know Oracle, so there is not much I can do. ... or the Oracle ODBC driver. ... One idea is to change from using the default provider - OLE DB over ODBC - ... which is the Oracle OLE DB provider. ...
    (microsoft.public.data.oledb)
  • Re: How to get IBMDA400 provider
    ... i found that it can be done using IBMDA400 OLE DB Provider. ... if you install client access the odbc driver is included within the ...
    (microsoft.public.vb.general.discussion)