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

Posted on 2012-09-14
Medium Priority
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 (http://rdc.codeplex.com/) but it doesn't seem to work with VS2010.

any ideas what I may be missing?

SSIS package
Question by:darrennelson

Assisted Solution

by:Vijaya Reddy Pinnapa Reddy
Vijaya Reddy Pinnapa Reddy earned 668 total points
ID: 38400866
LVL 12

Assisted Solution

Jared_S earned 664 total points
ID: 38401029
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

DcpKing earned 668 total points
ID: 38402652
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

darrennelson earned 0 total points
ID: 38406299
@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

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

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
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 setup several different housekeeping processes for a SQL Server.
Suggested Courses

840 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