December 8, 2007 at 11:33 am
Hi,
I have posted this question before, and many of them were able to help me, however i still need to do this task using ssis package but i am getting the below mentioned error please help me understand this error as i am new to ssis. Pleeeeeeeeeeese
Package Validation Error (Package Validation Error)
===================================
Error at Data Flow Task [Excel Destination [1178]]: Column "calltable" cannot convert between unicode and non-unicode string data types.
Error at Data Flow Task [Excel Destination [1178]]: Column "Cell_Code" cannot convert between unicode and non-unicode string data types.
Error at Data Flow Task [Excel Destination [1178]]: Column "s3" cannot convert between unicode and non-unicode string data types.
Error at Data Flow Task [Excel Destination [1178]]: Column "s4" cannot convert between unicode and non-unicode string data types.
Error at Data Flow Task [Excel Destination [1178]]: Column "s5" cannot convert between unicode and non-unicode string data types.
Error at Data Flow Task [Excel Destination [1178]]: Column "s6" cannot convert between unicode and non-unicode string data types.
Error at Data Flow Task [Excel Destination [1178]]: Column "s7" cannot convert between unicode and non-unicode string data types.
Error at Data Flow Task [Excel Destination [1178]]: Column "state" cannot convert between unicode and non-unicode string data types.
Error at Data Flow Task [DTS.Pipeline]: "component "Excel Destination" (1178)" failed validation and returned validation status "VS_ISBROKEN".
Error at Data Flow Task [DTS.Pipeline]: One or more component failed validation.
Error at Data Flow Task: There were errors during task validation.
(Microsoft.DataTransformationServices.VsIntegration)
------------------------------
Program Location:
at Microsoft.DataTransformationServices.Project.DataTransformationsPackageDebugger.ValidateAndRunDebugger(Int32 flags, DataWarehouseProjectManager manager, IOutputWindow outputWindow, DataTransformationsProjectConfigurationOptions options)
at Microsoft.DataTransformationServices.Project.DataTransformationsProjectDebugger.LaunchDtsPackage(Int32 launchOptions, ProjectItem startupProjItem, DataTransformationsProjectConfigurationOptions options)
at Microsoft.DataTransformationServices.Project.DataTransformationsProjectDebugger.LaunchActivePackage(Int32 launchOptions)
at Microsoft.DataTransformationServices.Project.DataTransformationsProjectDebugger.LaunchDtsPackage(Int32 launchOptions, DataTransformationsProjectConfigurationOptions options)
at Microsoft.DataTransformationServices.Project.DataTransformationsProjectDebugger.Launch(Int32 launchOptions, DataTransformationsProjectConfigurationOptions options)
December 14, 2007 at 4:25 am
Probably there is a implicit conversion from unicode to non-unicode data types in your package , in the mentioned dataflow. I think Excel is not able to use unicode.
You should be able to get around this error by adding a DataConversion component in your dataflow.
Add this before the Excel destination, and convert the unicode strings (DT_WSTR) to non-unicode strings (DT_STR).
The converted strings will be available to the Excel destination in new columns, so you'll have to redo the mappings in your Excel destination to use the new, non-unicode columns.
Peter Rijs
BI Consultant, The Netherlands
December 14, 2007 at 4:34 am
Thanks Peter,
It is working, however i have also found another solution plz let me know if i am wrong. I have converted the data type of my db table from VARCHAR TO NVARCHAR AND CHAR to NCHAR and its working this way also. Plz let me know if this will cause any complications in the future use of the package.
Thanks again.:)
December 14, 2007 at 5:04 am
Ah, that passed my mind when writing, but it slipped away before I wrote it down.
Do you need to use unicode string for some reason, or will non-unicode do as well?
Unicode has a bigger characterset and is locale independent, but has a bigger storagesize.
I suggest you read BOL on this subject (Working with Unicode Data) to see what suits your puproses best.
Peter Rijs
BI Consultant, The Netherlands
June 18, 2008 at 7:49 pm
With a similar problem, rather than change the datatype to unicode in my database (not really an option), I added a convert function to the OLE data source: CONVERT(nchar, Problem_column) as Problem_Column
It certainly seems strange that SSIS makes one convert data to uni-code so it can be sent to Excel. The create-worksheet pop-up did its best, trying to define the output as NVarChar, but that was wrong. Worse than wrong, IMO, as it made me think that the incoming data was already unicode.
Of course, it also tried to make the result of a RANK() function BIGINT into Excel as well. I had to change that definition to Integer.
Overall, the Excel destination seems to be an after-thought depending on Excel to handle all datatypes that SQL Server has. Maybe if the different development groups at MS actually talked to each other.........
June 19, 2008 at 3:18 am
With a similar problem, rather than change the datatype to unicode in my database (not really an option), I added a convert function to the OLE data source:
CONVERT(nchar, Problem_column) as Problem_Column
Why do the conversion in T-SQL and not in SSIS (as I proposed in my first answer)?
It certainly seems strange that SSIS makes one convert data to uni-code so it can be sent to Excel. The create-worksheet pop-up did its best, trying to define the output as NVarChar, but that was wrong. Worse than wrong, IMO, as it made me think that the incoming data was already unicode.
Of course, it also tried to make the result of a RANK() function BIGINT into Excel as well. I had to change that definition to Integer.
This behavior may be annoying, but I'll rather make explicit conversion choices than be surprised by the results and/or problems from implicit conversions.
The Excel destination uses the Jet engine, and since the Jet engine is Unicode internally, it's appropriate for the Excel destination to be Unicode as well (to prevent implicit conversions). The Jet engine also has no BIGINT equivalent.
If you want an Excel destination that handles datatypes different than the Jet engine does, you should search for or build another Excel destination that has the desired behavior. Extensibility is a wonderful thing 🙂
Peter Rijs
BI Consultant, The Netherlands
June 19, 2008 at 9:01 am
Peter,
Thank you for the further explanation. I think I'm a lot closer to understanding what's happening now.
You ask:
Why do the conversion in T-SQL and not in SSIS (as I proposed in my first answer)?
The package was a one-time extract (although I'll concede that technique counts in terms of setting a precedent) and I was confused as to which way the conversion should go. Your original answer suggested a conversion FROM unicode whereas the final solution required me to convert TO unicode. In that confusion, I decided that coding CONVERT functions in the SQL for my OLE source was the easiest way to try different combinations.
Next time around, I'll certainly try using a data conversion component. Again, thank you for clarifying that the root of the difficulty is in that SSIS is using its connection to send data to Excel's own Jet data engine; it's not SSIS writing directly to the XLS file (as my fuzzy-headed earlier self presumed).
January 16, 2009 at 8:08 am
Hi All,
Hope someone can help with this. I have tried a few of the solutions around the web but am unable to get this to work.
Here's the problem.....
I am trying to run a monthly query, that using ssis, will send the data to a spreadsheet. Howeevr, like a lot of the other posts here i am getting the same "Cannot convert between Unicode and Non-Unicode string data types". I have tried a data conversion using a data flow transformation but still cannot get this to work.
It is a large query and i am having to convert datetime and varchar datatypes.
Got to admit that i am at a loss as to why this is happening, could be i ahven't followed one of the solutions properly. Can anyone help possibly with a detailed step - by -step solution.
thanks in advance.
January 16, 2009 at 8:25 am
Forgot to mention. If anyone wants to reply via email they can reach me at emkafkaesque@googlemail.com
January 16, 2009 at 8:35 am
enzomenoni (1/16/2009)
Hi All,Hope someone can help with this. I have tried a few of the solutions around the web but am unable to get this to work.
Here's the problem.....
I am trying to run a monthly query, that using ssis, will send the data to a spreadsheet. Howeevr, like a lot of the other posts here i am getting the same "Cannot convert between Unicode and Non-Unicode string data types". I have tried a data conversion using a data flow transformation but still cannot get this to work.
It is a large query and i am having to convert datetime and varchar datatypes.
Got to admit that i am at a loss as to why this is happening, could be i ahven't followed one of the solutions properly. Can anyone help possibly with a detailed step - by -step solution.
thanks in advance.
You may find you get more replies if you start a new thread with your question,
What are the data types for your string data in SQL ?
January 16, 2009 at 8:35 am
Here's a copy of the error
TITLE: Package Validation Error
------------------------------
Package Validation Error
------------------------------
ADDITIONAL INFORMATION:
Error at Data Flow Task [Excel Destination [790]]: Columns "Doc.GivenName" and "Document Givenname" cannot convert between unicode and non-unicode string data types.
Error at Data Flow Task [Excel Destination [790]]: Columns "Doc.FamilyName" and "Document Familyname" cannot convert between unicode and non-unicode string data types.
Error at Data Flow Task [Excel Destination [790]]: Columns "Doc.CHI" and "Document CHI" cannot convert between unicode and non-unicode string data types.
Error at Data Flow Task [Excel Destination [790]]: Columns "Doc.OtherID" and "Document Other ID" cannot convert between unicode and non-unicode string data types.
Error at Data Flow Task [Excel Destination [790]]: Columns "Store.GivenName" and "Store Givenname" cannot convert between unicode and non-unicode string data types.
Error at Data Flow Task [Excel Destination [790]]: Columns "Store.FamilyName" and "Store Familyname" cannot convert between unicode and non-unicode string data types.
Error at Data Flow Task [Excel Destination [790]]: Columns "Store.CHI" and "Store CHI" cannot convert between unicode and non-unicode string data types.
Error at Data Flow Task [Excel Destination [790]]: Columns "NoMatchOn" and "No Match On?" cannot convert between unicode and non-unicode string data types.
Error at Data Flow Task [Excel Destination [790]]: Column "Action" cannot convert between unicode and non-unicode string data types.
Error at Data Flow Task [Excel Destination [790]]: Columns "StageStatusDescription" and "Store Error Message" cannot convert between unicode and non-unicode string data types.
Error at Data Flow Task [Excel Destination [790]]: Column "Filename" cannot convert between unicode and non-unicode string data types.
Error at Data Flow Task [Excel Destination [790]]: Columns "XMLMessage" and "XML Message" cannot convert between unicode and non-unicode string data types.
Error at Data Flow Task [DTS.Pipeline]: "component "Excel Destination" (790)" failed validation and returned validation status "VS_ISBROKEN".
Error at Data Flow Task [DTS.Pipeline]: One or more component failed validation.
Error at Data Flow Task: There were errors during task validation.
(Microsoft.DataTransformationServices.VsIntegration)
January 16, 2009 at 8:41 am
varchar
varchar
datetime
varchar
varchar
varchar
varchar
datetime
varchar
January 16, 2009 at 8:42 am
If I'm not mistaken, a friend of mine ran into the same problem.
After search the internet, I believe he found out that this is caused by a BUG.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
January 16, 2009 at 8:43 am
datatypes are
varchar
varchar
datetime
varchar
varchar
varchar
varchar
datetime
varchar
January 16, 2009 at 8:45 am
Are you explicity converting these datatype with a conversion transformation?
Are you able to post a screen-shot of this..?
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply