在SSIS 2012中为Oracle CLOB转换DT_TEXT [英] Transform DT_TEXT in SSIS 2012 for Oracle CLOB

问题描述

我正在使用SSIS2012.我有一个脚本组件，其输出列的类型为DT_TEXT. (它是来自网页的XML.)

I am using SSIS 2012. I have a script component with an output column of type DT_TEXT. (It is XML from a web page.)

我有一个使用OLE DB的本机OLD DB \ Oracle Provider的OLE DB目标，并且该字段的表定义为CLOB.

I have an OLE DB Destination using the Native OLD DB\Oracle Provider for OLE DB, and the table for that field is defined as a CLOB.

这个问题似乎很相似: SSIS脚本组件不允许文本流输出.这似乎给出了答案:

This question seems similar: SSIS Script Component won't allow text Stream Output. And this seems to give an answer: http://www.rad.pasfu.com/index.php?/archives/35-How-to-Fill-NTEXT-Column-from-Script-Component-SSIS.html

在脚本组件中，我将XML作为字符串获取，文档称其最多可容纳2GB.我很难将其分配给我的Output0Buffer(DT_TEXT).任何使用AddBlobData的尝试都会产生错误.

Within the Script Component I am getting my XML as a String, which the docs say can hold up to 2GB. I am having difficulty assigning it to my Output0Buffer (DT_TEXT). Any attempt to use AddBlobData gives an error.

  // Output0Buffer.AddRow();
  // Output0Buffer.forecastXML = forecastXML;
  // Output0Buffer.AddBlobData(forecastXML);
  // Output0Buffer.LargeCol.AddBlobData(forecastXML);
  // Output0Buffer.LargeCol.AddBlobData(System.Text.Encoding.UTF8.GetBytes(forecastXML));

这些是我的一些尝试，但没有一个可以编译.最后一个错误:'Output0Buffer'不包含'LargeCol'的定义，并且找不到扩展方法'LargeCol'接受类型为'Output0Buffer'的第一个参数(您是否缺少using指令或程序集引用? )

Those are some of my attempts, but none of them compile. That last one gives the error: 'Output0Buffer' does not contain a definition for 'LargeCol' and no extension method 'LargeCol' accepting a first argument of type 'Output0Buffer' could be found (are you missing a using directive or an assembly reference?)

如何获取从脚本组件到oracle目标的文本流?

How do I get the text stream from the script component to the oracle destination?

推荐答案

事实证明，

It turns out that SSIS Script Component won't allow text Stream Output did have the answer, but I had to figure out what the fields meant in the answer.

我将解释所有部分，因此很清楚发生了什么事情.

I'll explain all of the parts, so it's clear what is going on.

脚本组件具有一个名为Output 0的输出，带有Output Columns ForecastXML.这些是默认名称，但最后一个除外. ForecastXML的数据类型为文本流[DT_TEXT]

The Script Component has an Output called Output 0, with Output Columns forecastXML. Those are default names except for the last. forecastXML has the data type of Text stream [DT_TEXT]

在脚本本身中，我有一个名为ForecastXML的字符串(是的，名称相同，这令人困惑.)

In the script itself, I have a string called forecastXML (yeah, same name, which makes it confusing.)

在用数据填充字符串ForecastXML之后，我可以使用以下几行将其分配给Output0Buffer:

After filling the string forecastXML with data, I can assign it to the Output0Buffer with the following lines:

String forecastXML = oResult.XmlResult;
Output0Buffer.AddRow();
Output0Buffer.forecastXML.AddBlobData(System.Text.Encoding.UTF8.GetBytes(forecastXML));

第一行适用于所有数据类型.因为我正在写NTEXT，所以需要第二行，而不是直接分配. Output0Buffer.forecastXML指的是我的输出0中定义的NText数据类型.最后一个是我的代码中的字符串.

The first line works for all data types. Because I'm writing to a NTEXT, the second line is needed, rather than a straight assignment. The Output0Buffer.forecastXML refers to the NText data type defined in my Output 0. The last one is my string from the code.

更清楚一点，我应该拥有

To be more clear, rather than creating a string, I should have

Output0Buffer.forecastXML.AddBlobData(System.Text.Encoding.UTF8.GetBytes(oResult.XmlResult));

其中，oResut.XmlResult是获取XML的调用的结果.将其分配给字符串是一个多余的步骤.

where oResut.XmlResult is the result of my call that gets the XML. Assigning it to a string is an extra, unneeded step.

这将转到Oracle CLOB，因此下一步是将输出输出到派生列"并将我的输出ForecastXML转换为(DT_NTEXT)forecastXML. (我怀疑我正在进行一些不必要的类型更改.)

This is going to an Oracle CLOB, so the next step is a to take that output to a Derived Column and cast my output forecastXML as (DT_NTEXT)forecastXML. (I suspect I'm doing some unneeded changes of types.)

然后，将该字段映射到我的OLE DB目标中的CLOB字段.

And then, I map that field to my CLOB field in my OLE DB Destination.

这篇关于在SSIS 2012中为Oracle CLOB转换DT_TEXT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！