Excel destination - cannot convert between unicode and non-unicode

While using the Excel Destination we used to get following error:
Error1 Validation error. Data Flow Task: Excel Destination [1311]: Column "Order Date" cannot convert between Unicode and non-Unicode string data types.

OLE DB source query:
where order_date data type is datetime in DB. Here we are converting to varchar

DFT (OLE DB Source and Excel destination)

Table structure:

Answer:
I checked on MSDN for excel destination. Here is some content written about moving data for your reference: Data types. The Excel driver uses only six data types, which Integration Services maps as follows: · Numeric – double-precision float (DT_R8) · Currency – currency (DT_CY) · Boolean – Boolean (DT_BOOL) · Date/time – date (DT_DATE) · String – Unicode string, length 255 (DT_WSTR) · Memo – Unicode text stream (DT_NTEXT)

Integration Services does not implicitly convert data types. for resolving this problem please follow these steps.

Step1: Add Data Conversion task between Source and Excel destination

Step2: Edit the data conversion task and change the data type to [DT_WSTR], change the length if required.

Step3: After adding the data conversion, the error message will go. Execute your package.

It will create an Excel file and load the data.

How to include Primary ID column using SSIS package?

Question:
I have target table where columns are ID, Name & DateofBirth where ID is PK and should be increment of 1(eg. 1, 2, 3, 4... and so on) also NOTE that ID column is NOT AN IDENTITY column

Now I have flat file which has data with only Names & Dateofbirth.

http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/db36004b-30a8-4404-8f95-301cc4a628f1

Answer:
Hi, There are two ways.

First:: Download the "Row Number Transformation" and use it in your SSIS package.

The Row Number Transformation calculates a row number for each row, and adds this as a new output column to the data flow. The column number is a sequential number, based on a seed value. Each row receives the next number in the sequence, based on the defined increment value.

Second:: Add a "Script Component transformation" and write a script there for adding the row numbers. Please check the "Row Numbers in a DataFlow"for step-by-step explanation.

AboutThisBlog

The main motto of this blog is to share my knowledge and experience with you.