0

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 .

Tamirt
  • 39
  • 2
  • 7
  • Is the intention that this field is only thing in the output file or would it look something like col1|col2|col3varbinmax? – billinkc Oct 03 '12 at 16:23
  • It wont be the only field in the output file , the table contains also ordinary datatypes such as int and varchar . – Tamirt Oct 07 '12 at 10:30
  • Could you provide an example of your expected output then? Truncate the varbinary data obviously – billinkc Oct 07 '12 at 13:24

1 Answers1

3

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

Community
  • 1
  • 1
billinkc
  • 59,250
  • 9
  • 102
  • 159
  • Could you provide an example of what your expected output would be? How should it behave when an embedded carriage returns in the varbinary data? – billinkc Oct 09 '12 at 16:17
  • Hi, We'll probably have to be settle with the export column comp. and add another column with the file path . thx for your help – Tamirt Oct 09 '12 at 16:59