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 .

Is the intention that this field is only thing in the output file or would it look something like col1|col2|col3varbinmax? — billinkc
It wont be the only field in the output file , the table contains also ordinary datatypes such as int and varchar . — Tamirt
Could you provide an example of your expected output then? Truncate the varbinary data obviously — billinkc

billinkc billinkc · Accepted Answer · 2012-10-03T17:39:06

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. Simple data flow

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

metadata

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.

Export Column Transformation editor

Export Varbinary(max) column with ssis

1 Answers