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.
