使用 ssis 导出 Varbinary(max) 列 [英] Export Varbinary(max) column with ssis

问题描述

我们有一个带有 varbinary(max) 列的表，该列用于应用程序中的富文本字段(不用于附加文档).我正在尝试通过一个非常简单的 ssis 包将该列导出到一个平面文件中.我的包包含 oledb 源和平面文件目标.

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 .

当我打开平面文件输出时，我惊讶地发现数据被截断为 255 个字符，您知道如何完全导出所有数据吗?

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?

感谢帮助.

推荐答案

在等待您打算如何使用它的说明时，我建议您查看 导出列转换.在这个问题上有类似的需求使用 SSIS将表数据的 XML 表示提取到文件中

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

我用了一个简单的例子来说明如何导出 varbinary 数据.以下查询将一些字符串连接在一起，然后将它们转换为 varbinary(max).它还会生成第二列，作为所使用的输出文件.

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;

配置 SSIS 非常简单.我使用上面的查询作为我的来源.

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

我确保元数据看起来像预期的一样 - 是的，BLobData 列是一个图像

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

配置导出列转换.如果文件已经存在，任务将失败，如图所示.您需要选中 Allow Append 或 Force Truncate 选项.

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.

这篇关于使用 ssis 导出 Varbinary(max) 列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！