Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Extracting VarBinary(MAX) column to Flat File - Facing Issue. Expand / Collapse
Author
Message
Posted Monday, July 21, 2014 1:22 PM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, August 18, 2014 7:36 AM
Points: 22, Visits: 118
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 A Drop,UnKnown Is An Ocean....
Njoy Programming
:)
Post #1594797
Posted Monday, July 21, 2014 5:14 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 10:33 PM
Points: 35,259, Visits: 31,740
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1594880
Posted Monday, July 21, 2014 5:54 PM This worked for the OP Answer marked as solution


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Monday, September 22, 2014 10:53 PM
Points: 5,383, Visits: 7,456
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 | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1594888
Posted Thursday, July 24, 2014 9:34 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, August 18, 2014 7:36 AM
Points: 22, Visits: 118
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 A Drop,UnKnown Is An Ocean....
Njoy Programming
:)


  Post Attachments 
Capture1.JPG (9 views, 140.64 KB)
Post #1595940
Posted Thursday, July 24, 2014 10:57 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Monday, September 22, 2014 10:53 PM
Points: 5,383, Visits: 7,456
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 | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1595963
Posted Thursday, July 24, 2014 11:56 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, August 18, 2014 7:36 AM
Points: 22, Visits: 118
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 A Drop,UnKnown Is An Ocean....
Njoy Programming
:)
Post #1595976
Posted Thursday, July 24, 2014 12:40 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Monday, September 22, 2014 10:53 PM
Points: 5,383, Visits: 7,456
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 | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1595989
Posted Thursday, July 24, 2014 2:14 PM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, August 18, 2014 7:36 AM
Points: 22, Visits: 118
Yeah.. I tried that too.. but getting the same exception

Known Is A Drop,UnKnown Is An Ocean....
Njoy Programming
:)


  Post Attachments 
Double.JPG (6 views, 122.46 KB)
Post #1596015
Posted Thursday, July 24, 2014 2:41 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Monday, September 22, 2014 10:53 PM
Points: 5,383, Visits: 7,456
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 | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1596024
Posted Wednesday, July 30, 2014 11:47 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, August 18, 2014 7:36 AM
Points: 22, Visits: 118
Thanks Guys for your guidance...

I have overcomes this problem using XQuery functionality.


Known Is A Drop,UnKnown Is An Ocean....
Njoy Programming
:)
Post #1597880
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse