SSIS: Convert between Unicode and Non-Unicode Strings

0
votes

Setup

I have a data flow task with:

  • An OLE DB Source selecting all data from a database table (SQL Native)
  • An OLE DB Destination of an Access 2003 File template (MS Jet 4.0)

So Essentially I am selecting a table and inserting it into a .mdb file.

Problem

I have the error:

column [X] cannot convert between unicode and non-unicode string data types

However If I add a 'Data Conversion Step' to convert all columns of type DT_STR to DT_WSTR, that error message goes away however I get the error message:

[DTS.Pipeline] Error: "component "OLE DB Source" (6289)" failed validation and returned validation status "VS_NEEDSNEWMETADATA".

Iv'e been Googling this problem for a while and still can't fix it. Any ideas?

1
sql-serversql-server-2005ssisvisual-studio-2005
What is the datatype of your source columns? Are those nvarchar? – Dibin
All strings in the source (SQL database table) are varchars, not nvarchars. – Jamie Stuart Robin Parsons
Delete your OLE DB Source and add it again. It looks like its meta data is out of sync. I dont know why it would be tho – TsSkTo
"selecting all data from a database" All data from a single table or is it truly that you have some query that is attempting to generate all the data from all the tables in the database? If it's the former, how are you acquiring the data? Did you select the table name in the dropdown list or write a specific query? What is the source database? You list SQLNative but is that hitting SQL Server, SQL Server express, something else? What version is that database? – billinkc
@TsSkTo - I have tried this multiple times including the entire Data Flow Task Object and the error reoccurs – Jamie Stuart Robin Parsons

1 Answers

1
votes

Instead of

Select * from tblName

use

Select col1, col2, col3, etc from tblName

The reason I found is, when you use different database, they might have one or two column that cause issue as it's not mapped. So when you use table query, be specific regarding column name.