column cannot convert between unicode and non-unicode...again

Windows 7 64bit
SQL Server 2012
VS 2010

I've built a package to read a flat file into a table.  The components are:

flat file source->data conversion->ole db destination

-the incoming data types are all DT_STR50
-the db data types are varchar30, int, and nvarchar50

the data conversion makes a 'copy of' that I convert as follows:
DT_STR - DT_WSTR 30 for varchar
DT_STR - DT_WSTR 50 for nvarchar
DT_STR - DT_I4 for int

the varchar and int are ok, but I get a error message for the nvarchar on the ole component stating "Column 'XXX' cannot convert between unicode and non-unicode string data..."

I also tried to create a package through the wizard, which attempted the same conversion, but it also failed.

I found a component someone wrote called Replace Data Conversion (http://rdc.codeplex.com/) but it doesn't seem to work with VS2010.

any ideas what I may be missing?

SSIS package
darrennelsonAsked:
Who is Participating?
 
darrennelsonConnect With a Mentor Author Commented:
@jared the core tables do need to be nvarchar for non English character set support.
@dcp we're trying to avoid this.  we have customers currently doing this and it's becoming a support nightmare

Interestingly, I removed the data conversion component, did the conversion at the flat file source level and it works.  Now I'm confused as to why there is a data conversion component if you can convert at the input level.  Is it because not all input level components don't allow you to transform data types?
0
 
Jared_SConnect With a Mentor Commented:
Try either leaving your nvarchar as DT-STR -
or (if possible) evaluate whether or not that column REALLY needs to be an nvarchar.
You may be able to save space and solve a problem by changing it to a varchar.
0
 
DcpKingConnect With a Mentor Commented:
Pull the data in as-is and do the conversions in a stored procedure you call from the SSIS after the data is all there.

hth

Mike
0
 
darrennelsonAuthor Commented:
I chose my answer as the best solution because it provided the most direct solution.  Thanks to all who contributed.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.