SSIS 脚本任务 - 在循环中读取 RecordSet 对象失败 [英] SSIS Script Task - Reading RecordSet object in a loop is failing

问题描述

我已经收集了一个数据集并使用 RecordSet 目标将其分配给一个对象变量.

I've collected a dataset and assigned it to an object variable using the RecordSet destination.

根据数据集中的记录数，我需要遍历数据集并将记录输出到文件中.例如，如果原始数据集中有 5000 条记录，我需要遍历它 5 次以创建一个包含 1000 条记录的文件，名为 MyFile_1.txt、MyFile_2.txt 等.

Based off of the number of records in the dataset, I need to loop through the dataset and spit out records to a file. For example, if there were 5000 records in the original dataset, I need to loop through it 5 times to create a file containing 1000 records, called MyFile_1.txt, MyFile_2.txt, etc.

在数据流中的脚本组件任务中，我使用 RecordSet 中的列填充输出缓冲区.为此，我创建了一个新的 OleDbAdapter 和 DataTable，然后用对象变量填充它们.

In my script component task in the dataflow, I'm populating the output buffer with the columns from the RecordSet. In doing this, I create a new OleDbAdapter and DataTable, then fill them with the object variable.

这第一次效果很好.但是，第二次，DataAdapter.Fill(DataTable, ObjectVariable) 方法执行没有错误，但 DataTable 中没有行.在调试时，我可以深入查看 Object 变量中是否仍有记录.

This works great the first time. However, the second time, the DataAdapter.Fill(DataTable, ObjectVariable) method executes without error, but the DataTable has no rows in it. While debugging, I can drill down to see that the Object variable still has records in it.

有人有什么想法吗?

推荐答案

这是一个有效的脚本任务.它需要引用 Microsoft ActiveX Data Objects 2.x

Here's a Script Task that works. It requires a reference to Microsoft ActiveX Data Objects 2.x

public void Main()
        {

            OleDbDataAdapter da = new OleDbDataAdapter();
            DataTable dt = new DataTable();

            ADODB.Stream stm = new  ADODB.Stream();
            ADODB.Recordset rs = ((ADODB.Recordset)Dts.Variables["User::Variable"].Value).Clone();
            ADODB.Recordset rsCopy = new ADODB.Recordset();

            rs.Save(stm);
            rsCopy.Open(stm);
            da.Fill(dt, rs);
            dt.Clear();
            da.Fill(dt, rsCopy);
            Dts.TaskResult = (int)ScriptResults.Success;
        }

您可以根据需要多次将数据从流中提取到新的记录集中.

You can pull the data from the stream into a new recordset as many times as you need.

这篇关于SSIS 脚本任务 - 在循环中读取 RecordSet 对象失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！