2

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'

Hadi
  • 36,233
  • 13
  • 65
  • 124
Nade
  • 23
  • 4
  • Because you cannot convert an object to nvarchar. sql_Variant datatype contains serveral datatypes ex int,binary or char values. And SSIS does not fully support sql_Variant and i can see you use 2008R2. Im not sure they even support it at all. – SqlKindaGuy Nov 23 '17 at 13:07

1 Answers1

0

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

Hadi
  • 36,233
  • 13
  • 65
  • 124