SSIS is pathetic with ODBC connections ...

We have spent days trying to perform a proof of concept and I am
dissappointed with SSIS to say the least. We are trying to connect and
fetch data from a Double Byte Progress database and the "DataReader
Source" using a ADO .NET ODBC provider does not work! I understand
that SSIS is a totally rewritten version of DTS. What annoys me is
that this functionality used to work in DTS and does not in the much
talked about SSIS!! Will someone in Microsoft start listening to
customers instead of gloating on useless features. SSIS is a total
waste if it cannot connect and fetch data from a wide variety of
source databases!!

Re: SSIS is pathetic with ODBC connections ...

jags_32@yahoo.com wrote:
> We have spent days trying to perform a proof of concept and I am
> dissappointed with SSIS to say the least. We are trying to connect and
> fetch data from a Double Byte Progress database and the "DataReader
> Source" using a ADO .NET ODBC provider does not work! I understand
> that SSIS is a totally rewritten version of DTS. What annoys me is
> that this functionality used to work in DTS and does not in the much
> talked about SSIS!! Will someone in Microsoft start listening to
> customers instead of gloating on useless features. SSIS is a total
> waste if it cannot connect and fetch data from a wide variety of
> source databases!!

In my experience, ODBC is a bad choice. It is slower than dirt and
a very old technology. I use SSIS w/ an OLE DB driver (both DB2 and
SQL Server) and have been very impressed with its functionality.

Can you use an OLE DB driver?

aj

Re: SSIS is pathetic with ODBC connections ...

On Nov 30, 11:19 am, aj wrote:
> jags...@yahoo.com wrote:
> > We have spent days trying to perform a proof of concept and I am
> > dissappointed with SSIS to say the least. We are trying to connect and
> > fetch data from a Double Byte Progress database and the "DataReader
> > Source" using a ADO .NET ODBC provider does not work! I understand
> > that SSIS is a totally rewritten version of DTS. What annoys me is
> > that this functionality used to work in DTS and does not in the much
> > talked about SSIS!! Will someone in Microsoft start listening to
> > customers instead of gloating on useless features. SSIS is a total
> > waste if it cannot connect and fetch data from a wide variety of
> > source databases!!
>
> In my experience, ODBC is a bad choice. It is slower than dirt and
> a very old technology. I use SSIS w/ an OLE DB driver (both DB2 and
> SQL Server) and have been very impressed with its functionality.
>
> Can you use an OLE DB driver?
>
> aj

I totally agree with you that ODBC is old technology, however, we have
no choice since that is the only way we can connect to the SOurce
Database.

Re: SSIS is pathetic with ODBC connections ...

(jags_32@yahoo.com) writes:
> We have spent days trying to perform a proof of concept and I am
> dissappointed with SSIS to say the least. We are trying to connect and
> fetch data from a Double Byte Progress database and the "DataReader
> Source" using a ADO .NET ODBC provider does not work! I understand
> that SSIS is a totally rewritten version of DTS. What annoys me is
> that this functionality used to work in DTS and does not in the much
> talked about SSIS!! Will someone in Microsoft start listening to
> customers instead of gloating on useless features. SSIS is a total
> waste if it cannot connect and fetch data from a wide variety of
> source databases!!

I have played with OdbcClient against SQL Server, and it was fairly
easy to do things that annoyed the ODBC SQL Server Driver.

As "aj" suggested, you could try OLE DB instead. There may not be an
OLE DB provider for your data source, but you should still be able
to use MSDASQL, OLE DB over ODBC.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

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

Re: SSIS is pathetic with ODBC connections ...

On Nov 30, 5:18 pm, Erland Sommarskog wrote:
> (jags...@yahoo.com) writes:
> > We have spent days trying to perform a proof of concept and I am
> > dissappointed with SSIS to say the least. We are trying to connect and
> > fetch data from a Double Byte Progress database and the "DataReader
> > Source" using a ADO .NET ODBC provider does not work! I understand
> > that SSIS is a totally rewritten version of DTS. What annoys me is
> > that this functionality used to work in DTS and does not in the much
> > talked about SSIS!! Will someone in Microsoft start listening to
> > customers instead of gloating on useless features. SSIS is a total
> > waste if it cannot connect and fetch data from a wide variety of
> > source databases!!
>
> I have played with OdbcClient against SQL Server, and it was fairly
> easy to do things that annoyed the ODBC SQL Server Driver.
>
> As "aj" suggested, you could try OLE DB instead. There may not be an
> OLE DB provider for your data source, but you should still be able
> to use MSDASQL, OLE DB over ODBC.
>
> --
> Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
>
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/down loads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/boo ks.mspx

Could you clarify on "MSDASQL, OLE DB over ODBC"? What is MSDASQL, OLE
DB?

Thanks
Jagannathan Santhanam

Re: SSIS is pathetic with ODBC connections ...

(jags_32@yahoo.com) 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@sommarskog.se

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

Re: SSIS is pathetic with ODBC connections ...

On Dec 3, 5:39 pm, Erland Sommarskog wrote:
> (jags...@yahoo.com) 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...@sommarskog.se
>
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/down loads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/boo ks.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.