Search code examples
ssisetlbusiness-intelligencessis-data-types

How to achieve similar datatype

Goal:
Combine two column named first and lastname, from the same table A and then transfer it to column fullname in table B from a another relational database.

Column first and lastname has the same datatype as fullname. The datatype is varchar(50) or varchar(100).

Problem:
I can't make the transaction to have the same datatype

enter image description here

Solution

  • You need to use the type cast expression DT_STR in Derived Column transformation so that the output from Derived Column transformation is still in varchar data type.

    Below shown Derived Column Transformation shows two new columns.

    First new column FullName takes in two input columns FirstName and LastName. Concatenates the columns with a space to separate them and then type casts to DT_STR. In (DT_STR, 100, 1252), 100 represents the length of the output column, 1252 represents the code page.

    Second new column FullNameNoCast simply concatentates the two input columns FirstName and LastName. This will result in Unicode data type.

    Since, you mentioned that your destination is of varchar data type. I believe that you are not type casting the new column in Derived Column transformation. That might lead to the error you are facing.

    Hope that helps.

    Derived Column