DataType issues importing legacy tables via ODBC using SSIS DataReader 1

[GordonLeeds]

Advice from SSIS gurus urgently desired!

I'm migrating an existing SQL 2000 Data Mart and its DTS packages to SQL 2005.

I'm extracting data from the Qantel manufacturing system, via the Server version of their proprietary ODBC driver, into local SQL 2005 tables.

In Windows 2003 I have a System DSN using the ODBC Driver. In Visual Studio 2005 SSIS, I have a Data Source configured to reference the DSN, with the pwd embedded. Using Server Explorer to expand the connection's schema, I can see that every table, each field, and each field's Data Type is exactly what I expect and what DTS used to happily deliver to SQL 2000 every night.

When we started manually testing the migration process a few days ago I discovered a big hitch while re-building my old DTS data extracts as Data Flows using SSIS. On the one hand, the SSIS Server Explorer correctly sees that a particular field has a Data Type of "OdbcType.VarChar(8)". On the other hand, when I begin a new SSIS Package using the DataReader Source with ODBC as the Connection Manager (which I gather is the ONLY way to employ ODBC now since there's no direct Data Flow Source for it), DataReader sees all of the Source table's VarChar fields as being of type "Unicode text stream [DT_NTEXT]". This is very annoying to me.

I've tried to jump through every hoop I can find, but the only solution seems to be to send the Data Source's Output fields (it lets you tell it that the INput fields are of a different type, but gives an error when you try to control the OUTput from the Data Source object!) to a Data Conversion Transformation object where I can change it from DT_NTEXT to DT_WSTR, whose output in turn maps to NVARCHAR in the local Destination table. It will NOT convert or map to a VARCHAR field, even though the original data is simple text and NOT unicode. Again, at the Server Explorer level the app knows perfectly well that it's a VarChar field, but the left hand isn't talking to the right.

Questions:
1) Why on earth did MSFT bury ODBC so deeply in nested wrappers in SQL 2005, when it's the only tool folks like me HAVE to talk to old legacy systems like Qantel? This one's rhetorical - never mind trying to answer it...
2) Why does the Server Explorer understand my Data Types perfectly, while SSIS mis-converts all varchar fields to what are basically unicode MEMO fields?!?!?
3) Is there a smart way around this boondoggle, or am I forced to do it the hard & tedious way?
4) Would anyone else be interested in a DataType conversion utility that re-CREATE's the structure of selected local SQL tables, converting (for example) all varchar fields to nvarchar? If so, I'll post the work my colleague Ben is doing once it's complete and we've tested it. It will save us hours and hours of manually poking at table structures.

I realize that I can retain the original DTS packages and simply execute them from within SSIS, but I don't want to leave an embedded legacy workaround at the core of my database if I can possibly help it. It just shouldn't be this DIFFICULT to pull old data into a piece of Middleware using ODBC in SQL Server 2005. And no, the source is not OLE DB compliant.

I know we're all still getting used to SSIS, and it has many WONDERFUL and innovative improvements, but there are definitely some dark corners here and there. I will bow low and repeatedly to anyone who can help illuminate this one.

Regards,
Gordon Leeds
Lang Manufacturing

PS: thread1555-1270643 talks about related issues, but doesn't quite point me in a new direction.

 

[Catadmin]

When Microsoft built SSIS, they ignored all the old DTS stuff and started from scratch. It is entirely possible they either forgot to stick something back in OR that they deliberately left it out.

If I were you, I'd go to Microsoft's newsgroups, find the SSIS bug forum and post your issue there. See what kind of response you get. I'd be interested in hearing whether or not they care to fix problems like this.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[GordonLeeds]

Thanks, Catadmin!

It's good to know there's a dedicated MSFT SSIS newsgroup - I'll use that for some other issues that have surfaced recently.

As it happens (and I'm sorry I didn't re-post more promptly) I copied my posting to Qantel and it turned out they were JUST about to release a SQL2005-compliant version of their server ODBC driver, and were able to send me an update that made the data type mis-translations go away.

So the lesson here would be: talk to the driver's maker before you spend a lot of time writing up & posting weird version-related snafus!!

I wish I could say all was well now, but I'm currently having serious trouble importing .csv files where the provider puts double-quote delimiters around ALL data, numeric and date as well as string. After spending all day yesterday wrestling with both DTS and SSIS in the Valley of Futility, and losing, I'm going to have to try importing into a staging table that ONLY has string fields, and then write an append query with lots of CAST()s in it to move the data to its final resting place.

I didn't used to have to jump through such hoops, but ever since I installed SQL2005 alongside SQL2000 in order to begin preparing and testing for db conversion/upsizing, I've been having obscure ODBC errors. See KB884038 for your amusement. What a cul-de-sac article. I'm going to break down and call MS, ask them to cough up the hotfix that ought to be attached to the KB article in the first place, and tell them don't you DARE try to charge me $100 for the privilege!!

Anyway, thanks again for the bug forum tip - I'll give that a shot and see what happens.

Regards,
Gordon