SSIS OLE DB命令数据流转换SqlCommand大小限制

I'm running into a situation in a data flow where I think I'm reaching the size limit of the SqlCommand property of an OLE DB Command data flow transformation. Let me give you the set up:

我遇到了数据流中的情况,我认为我已达到OLE DB命令数据流转换的SqlCommand属性的大小限制。让我给你设置:

I have a text file source containing a few columns. One of the columns is a string object with upwards of 50,000 characters. I wrote a stored procedure that accepts these columns, including the string object which gets parsed and added as several new rows in a SQL table. It works fine when the stored proc is called within SQL Server Management Studio and I can pass in large amounts of text (50k+).

我有一个包含几列的文本文件源。其中一列是一个超过50,000个字符的字符串对象。我编写了一个接受这些列的存储过程,包括在SQL表中解析并添加为几个新行的字符串对象。当在SQL Server Management Studio中调用存储过程并且我可以传入大量文本(50k +)时,它工作正常。

Now in my data flow object in SSIS, I have an OLE DB Command transformation which calls the stored proc passing in the columns as parameters, including the string object (DT_NTEXT data type). If the text file column containing the string has less than 33,000 characters, the data flow works fine. Once it runs into a row in which the string column goes over the size of 32,767 characters, the data flow triggers a truncation error and that row is never processed.

现在在SSIS中的数据流对象中,我有一个OLE DB命令转换,它调用作为参数传递列中的存储过程,包括字符串对象(DT_NTEXT数据类型)。如果包含该字符串的文本文件列少于33,000个字符,则数据流可以正常工作。一旦它遇到一个字符串列超过32,767个字符大小的行,数据流就会触发截断错误,并且永远不会处理该行。

The SQLCommand property only has 1 line which is the stored proc call:

SQLCommand属性只有1行,它是存储的proc调用:

EXEC usp_ParseDataColumns ?,?,?

The last parameter is the string object which can get very large. I'm assuming the transformation replaces each parameter with the row's data during run-time and if the property's value size exceeds 32,767 characters, it truncates and generates an error.

最后一个参数是字符串对象,它可以变得非常大。我假设转换在运行时用行的数据替换每个参数,如果属性的值大小超过32,767个字符,它会截断并生成错误。

The error output that is generated by the OLE DB Command transformation is:

OLE DB命令转换生成的错误输出是:

The data value cannot be converted for reasons other than sign mismatch or data overflow.

由于符号不匹配或数据溢出以外的原因,无法转换数据值。

I tried searching for several alternatives, including using a variable, but not sure if that's the right path. I'm using an OLE DB Command because I need to perform other transformations afterwards on each row.

我尝试寻找几种替代方案,包括使用变量,但不确定这是否是正确的路径。我正在使用OLE DB命令,因为我需要在每一行后执行其他转换。

How can I resolve this or is there a better alternative?

我该如何解决这个问题还是有更好的选择?

1 个解决方案

#1

I have never encountered the OLEDB command being truncated, that's an odd one. Please post the full error when you can.

我从来没有遇到OLEDB命令被截断,这是一个奇怪的。请尽可能发布完整的错误。

As for a fix, I suggest re-architecting this data flow a little bit. Break the work into logical pieces and stage the data multiple times. So, for example,

至于修复,我建议稍微重新构建这个数据流。将工作分解为逻辑部分并多次分阶段处理数据。所以,例如,

instead of using an OLEDB command, insert the data into a staging table.

而不是使用OLEDB命令,将数据插入临时表。

When the data flow task is complete, us an execute SQL task to either run that stored proc in a cursor, or better, execute a proc that will use the staged data as a source and perform a set based operation.

当数据流任务完成时,我们执行SQL任务以在游标中运行该存储过程,或者更好,执行将使用分阶段数据作为源并执行基于集合的操作的过程。

Finally, use another execute SQL task or another data flow to finish the operation (whatever was happening after the OLEDB command)

最后,使用另一个执行SQL任务或另一个数据流来完成操作(在OLEDB命令之后发生的任何事情)

I know this seems like more work to be inserting and reading data multiple times, but this technique performs invariably better than large complicated data flows with row-by-row operations.

我知道这似乎是多次插入和读取数据的工作,但这种技术总是比逐行操作的大型复杂数据流更好。

#1