BLOB or VARBINARY(MAX) as SSIS source.

Hi all,

Can a SSIS package treat a file from a table ( VARBINARY(MAX) or BLOB) as a source for migration ?

Thanks in advance,

DBAnalyst

Sure. The varbinary(max) SQL 2005 data type maps to the SSIS type DT_IMAGE, which is just a bunch of bytes of which the file consists.

So what you have at that point is a byte stream, or byte array.

To use those bytes as file source, you could write them to a file in one dataflow and read them in a second dataflow.

Another way to use varbinary(max) data as a "file source" would be to use a script or custom source component (its not that painful) to wrap a StreamReader object arround the SQL byte stream. Using this approach, the varbinary(max) never touches down to disk as an intermediate step.

There are certainly other techniques, but those come to mind.|||Thanks a lot Jaegd.