Blob field Help (SSIS)

  • 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.

  • 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/

  • 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.

  • 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/

  • 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.

  • 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/

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

  • 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/

  • 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

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

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