Home Forums Data Warehousing Integration Services Extracting VarBinary(MAX) column to Flat File - Facing Issue. RE: Extracting VarBinary(MAX) column to Flat File - Facing Issue.

  • August 22, 2014 at 3:00 pm

    #1740415

    nishav2 (8/22/2014)

    Hi,

    I am facing similar kind of problem.I have an SSIS package which has a flat file source and an oledb destination.

    I am transferring flat file content to a table.This SQL table has a column named 'SSN' defined as VARBINARY(300).Because i need to store the encrypted value in it and not the actual value.

    I have created an 'Instead of Insert' Trigger on that table.So this trigger will encrypt and store the value in SSN field.

    But in text file this SSN column comes as Varchar. How to type cast this varchar to varbinary in SSIS package.

    I tried to convert the datatype by using Derived column,Data conversion etc.,But it did not work out.

    Can anyone help me on this!

    Thanks

    Nisha V Krishnan

    As a work around i tried it in two steps

    1) in the first DFT i got the data from the flat file to a staging table

    2) then in the second DFT i used the SQL Command to cast the column to binary.

    As of now this seems to be working.

    thanks