December 14, 2010 at 5:43 pm
We have an ETL application which loads data from lots of delimited text files, from several different sources, using SSIS 2008. There are various formats, leading to several data flows each handling a set of files of a particular layout. The files are loaded in a FOREACH loop, so there's a variable with the file name in it.
I now have a problem in that some files are 8-bit ANSI and some 16-bit Unicode (UTF-16LE) - and they can be in either format, depending where they came from. I can look that up and set a User::Unicode variable accordingly.
I can load data in either ANSI (Code page 1252) or Unicode fine if the connection was created for one or the other - but I need to switch between them at run time based on the User::Unicode flag. So I set an expression in the Flat File Connection properties, Unicode = @[User::Unicode], and also set the connection's DelayValidation to TRUE. But when I run it, it fails if the Unicode flag is not the same as when the connection was created.
The error message says that the Flat File Source Error Output Column data type must be changed from DT_TEXT to DT_NTEXT (or vice versa), but it won't let you change it! Nor will it let you remove that column - I don't need it as I've set the task to fail on error (any error is caught by an event handler).
So an output I don't need is stopping me setting the Unicode value, because it's not switching type accordingly, but IS complaining that it should have done!
Does anyone have a solution to this? Why would Microsoft let you set the Unicode flag in an expression if doing so always fails?
Viewing post 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply