Post reply

Extracting VarBinary(MAX) column to Flat File - Facing Issue.

  • July 21, 2014 at 1:22 pm

    #310139

    I’m extracting data from the table to flat file using SSIS,during this process I’m facing the data conversion issue in the varBinary(max) data column. To overcome this issue I have used Data Conversion in my data flow to convert to varBinary(max) to DT_NText but I’m getting the following error,

    Error at Patient Documents Data Flow Task [Flat File Destination [1252]]: The data type for "input column 'MYCOLUMNNAME' (1355)" is DT_NTEXT, which is not supported with ANSI files. Use DT_TEXT instead and convert the data to DT_NTEXT using the data conversion component.

    Error at Patient Documents Data Flow Task [SSIS.Pipeline]: "component "Flat File Destination" (1252)" failed validation and returned validation status "VS_ISBROKEN".

    Error at Patient Documents Data Flow Task [SSIS.Pipeline]: One or more component failed validation.

    Error at Patient Documents Data Flow Task: There were errors during task validation. (Microsoft.DataTransformationServices.VsIntegration)

    So I have used TEXT type in data conversion but I'm not getting the Binary value "0x" is discarded,

    DB Content = 0x3C436C696E69 (It's Part of the content)

    After Data Conversion from VARBINARY(max) to Text "3C436C696E69" , "0x" is missing. Exactly I need to extract the varBinary data to flat file as it is like in DB.

    Kindly Guide me where I'm doing wrong and help me out to overcome this issue.

    ___
    Known Is An Drop,Unknown Is An Ocean....
    Njoy Programming     🙂

  • July 21, 2014 at 5:14 pm

    #1731263

    Try NOT using references to Unicode when dealing with binary data, which is no where near being Unicode.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • July 21, 2014 at 5:54 pm

    #1731270

    Derive the column after the conversion and prepend the 0x.

    DT_BYTES has a max length of 8000, which won't handle your MAX. Since there's a single binary value you shouldn't need to overengineer the solution.

    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
    Twitter: @AnyWayDBA

  • July 24, 2014 at 9:34 am

    #1732134

    Hi Evil,

    Thanks for your reply, when i prepend 0x in script it's working. But when i tried the same in SSIS using derived column I'm getting exception(i have attached the screenshot as attachment)

    ___
    Known Is An Drop,Unknown Is An Ocean....
    Njoy Programming     🙂

  • July 24, 2014 at 10:57 am

    #1732154

    windows_mss (7/24/2014)

    Hi Evil,

    Thanks for your reply, when i prepend 0x in script it's working. But when i tried the same in SSIS using derived column I'm getting exception(i have attached the screenshot as attachment)

    Expressions don't use the same quotation components.

    You want "0x" + @Column

    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
    Twitter: @AnyWayDBA

  • July 24, 2014 at 11:56 am

    #1732166

    Yes Evil, 0x+@Column. Currently File is extract with the following data "3C436C696E6963" after DT_Text conversion but it has be prefixed with Hex String 0x3C436C696E6963

    ___
    Known Is An Drop,Unknown Is An Ocean....
    Njoy Programming     🙂

  • July 24, 2014 at 12:40 pm

    #1732175

    windows_mss (7/24/2014)

    Yes Evil, 0x+@Column. Currently File is extract with the following data "3C436C696E6963" after DT_Text conversion but it has be prefixed with Hex String 0x3C436C696E6963

    You misunderstand. You're using apostrophes (like T-SQL wants) instead of quotes (like Expressions want).

    Switch '0x' + Column

    to "0x" + column

    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
    Twitter: @AnyWayDBA

  • July 24, 2014 at 2:14 pm

    #1732198

    Yeah.. I tried that too.. but getting the same exception

    ___
    Known Is An Drop,Unknown Is An Ocean....
    Njoy Programming     🙂

  • July 24, 2014 at 2:41 pm

    #1732204

    Have you already converted content_document to type TEXT before attempting that? Otherwise, you're attempting to append "0x" to a binary value, which it fails. If you're trying to convert it inline, you have to convert the binary first, so "0x" + (DT_Text)Content_Document

    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
    Twitter: @AnyWayDBA

  • July 30, 2014 at 11:47 am

    #1733734

    Thanks Guys for your guidance...

    I have overcomes this problem using XQuery functionality.

    ___
    Known Is An Drop,Unknown Is An Ocean....
    Njoy Programming     🙂

  • July 30, 2014 at 11:54 am

    #1733739

    windows_mss (7/30/2014)

    Thanks Guys for your guidance...

    I have overcomes this problem using XQuery functionality.

    Any chance of you posting the code/package you ended up with?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • August 22, 2014 at 1:50 pm

    #1740400

    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

  • 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

  • August 22, 2014 at 3:20 pm

    #1740421

    Thanks for your reply.

    The problem I cannot use a staging table since the data is sensitive data.

  • August 22, 2014 at 4:53 pm

    #1740431

    Hi,

    As mentioned below i need to convert varchar field to varbinary in SSIS.Please suggest.

    PS : *)Cannot use Staging table.

    *)Sensitive data(in my case SSN) should be encrypted on the fly. And should be stored in encrypted format.

    ISSUE :

    ******

    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 the flat file content to a SQL table.This SQL table has a column named 'SSN' defined as VARBINARY(300).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 the 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

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply

Cookies on SQLServerCentral

This website stores cookies on your computer.

These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

To find out more about the cookies we use, see our Privacy Policy