I am trying to load the data from a CSV file to a table in Sql Server R2 2008, using SSIS custom package, and i am getting a Package Validation Error. Error at Data Flow Task [OLE DB Destination [34]]: Column "Defense In Depth Layer" cannot convert between unicode and non-unicode string data types. Error at Data Flow Task [OLE DB Destination [34]]: Column "Sub Layer" cannot convert between unicode and non-unicode string data types. Error at Data Flow Task [OLE DB Destination [34]]: Column "Capability" cannot convert between unicode and non-unicode string data types. Error at Data Flow Task [OLE DB Destination [34]]: Column "Requirement Name" cannot convert between unicode and non-unicode string data types. Error at Data Flow Task [SSIS.Pipeline]: "component "OLE DB Destination" (34)" 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) Can anyone please let me know as to how to resolve this issue. I am new to Sql server environment, as i have migrated from Oracle database environment. Appreciate any help. Thanks!

I think your Destination is non-unicode (DT_STR) and the Source is (DT_WSTR) for Defense In Depth Layer, Sub Layer, Capability and Requirement name columns. Add a Data Conversion in the middle and convert the following columns to DT_STR. Use the converted columns in Mapping in the OLE DB Destination. Or Change the type of the columns in your database to NVARCHAR (for Defense In Depth Layer, Sub Layer, Capability and Requirement name ), if you are expecting special characters (Chinese, Korean etc..) After the change, double click the OLE DB Destination column to refresh it. Randy Aldrich Paulo MCTS(BizTalk 2010/2006,WCF NET4.0), MCPD | My Blog BizTalk Message Archiving - SQL and File

Thanks for the quick reply Randy, The Source is not (DT_WSTR) data type but it is String (DT_STR) data type. I have set the columns in my database as NVARCHAR. So i dont think i need to add Data Conversion in the middle and convert the columns to DT_STR, when they are already in DT_STR format in the Source. Do you think that i'll have to change my Destination columns data type to DT_STR? Mohamed Rahman

Hi, Check this link on how to load hte data from csv file ot the tables. Hope this will be useful. http://beyondrelational.com/blogs/ashish/archive/2010/04/25/loading-data-from-text-files-to-sql-server-tables-csv-files-tab-separated-files-or-fixed-column-length-files.aspxThanks Karthikeyan Anbarasan http://f5debug.net/

Also check this step by step article on loading the CSV file. Hope this will be useful. http://beyondrelational.com/blogs/tejas/archive/2011/04/27/sql-server-ssis-basic-example-of-data-flow-task.aspxThanks Karthikeyan Anbarasan http://f5debug.net/

Like I said if you're expecting expecting special characters, non-ascii (Chinese, Korean etc..) then you need to update your column to use NVARCHAR (DT_WSTR)Randy Aldrich Paulo MCTS(BizTalk 2010/2006,WCF NET4.0), MCPD | My Blog BizTalk Message Archiving - SQL and File

Thanks for the quick reply Randy, The Source is not (DT_WSTR) data type but it is String (DT_STR) data type. I have set the columns in my database as NVARCHAR. So i dont think i need to add Data Conversion in the middle and convert the columns to DT_STR, when they are already in DT_STR format in the Source. Do you think that i'll have to change my Destination columns data type to DT_STR? Mohamed Rahman If you source is DT_STR (=varchar) and your destination is nvarchar, then you need to cast/convert somewhere in your package. The message is clear: cannot convert between unicode and non-unicode string data types. 1) change your flat file source from DT_STR to DT_WSTR 2) change your destination columns from nvarchar to varchar 3) Use a Data Conversion Transformation to convert the DT_STR to DT_WSTR 4) Use a Derived Column with a CAST to convert the DT_STR to DT_WSTRPlease mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com

Please check if the metadata shows the destination Column as DT_STR, If not then you'll have to convert the Source Column (DT_STR) to (DT_WSTR). Please mark this post as answer / mark it as helpful if it resolves your Problem. Thanks Subhash Subramanyam