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

Posted on 2012-09-14
Last Modified: 2012-09-22
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 ( but it doesn't seem to work with VS2010.

any ideas what I may be missing?

SSIS package
Question by:darrennelson
    LVL 9

    Assisted Solution

    LVL 12

    Assisted Solution

    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.
    LVL 16

    Assisted Solution

    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.



    Accepted Solution

    @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?

    Author Closing Comment

    I chose my answer as the best solution because it provided the most direct solution.  Thanks to all who contributed.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Introduced in Microsoft SQL Server 2005, the Copy Database Wizard ( is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
    Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now