Mismatch on SQL_variant and DT_WSTR in BIDS Manager

Question

I'm using BIDS to update some data to SQL Server 2008 R2.

My source is a varchar and destination table has this column VariantValue as a sql_variant datatype. So I have used a derived column transformation to create a unicode new column with this expression: (DT_WSTR,4000)(PNumber). Which means i'm converting a varchar into a unicode and inserting it into a sql_variant column

My slowly changing dimension connection manager is throwing this error:

Cannot map columns of different types.
Column 'PNumber' is of type 'System.String' and column VariantValue is of type 'System.Object'

Answer 1

There are many restrictions for the Sql_Variant data types:

• varchar(max)
• varbinary(max)
• nvarchar(max)
• xml
• text
• ntext
• image
• rowversion (timestamp)
• sql_variant
• geography
• hierarchyid
• geometry
• User-defined types
• datetimeoffset

So i think that your row source is Varchar(Max) so even if you convert it to

(DT_STR,4000,1252)(PNumber)

It will work.

Or you can use a SQL command as Source and CAST the column to VARCHAR(4000) or NVARCHAR(4000) from it.

Note: that when using MAX length, SSIS consider it as a blob data DT_TEXT

References

• sql_variant (Transact-SQL)