I was trying to pull data from the excel sheet in to the Sql server by Using SSIS. initially i tried Excel source and OLEDB destination. After configuring everything even oledb destination i was having a red mark on the oledb destination when i moved the cursoor over that it said "cannot convert between unicode and non -unicode string datatype. And then i tried DATA Conversion transform and when i tried to run the package it was not running but gave big Package validation error. Error was: Error at Data Flow Task [OLEDB destination 14]: Column " Copy of STATUS" and " STATUS" cannot convert between unicode and non-unicode string datatype. Same kind of error is for Eight columns. HElp me ASAP Thanks In advance Fighttillend_DBA/DEV

How did you set the data conversion up? The Excel by default gets opened as Unicode whereas OEDB is not unicode hence is your issue. In your Data Conversion Task convert the unicode to a non-unicode data type equivalent. Example: if a column comes from Excel as say DT_WSTR (which is unicode) use DT_SRT to go make non - unicode and it will satisfy the OLEDBArthur My Blog

Right click on the EXCEL Source find the right field that is causing the problem (assuming its STATUS only) and change the DATA TYPE to the same data type as the destination fields to check if you have it right open the destination object open the mapping page , move the mouse on the field and the TOOLTIP will show you the DATA TYPE of it , then move the mouse to the same filed but on the other side of the grid (same mapping field) and check the data type Sincerely SH -- MVP, MCITP 2008, MCTS 2008 & 2005 -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).

1) If there are unicode characters in the source excel columns, they can be converted to NVARCHAR or DT_WSTR() in the data conversion transformation. 2) The secoond method to convert non-unicode to unicode in the excel is to go to 'advanced editor' (right click the excel source). In the input output properties, the data type of the output column from the excel can be modified. 3) Point to remember is , if the excel sheet has unicode characters and the destination table has VARCHAR datatype, even the conversion if takes place in the SSIS, teh data cannot be loaded into the table. The SQL database rejects it. The data type of the table has to be modified. Any deviation?Please mark my post as an answer if I helped you to resolve the issue or vote up if I helped you.Thanks. Regards and good Wishes, Deepak.

This article normally helps resolving all the issues pertaining to loading an Excel file using OLEDB Importing Excel data with SQL Server Integration Services SSIS with unicode and non-unicode data issues http://www.mssqltips.com/tip.asp?tip=1393Arthur My Blog

Arthurs i applied the very first way you said but i am getting a warning while running the package. warning is: [OLEDB Destination [14]] Warning: Truncation may occur due to inserting data flow column "Copy of STATUS" with a length of 50 to dataabse column "STATUS" WITH A LENGTH OF 1. I am gettin same warning for six more columns. Thanks to all for responding i will try all the things But the issue is still there update you soonFighttillend_DBA/DEV

It rightfully gives you the warning, the source and destination are very different. Adjust to what makes sense in the source (I hope you do not try to load data from source which with exceeds the destination boundaries).Arthur My Blog

I am gettin same warning for six more columns. remove all of the columns and add them one by one while checking the data type question are you using IMEX = 1 in your connection string? Does every cell in the excel file have a data value? or are some of them NULL or empty string ????Sincerely SH -- MVP, MCITP 2008, MCTS 2008 & 2005 -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).

You know guys here i am very much amazed that the column STATUS have only one letter in the excel sheet. but the warning as i mentioned above says the following [OLEDB Destination [14]] Warning: Truncation may occur due to inserting data flow column "Copy of STATUS" with a length of 50 to dataabse column "STATUS" WITH A LENGTH OF 1. Similar is the case for the other columns. AND My second question is to NIK-Shahriar You mentioned about changing the datatype at the source i mean in the excel sheet so i am not clear how should i do this?? Also I am Blank or confused about using IMEX = 1 in your connection string? I do not know what this mean ? ThanksFighttillend_DBA/DEV

My second question is to NIK-Shahriar You mentioned about changing the datatype at the source i mean in the excel sheet so i am not clear how should i do this?? Also I am Blank or confused about using IMEX = 1 in your connection string? I do not know what this mean ? Q: You mentioned about changing the datatype at the source i mean in the excel sheet so i am not clear how should i do this?? A: no open the package , open the DFT , Right click on the Excel source OLE obkject in SSIS , select ADVANCE OPTION, click on a field, change the data type from the right hand side Story short , assuming that your PACKAGE is working fine , to test it remove the DATA from the first 8 rows ( not the headers) , technically your package should work , it basically sees empty or NULL records, well sorry it wont work, because you dont have the IMEX = 1 in your connection string , IMEX= will force Excel connection to read the Excel file as TEXT check the 3 Part http://dougbert.com/blogs/dougbert/archive/2008/06/21/excel-in-integration-services-part-3-of-3-issues-and-alternatives.aspxSincerely SH -- MVP, MCITP 2008, MCTS 2008 & 2005 -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).

a better lead is GO to the DFT -> right click on the Source excel object -> select Show advance option -> go to tab named : Input and output properties -> on the left tree go to Excel source output --> Output Cloumn --> select the field that you want to change the setting --> go to property DATA TYPE : Sincerely SH -- MVP, MCITP 2008, MCTS 2008 & 2005 -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).

check the 5th or maybe the 6 picture in http://pragmaticworks.com/forum/yaf_postst48_SSIS-2008-Using-Excel-in-a-Lookup-Tansform.aspxSincerely SH -- MVP, MCITP 2008, MCTS 2008 & 2005 -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).

Hi Nik Sorry for so delayed response was stuck in some other priority issue. So i tried things as you said but one thing which i was searching was IMEX property in the connection manager but unable to find that. Am i missing something the thing is i am using dataconversion task and i tried to implement several changes in dataconversion transformation like changing the length there according to the destination but that wass leading the error message that truncation at the dataconversion task as source was of more size. Then i tried to change advanced properties at the excel source but that is not fixing issue. So now i think its something to do with the IMEX that i am not able to locate in the properties? Thanks Fighttillend_DBA/DEV

It rightfully gives you the warning, the source and destination are very different. Adjust to what makes sense in the source (I hope you do not try to load data from source which with exceeds the destination boundaries). ARTHURZ If you said that then please how should i import the data as i need it and excel is the only source for me. ThanksFighttillend_DBA/DEV