BlobColumn to String

• yukinonon

  SSC Enthusiast

  Points: 172

  More actions

  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?

• Phil Parkin

  SSC Guru

  Points: 247158

  More actions

  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;

  ---