none
Convert BLOB to Text

    Question

  • I have a source DB that contains a BLOB which is really a bunch of text. I am trying to use SSIS to read this BLOB and stream the chars as text to a flat file which will later be FTP'ed to another server and reloaded as nvarchar records.

     

    In my data flow task I have my source (SQL DB BLOB) and target (Flat File) of course...in between I am using a script component to try and convert the BLOB. Here is my code:

     

            Dim intBlobLength As Integer = Convert.ToInt32(Row.denoteblob.Length)
            Dim intFinish As Integer = intBlobLength - 1

            Dim byteBlob(intFinish) As Byte
            byteBlob = Row.denoteblob.GetBlobData(0, intFinish)

            Row.outdenoteline. = byteBlob
            Row.outdebtorrowid = Row.debtorrowid

     

     

    I get a pipeline is to small error. I cant seem to adjust the size of the outdenoteline within the script componenet.

     

    please help!

     

    Regards.

    Friday, March 14, 2008 3:23 PM

Answers

  •  dgrm44 wrote:

     I cant seem to adjust the size of the outdenoteline within the script componenet.

     

    You cannot do this. The SSIS data flow does not allow its metadata (such as the size and data types of columns) to be changed at runtime.

     

    When working with BLOB data, you may want to look at the Import Column and Export Column transformations to see if they give you what you need:

     

    http://technet.microsoft.com/en-us/library/ms141262.aspx

     

    Import Column Transformation

    The Import Column transformation reads data from files and adds the data to columns in a data flow. Using this transformation, a package can add text and images stored in separate files to a data flow. For example, a data flow that loads data into a table that stores product information can include the Import Column transformation to import customer reviews of each product from files and add the reviews to the data flow.

     

    http://technet.microsoft.com/en-us/library/ms139818.aspx

     

    Export Column Transformation

    The Export Column transformation reads data in a data flow and inserts the data into a file. For example, if the data flow contains product information, such as a picture of each product, you could use the Export Column transformation to save the images to files.

    You can configure the Export Column transformation in the following ways:

    • Specify the data columns and the columns that contain the path of files to which to write the data.
    • Specify whether the data-insertion operation appends or truncates existing files.
    • Specify whether a byte-order mark (BOM) is written to the file.
      Note:
      A BOM is written only when the data is not appended to an existing file and the data has the DT_NTEXT data type.
    Saturday, March 15, 2008 8:13 PM
    Moderator

All replies

  • Row.outdenoteline.Length = intFinish ?

    hth
    Saturday, March 15, 2008 8:13 AM
    Moderator
  • Doesnt support changing the lenght in the vb code. Any other ideas?

     

    Saturday, March 15, 2008 3:16 PM
  •  dgrm44 wrote:

     I cant seem to adjust the size of the outdenoteline within the script componenet.

     

    You cannot do this. The SSIS data flow does not allow its metadata (such as the size and data types of columns) to be changed at runtime.

     

    When working with BLOB data, you may want to look at the Import Column and Export Column transformations to see if they give you what you need:

     

    http://technet.microsoft.com/en-us/library/ms141262.aspx

     

    Import Column Transformation

    The Import Column transformation reads data from files and adds the data to columns in a data flow. Using this transformation, a package can add text and images stored in separate files to a data flow. For example, a data flow that loads data into a table that stores product information can include the Import Column transformation to import customer reviews of each product from files and add the reviews to the data flow.

     

    http://technet.microsoft.com/en-us/library/ms139818.aspx

     

    Export Column Transformation

    The Export Column transformation reads data in a data flow and inserts the data into a file. For example, if the data flow contains product information, such as a picture of each product, you could use the Export Column transformation to save the images to files.

    You can configure the Export Column transformation in the following ways:

    • Specify the data columns and the columns that contain the path of files to which to write the data.
    • Specify whether the data-insertion operation appends or truncates existing files.
    • Specify whether a byte-order mark (BOM) is written to the file.
      Note:
      A BOM is written only when the data is not appended to an existing file and the data has the DT_NTEXT data type.
    Saturday, March 15, 2008 8:13 PM
    Moderator