Export Varbinary(max) column with ssis

Solution 1:

Whilst awaiting clarification on how you intend to use it, I'd suggest looking at the Export Column Transformation. Similar need on this question Using SSIS to extract a XML representation of table data to a file

I banged out a quick example that illustrates how to do export varbinary data. The following query concatenates some strings together before casting them as varbinary(max). It also generates a second column which will be the output file used.

WITH STARTER(starter) AS
(
    -- some simple data
    SELECT 'a'
    UNION ALL SELECT 'b'
    UNION ALL SELECT 'c'
)
,  VCM(longenough) AS
(
    SELECT
        CAST(REPLICATE(T.starter, 8000) AS varchar(max))
    FROM
        STARTER T
)
SELECT
    CAST(V0.longenough + V1.longenough AS varbinary(max)) AS BlobData
,   'C:\ssisdata\filename.' + CAST(row_number() OVER (ORDER BY (SELECT NULL)) AS varchar(10)) + '.txt' AS FileName
FROM
    VCM V0
    CROSS APPLY
    VCM V1;

Configuring SSIS is a snap. I used the above query as my source.

I ensured the metadata looked as expected - yup, the BLobData column is an image

Configure the Export Column transformation. If the file already exists, the task will fail as shown. You would either need to check the Allow Append or Force Truncate option.