Search code examples
sql-serverssisetlbidssql-variant

Mismatch on SQL_variant and DT_WSTR in BIDS Manager

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'

Solution

  • 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