Microsoft.SqlServer.Dts.Pipeline.BlobColumn

I am using Component Script to do - Transforming Comma-delimited list row data to column

and I want to use MessageBox to see the value

Dim DataPnts As String

DataPnts = Row.DataPnts.ToString() -- this is my input column (data type = text in Source table and I put as Unicode string [DT_WSTR] in Output column)

MessageBox.Show(DataPnts, "DataPoints1", MessageBoxButtons.OK)

and why can't I see it. It gives me some message with Microsoft.SqlServer.Dts.Pipeline.BlobColumn. Why?

Values = DataPnts.Split(CChar(","))

Please point me to more info on how to do transform Comma-delimited list row data to column.

Thanks.

Vita wrote:

and why can't I see it. It gives me some message with Microsoft.SqlServer.Dts.Pipeline.BlobColumn. Why?

It would be very valuable if you posted here the full message you're receiving.

|||

The only thing I see in the message box is - "Microsoft.SqlServer.Dts.Pipeline.BlobColumn" - exact words.

And that is it. No value at all.

And if I do

Values = Row.DataPntsConv.Split(CChar(",")) - it shows me the error that Split is not a member of Microsoft.SqlServer.Dts.Pipeline.BlobColumn.

This is why I created a Variable as String and tried to do a Split on it.

I think the problem with text data type I have in the Source table for this field.

|||

Look at the type of the object, it is not a string type or something that supports casting top a string. It is a complex type, a Microsoft.SqlServer.Dts.Pipeline.BlobColumn class actually.

Take a closer look at the methods on Row.DataPnts, I think there should be so read and write methods. Use them to get the data you want. Just bear in mind that this is a big texttype, not a string, and you don't want it leaving up to 2GB of data in a string for you!

|||

Ok, I must be extra-dense today. But the light bulb just came on.

The problem is that the Microsoft.SqlServer.Dts.Pipeline.BlobColumn class inherets its implementation of the ToString method from the Object base class. This means that it just returns the name of the class: "Microsoft.SqlServer.Dts.Pipeline.BlobColumn." As you can probably imagine, this would handily explain the MessageBox message you've reported.

So... You actually need to use the BlobColumn.GetBlobData method instead: http://msdn2.microsoft.com/en-us/library/microsoft.sqlserver.dts.pipeline.blobcolumn.getblobdata.aspx

Enjoy!

|||

Thanks!

Can you give the example of it?

How to use it in

DataPnts = Row.PointsValue.ToString()

Values = DataPnts.Split(CChar(",")

May be I am missing some references to use it, but if I put GetBlobData - the code shows error.

I use already

Namespace: Microsoft.SqlServer.Dts.Pipeline
Assembly: Microsoft.SqlServer.PipelineHost (in microsoft.sqlserver.pipelinehost.dll)

Or may be I should put something before the word GetBlobData. Like buffer or something like it.

Thanks.

|||

I do not have an example to share. As Darren pointed out, this BLOB colum can be very large, so it's non-trivial to handle all cases, as depending on the size of the data in each row you may be able to get it all in one pass, or you may need to batch it into many smaller passes.

Basically you need to declare a variable of type Byte array, and then assign that value with the value returned from the call to Row.PointsValue.GetBlobData(). I can't really get more specific than this without writing the code myself...

|||How do I do a split on BlobData?|||You can't do it directly - you have to get the blob into a string first.