Export Varbinary(max) column with ssis

Question

we've a table with a varbinary(max) column, the column is used for a Rich text field in the Application (not for Attaching docs) . I'm trying to export the column into a flat file through a very simple ssis package. my package contain oledb cource and flat file destination .

when i opened the flat file output , i've found out to my surprise the the data was truncated to 255 characters, do you have any idea how to export all the data completely?

Thx for the help .

Answer 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.