Post reply

BlobColumn to String

  • September 30, 2022 at 3:15 am

    #4093783

    I have this column in sql server that has value of "dog,cat,mouse" so I want to fetch this data into SSIS then transform it into an array using script so I can loop through it but I'm having an error when doing so.

    var columnfromDB = "1,2,3"; // Need population so that Split won't throw an error

    if (Row.Animals != null)

    {

    columnfromDB = Row.Animals; // This is where the error comes from since Row.Animal is a BlobColumn

    string[] splittedString = columnfromDB.Split(',');

    }

    Any tips on how to solve this?

  • September 30, 2022 at 8:30 am

    #4093870

    Use a query to get your data from SQL Server into SSIS and use CAST in the query to get the BLOB into string format

    DROP TABLE IF EXISTS #Blob;

    CREATE TABLE #Blob
    (
        SomeBLOB VARBINARY(MAX) NOT NULL
    );

    INSERT #Blob
    (
        SomeBLOB
    )
    VALUES
    (CAST('Dog,Cat,Mouse' AS VARBINARY(MAX)));

    SELECT b.SomeBLOB
          ,SomeText = CAST(b.SomeBLOB AS VARCHAR(8000))
    FROM #Blob b;

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

Viewing 2 posts - 1 through 1 (of 1 total)

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