SSIS Convert Between Unicode and Non-Unicode Error

Question

I have an ssis package where I am using an OLEDB source linking to SQL Server 2005 table  All columns except a date column are NVARCHAR 255   I am using an Excel destination and using a SQL statement to create the sheet in the Excel workbook  the SQL is in the excel connection manager  effectively a create table statement that creates a sheet  and is derived from the mapping of the columns from the DB   No matter what I have done I keep getting this unicode --  non-unicode conversion error between my source and destination  Tried conversion to string DT STR  between S   D  removed it  changed SQL Table VARCHAR to NVARCHAR and still get this flippin error    Because I am creating the sheet in Excel with a SQL statement I do not see any way to actually pre-define what the data types of the columns will be in the Excel sheet  I imagine it would be a default meta data but I do not know   So between my SQL table destination and the creation of my Excel sheet with this SSIS sql statement how can I stop this error coming up   My error is      Error at Data Flow Task  OLE DB Source  1    Column  MyColumn  cannot convert between unicode and non-unicode string data types    And for all nvarchar columns   Appreciate any help  Thanks  Andrew

User · Answer

I experienced this condition when I had installed Oracle version 12 client 32 bit client connected to an Oracle 12 Server running on windows  Although both of Oracle-source and SqlServer-destination are NOT Unicode   I kept getting this message  as if the oracle columns were Unicode  I solved the problem inserting a data conversion box  and  selecting type DT-STR  not unicode  for varchar2 fields and DT-WSTR  unicode  for numeric fields  then I ve dropped the  COPY OF  from the output field name  Note that I kept getting the error because I had connected the source box arrow with the conversion box BEFORE setting the convertion types  So I had to switch source box and this cleaned all the errors in the destination box

User · Answer

I have been having the same issue and tried everything written here but it was still giving me the same error  Turned out to be NULL value in the column which I was trying to convert   Removing the NULL value solved my issue   Cheers  Ahmed

User · Answer

Add Data Conversion transformations to convert string columns from non-Unicode  DT STR  to Unicode  DT WSTR  strings   You need to do this for all the string columns

User · Answer

The missing piece here is Data Conversion object  It should be in between OLE DB Source and Destination object

User · Answer

On the above example I kept losing the values  I think that delaying the Validation will allow the new data types to be saved as part of the meta data   On the connection Manager for  Excel Connection Manager  set the Delay Validation to False from the Properties   Then on the data flow Destination task for Excel set the ValidationExternalMetaData to False  again from the properties   This will now allow you to right click on the Excel Destination Task and go to Advanced Editor for Excel Destination --  far right tab - Input and Output Properties  In the External Columns folder section you will be able to now change the Data Types and Length values of the problematic columns and this can now be saved   Good Luck

User · Answer

Instead of adding an earlier suggested Data Conversion you can cast the nvarchar column to a varchar column  This prevents you from having an unnecessary step and has a higher performance then the alternative   In the select of your SQL statement replace date with CAST date AS varchar  size     For some reason this does not yet change the output data type  To do this do the following    Right click your OLE DB Source step and open the advanced editor   Go to Input and Output Properties Select Output Columns Select your column Under Data Type Properties change DataType to string  DT STR  Change Length to the length you specified in your CAST statement   After doing this your source data will be output as a varchar and your error will disappear   Source

User · Answer

First  add a data conversion block into your data flow diagram  Open the data conversion block and tick the column for which the error is showing  Below change its data type to unicode string DT WSTR  or whatever datatype is expected and save  Go to the destination block  Go to mapping in it and map the newly created element to its corresponding address and save  Right click your project in the solution explorer select properties  Select configuration properties and select debugging in it  In this  set the Run64BitRunTime option to false  as excel does not handle the 64 bit application very well

User · Answer

Below Steps worked for me   1   right click on source task   2   click on  Show Advanced editor   advanced edit option for source task in ssis  3   Go to  Input and Output Properties  tab   4   select the output column for which you are getting the error    5   Its data type will be  String DT STR     6   Change that data type to  Unicode String DT WSTR    Changing the data type to unicode string  7   save and close  Hope this helps

User · Answer

When creating table in SQL Server make your table columns NVARCHAR instead of VARCHAR

[unicode] SSIS Convert Between Unicode and Non-Unicode Error

Examples related to unicode

Examples related to ssis

Examples related to non-unicode