Blob field Help (SSIS)

Question

Blob field Help (SSIS)

rocky_498

SSCertifiable

Points: 6532
More actions
June 22, 2011 at 7:32 pm

#253242

My source has Blob Field (Column has LInk or you can hit world file to see whole description). I am using SSIS to transfer that field. In Data Conversion Transformation showing Data type of this field is "Image" My question is should i keep same data type "Image" or change to ? and how about destination field?
I am having problem if i use same source data type "Image" and destination data type "Image" or "ntext". Please let me know if need more information thanks.

Viewing 9 posts - 1 through 8 (of 8 total)

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

COZYROC One Orange Chip Points: 28499 More actions · Answer 1

Your question is very unclear. Can you post more details what kind of problem do you have? What is the exact error message you get?

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

rocky_498 SSCertifiable Points: 6532 More actions · Answer 2

Thanks for your reply, Here is the situation, I have Access Source and Sql Destination, I am using SSIS, One of the source file has link, Once you hit that link word file open with Description and Description has more than 2000 character. In Data Conversion Transformation the field has "Image" Data type" I can change that data type of "Ntext" and transfer that field and package run successful, but when i check source i am still getting link of the file as an text and i can't hit or nothing, I want the whole description in the target field as a text, Please feel free to let me know if still my question is not clear. Thanks for your help.

COZYROC One Orange Chip Points: 28499 More actions · Answer 3

Still very unclear. Actually you almost repeated the same description. I don't know where you get these links , in what system , etc. It looks like you are mixing SSIS with some other process, which is confusing. Can you start from the beginning , providing more details step-by-step? Assume I'm completely clueless .

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

rocky_498 SSCertifiable Points: 6532 More actions · Answer 4

No problem,

Source ==> OLE Db DEStination (Access)

Transformation ==> If Needed

Destination ==> SQL

One field in Access Source called "Description" Here is Table Structure as an example

ID FNAME DESCRIPTION

1 SMITH (Description field is listed below)

0x151C2D00020000000900100014001D00FFFFFFFF446F63756D656E7400576F72642E446F63756D656E742E3800010500000200000010000000576F72642E446F63756D656E742E38000000000000000000006E0000D0CF11E0A1B11AE1000000000000000000000000000000003E000300FEFF0900060000000000000000000000010000000100000000000000001000000200000001000000FEFFFFFF0000000000000000FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF

If i hit this link in access table they open new word file with whole description as a text.

My question is how i can convert this link to actual description? Meaning in destination without clicking anything i can see full Description.

COZYROC One Orange Chip Points: 28499 More actions · Answer 5

I think I have better idea now what is needed. This field actually contains a complete Microsoft Word document , right? I don't think it will be easy for you to extract the text from this field.

You have to:

1. Use the standard Export Column Transformation to extract this information into an external file.

2. Implement a script, which uses the Microsoft Word automation API to load the external file from step 1. The script will then extract the description into a new column in your data flow. You will then be able to load this column with the description into your destination database.

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

rocky_498 SSCertifiable Points: 6532 More actions · Answer 6

Thanks for your reply. Do you know any tutorial or Form about this issue? i google it but no luck.

COZYROC One Orange Chip Points: 28499 More actions · Answer 7

rocky_498 (6/22/2011)
Thanks for your reply. Do you know any tutorial or Form about this issue? i google it but no luck.

No idea. I would recommend you ask about the Microsoft Word API in this forum. Before posting make sure you are indeed dealing with Word documents. Try to export one column into a file with extension .doc and then try to open in Word.

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 8

rocky_498 (6/22/2011)
If i hit this link in access table they open new word file with whole description as a text.
My question is how i can convert this link to actual description?
Meaning in destination without clicking anything i can see full Description.

Sorry, came in a little late here.

You don't, not directly. You're dealing with a binary file. It's the equivalent of having the bytes for a jpg without using mspaint to see it.

You can store binary (LOB = Large Object Binary) data in SQL Server, but that doesn't mean it wants to work with it.

If it was only text you'd be fine. However, Word Docs (and other software) have header/footer wrappers on their files that interfere.

Also, if you can wrap that line for the description in a code shortcut (like code="plain") wrapper, the thread page will stop being oversized.

- 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