Greetings SSIS gurus



Using Sql Server 2005 on a Win2003 (sp1) Ent server.



Starting on an SSIS project to move data directly from Informix tables to
Sql Server 2005 via an ODBC connector.



Successfully created an SSIS .dtsx to move the contents of a small from
Informix look-up table directly into the Sql Server 2005 version of the
table. For the most part ... works great ...



THE PROBLEM:



When moving Informix char/varchar columns to the matching Sql Server 2005
char/varchar problems each column causes an error in the "Sql Server
Destination" data flow destination object.



Each char/varchar column coming out of the Informix table causes the "Sql
Server Destination" data flow destination object to throw the following
error ...



== > "Column 'xxx' cannot covert between unicode and non-unicode string
data type." <==



After some web research the prevailing "fix advice" for this error seems to
be "Set up a Derived Column object" to cast the Infomix Unicode char columns
to Sql Server string types. The advice works. I created a 'cast' for each
of the objects char/varchar columns coming out of the Informix table and
dutifully reconnected everything in the "Sql Server Destination" data flow
destination object. Worked fine. SSIS is AWSOME in its power ... a HUGE
improvement over exporting and importing delimited files.



However, please Excuse Me, but when I'm looking at migrating a couple
THOUSAND char/varchar columns from Informix to Sql Server this "just create
a derived column transform object and cast each informix unicode column to
str" approch is simply LAME !!!!



There's got to be an easier way !!!



There MUST be some switch on the ODBC connector, the DataReader Source or
the Sql Server Destination objects that says "whenever you see an Informix
char/varchar column" treat it as an Sql String type. I can't believe MS
would allow such a powerful tool to suffer such a silly little Achilles'
heal !!



The "Data Source Reader" component that I'm using to connect to Informix
lists 2 sets of columns "External" and "Output" and simply refuses to allow
the "Output" columns to be changed from Unicode to Str type.



This smells like an "Elephant in the Room" bug to me ... This kind of stuff
makes me seriously consider going back to delimited export and import text
files.



THE QUESTION: Is there a less onerous alternative to the LAME "Set up a
Derived Column object to cast the Informix Unicode char columns to Sql
Server string" "fix"



Ye gods folks. This kind of stuff just makes me (obviously) RAVING CRAZY
!!!



Thanks in advance for any help / sage advice



Barry

in Oregon

Hello Barry,

Thank you for your feedback on this issue.

As you know, the Unicode errors are usually thrown because conversion from
DT_NTEXT/DT_WSTR to DT_STR is not supported here. I think this is becasue
varchar in Informix supports UTF-8.

Currently we have to use "data conversion task" and/or "derived column
task" to work around the issue as you mentioned. I agree with you it is not
convenient to add these tasks for many columns and your suggestion to allow
changes to "Output" columns is a great idea for a future product
enhancement. Please rest assured this has been routed to the proper
channel.In the meantime, I also encourage you submit via the link below

http://lab.msdn.microsoft.com/produc...k/default.aspx

Also, you may want to change the destination column type to nvarchar to see
if you could work around this issue.

More related information on unicode support in different databases

UNICODE SUPPORT : Unicode Support for Oracle 8&9, Informix 9.x, Sybase 12.5
+, Progress 9.1, DB/2 v 7.x, MS SQL Server 2000
http://docs.openlinksw.com/st/unicodechapter.html

International Unicode Conference
http://www.microsoft.com/globaldev/r...d_Unicode_Conf
..mspx

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

================================================== ===



This posting is provided "AS IS" with no warranties, and confers no rights.

Peter,

Sorry for the late reply to this thread.

As always, your prompt response is very much appreciated.

I'm sorry to hear that there is no workaround for the DT_NTEXT/DT_WSTR to
DT_STR conversion issue yet. As I said its going to cause pain. Still, in
final analysis, exporting the Informix tables to delimited text files and
then using the FF import to Sql Tables route is still more work since I end
up having to name and type each and every input Flat File input column to
allow mapping to Sql Table columns. Arrrgggg.

I just find it sooooooo frustrating to be at the wheel of a Ferrari with a
questionable 3rd gear.

Thanks for your time.

Barry
in Oregon


""privatenews"" <petery (AT) online (DOT) microsoft.com> wrote

Hello Barry,

This is Yanhong, manager of MSDN Newsgroup Support Team. Peter Yang raised
the issue to me and we discussed it together.

As we all see, this is a known issue in SQL 2005. You and Peter have all
found the workaround. However, the workaround is not convenient for you
since there are too many columns impacted. We have forwarded it to our
product group as an important community feedback. The product group will
definitely look into it.

Another approach is to contact our customer support service and raise the
business impact of the issue. The support team engineer will help check
whether it is possible to submit a change request to product group on it.
If you feel there is any we can do to help you, please feel free to let us
know.

Thanks again for your feedback. There is currently no way to do this, but
keep an eye out for it in the future. We are looking at continual
improvement, and it's this kind of feedback that let us know what things
you're trying to do, that we haven't yet exposed for you.

Keep the information coming.

Sincerely,
Yanhong Huang
Microsoft Online Community Support

==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================

This posting is provided "AS IS" with no warranties, and confers no rights.

Mr. Yan-Hong Huang

Thanks for your response and attention. Its very much appreciated.

We are finding SSIS tremendously useful and we certainly appreciate
Microsoft's interest in continuing to improve SSIS.

Barry
in Oregon

"Yan-Hong Huang[MSFT]" <yhhuang (AT) online (DOT) microsoft.com> wrote