SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
windows_mss
windows_mss
Mr or Mrs. 500
Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)

Group: General Forum Members
Points: 564 Visits: 180
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
:-)
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)

Group: General Forum Members
Points: 220188 Visits: 42002
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Evil Kraig F
Evil Kraig F
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21271 Visits: 7660
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
windows_mss
windows_mss
Mr or Mrs. 500
Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)

Group: General Forum Members
Points: 564 Visits: 180
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
:-)
Attachments
Capture1.JPG (23 views, 140.00 KB)
Evil Kraig F
Evil Kraig F
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21271 Visits: 7660
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
windows_mss
windows_mss
Mr or Mrs. 500
Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)

Group: General Forum Members
Points: 564 Visits: 180
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
:-)
Evil Kraig F
Evil Kraig F
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21271 Visits: 7660
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
windows_mss
windows_mss
Mr or Mrs. 500
Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)

Group: General Forum Members
Points: 564 Visits: 180
Yeah.. I tried that too.. but getting the same exception

___
Known Is An Drop,Unknown Is An Ocean....
Njoy Programming
:-)
Attachments
Double.JPG (19 views, 122.00 KB)
Evil Kraig F
Evil Kraig F
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21271 Visits: 7660
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
windows_mss
windows_mss
Mr or Mrs. 500
Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)

Group: General Forum Members
Points: 564 Visits: 180
Thanks Guys for your guidance...

I have overcomes this problem using XQuery functionality.

___
Known Is An Drop,Unknown Is An Ocean....
Njoy Programming
:-)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search