Cannot convert between unicode and non-unicode string data types in SSIS

2
votes

I have set up a SSIS solution where I use SQL Server to import the 'raw data' and convert these into suitable data types and save these into a table. For the column I have problem with I have done like this (note I only want the date, not the time value which is why I use 112 as an parameter):

SELECT CONVERT(nvarchar(15),a.DeliveryDate,112) AS DeliveryDate
INTO MyNewTable
FROM MyRawDataTable

I then use MyNewTable as an input to my SSIS in Visual Basic. In Data Flow I right click my Source OLE DB and ensure that the datatype is in fact DT_WSTR of length 15 under Advanced Options and Input and Output datatypes.

When I try to run my SSIS I get the error

Cannot convert between unicode and non-unicode string data types

Which I figured may have to do with the fact that a.DeliveryDate sometimes contain NULL values. I therefor adjust my code in the SQL server into: 

SELECT 
CONVERT(nvarchar(15),COALESCE(a.DeliveryDate,'1900-01-01 00:00:00.000'),112) AS DeliveryDate
INTO MyNewTable
FROM MyRawDataTable

and subsequently run my SSIS. I notice that I still have DT_WSTR of length 15 in my Output from the OLE DB import in SSIS. This will also cause the same error though.

How can I get my program to work? What I have tried is to convert the datatype to varchar in my SQL table, to use COALESCE(...,0) instead of using the year 1900 as a dummy value.

Please note the solution cannot contain Data conversion transformation tasks in the Data Flow of SSIS.

EDIT: I have done the same conversions for another date, which I got from the same raw data file. For this date, this works without any hinch. My conclusion from this is that, there might be a problem when DeliveryDate contain NULL values (I do not need to use COALESCE for the date that works, as it Always contain a valid datetype).

EDIT 2: I updated my SQL table MyNewTable to not include any rows where a.DeliveryDate had the value NULL. This however caused the same issue.

UPDATE I noticed that the appearence I want to have after my conversion yyyymmdd for the month is in fact ISO. I therefore added a CAST after my CONVERSE in order to get a legit input to SSIS. Like this:

CAST(Convert(nvarchar(15),a.DeliveryDate,112) AS nvarchar(15)) AS DeliveryDate

This causes my import from the OLE DB source in SSIS Date Flow to not cause error. However error still occurs at my export to my Datewarehouse complaining about datatypes. Even though it still says that the datatype is DT_WSTR in every step in my data flow.

Also, what is weird, is that when I import from OLE DB using a table it causes error. Using the same query I create my table with as an import (using the query as an input that is) causes no issues.

3
sql-serverunicodessis
So, what is the structure of your destination table? And did you change the output to DT_WSTR after you initially created your dataflow? e.g. Did your query first return varchar and later on nvarchar? SSIS sometimes behaves bitchy if the source datatypes are changed later on... - Tyron78
My destination table have DT_WSTR as well. At the original database it was returned as a nvarchar but with a very large range, so when I convert some data types in my own SQL Table which I later import I simply resize the nvarchar to nvarchar(15) instead of its original nvarchar(100) or so. At my OLE DB import in Data Flow I have edited the affected column to have DT_WSTR. I later commit a LOOKUP function which does not complain when I map the Input column to the output column in regards to my Date column. - Cenderze
Just something else I noticed: you should change your convert to nvarchar(10) - otherwise you will receive some funny formed dates... e.g. "1900-01-01 00:0" - Tyron78
And what you might want to try: remove the OLE DB Source and recreate it from scratch with your conversion in it - this should result in the columns and all metadata to use WSTR from the beginning on... - Tyron78
@Tyron78 Thanks! Removing the OLE DB source and recreate it from scratch solved this issue. I've been switching between datatypes for some hours trying to figure it out, so it ought to contain multiple weird values. Feel free to post your suggestion as an answer so I can accept it! :) - Cenderze

3 Answers

2
votes

You might want to try: remove the OLE DB Source and recreate it from scratch with your conversion in it - this should result in the columns and all metadata to use WSTR from the beginning on...

0
votes

Non-Unicode string data types:
Use STR for text file and VARCHAR for SQL Server columns.

Unicode string data types:
Use W_STR for text file and NVARCHAR for SQL Server columns.

The problem is that your data types do not match, so there could be a loss of data during the conversion.

0
votes

I had a similar problem and changed my source to DT_WSTR. Although, I got an error on the source table and it asked me if I wanted to convert back to the source format. When I did, both my errors for the source and the destination table went away!