ODBC via SSIS - Is DTS really better?

Question

ODBC via SSIS - Is DTS really better?

ohack

SSCrazy

Points: 2043
More actions
December 29, 2009 at 7:15 am

#214998

Hi,
I'm currently struggling with extracting data from a proprietary database using SSIS via the ODBC driver provided by the vendor. I've used the ADO .NET connection manager, chosen the .Net Providers\Odbc Data Provider and pointed it at my existing DSN (which I know works fine in Access etc). Inside my Data Flow Task I've used a DataReader Source and chosen the connection in the hard to spot drop-down for Connection Manager. I know that I can type a query in the Sqlcommand setting on the Component Properties tab and this successfully populates the output columns on the last two tabs. BUT....
Firstly I can't view a list of the available tables\views like you previously could in DTS (I should mention we have existing packages we'd like to upgrade). But also, if I now link the DataReader Source to an OLE DB Destination, although this handily can generate a CREATE TABLE script to make a matching table, if I now run this simple package it simplly sits there and does nothing.
I've found these two topics which seem to confirm that SSIS can't handle this situation when DTS could:
http://www.sqlservercentral.com/Forums/Topic485558-148-1.aspx
http://www.sqlservercentral.com/Forums/Topic665964-20-1.aspx#bm814588
Now hoping somebody out there can offer some suggestions on how best to do this. Incidentally I've also tried the Linked Server route but using OPENQUERY I run into errors like: "Error converting data type DBTYPE_DBTIMESTAMP to datetime" and "An unexpected NULL value was returned for column "[MSDASQL].created" from OLE DB provider "MSDASQL" for linked server "Linkedserver". This column cannot be NULL." (there is a column called "created").
Any help greatly appreciated.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

alen teplitsky SSC-Dedicated Points: 30014 More actions · Answer 1

what is the proprietary database? i didn't think anymore existed. almost all vendors seem to use mysql, postgres or sybase

ohack SSCrazy Points: 2043 More actions · Answer 2

It's something called ePEX by these people: http://www.ascribe.com. Of course, it's old and on the way out. The ODBC driver is called Protechnic PHC. I think the issues are fairly generic though as Access has no problem linking to the tables.

ohack SSCrazy Points: 2043 More actions · Answer 3

Hmmm, guess everyone's on holiday.

I realised that I can view the tables, views and can query them using Server Explorer in VS. However I still have problems actually extracting the data. Varchar columns seem to cause all sorts of problems - VS crashes, or the DataReader Source turns yellow and just sits there. Anyway, since my questions have moved on I'll be starting a new topic in the SQL Server 2005 > Integration Services forum.

nightowl23 Mr or Mrs. 500 Points: 522 More actions · Answer 4

hi,

hope you can get the message. Have you heard epex? i got the same problem as yours. having a problem with data extraction and this only applies to varchar or nvarchar. Have you got the problem solved?

thanks