Friday, August 14, 2009

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:

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.