Error-One of the most common error in SSIS is while
exporting data from SQL to Excel destination
as below.
TITLE: Package Validation Error
------------------------------
Package Validation Error
------------------------------
ADDITIONAL INFORMATION:
Error at Data Flow Task [Excel Destination [41]]: Column
"Name" cannot convert between unicode and non-unicode string data
types.
Error at Data Flow Task [SSIS.Pipeline]: "Excel
Destination" failed validation and returned validation status
"VS_ISBROKEN".
Error at Data Flow Task [SSIS.Pipeline]: One or more
component failed validation.
Error at Data Flow Task: There were errors during task
validation.
(Microsoft.DataTransformationServices.VsIntegration)
Solution-To solve this issue we need to use a Data Conversion transformation in between oledb source and Excel destination and
change the data type Unicode string [DT_WSTR].
Below is the complete Step by Step Solution
First we will create a table in the SQL and will insert some
records into it to produce the above error
create table test_Unicode
(Id int identity(1,1),
Name varchar(50)
)
Insert into test_Unicode values ('Rakesh'),('Mukesh'),('Nitin')
Select * from test_Unicode
Now we will create a SSIS package that will have a Oledb Source
which is connected to our test_Unicode table now we will take a excel
destination and try to load data into excel destination.
You will see a cross mark in excel destination with a error
showing as below.
Column "Name" cannot convert between unicode and
non-unicode string data types.
Below are the steps that need to be followed to remove the above highlighted error.
1. Add a data conversion
transformation between Source and destination.
2. Connect source to data conversion transformation.
3. Open data conversion add two
column and change the data type to Unicode
string [DT_WSTR] as in below screenshot
4.Click ok
5. Now connect data conversion to excel destination
6. Map the new column which we have converted to Unicode string [DT_WSTR] to excel destination columns as below screenshot
7. Click ok
8. Execute package
9. It will run successfully
