SSIS Script Task - Reading RecordSet Object in Loop Fails
I have collected a dataset and assigned it to an object variable using the RecordSet assignment.
Based on the number of records in the dataset, I need to go through the dataset and spit out the records to a file. For example, if the original dataset had 5000 records, I need to execute it 5 times to create a file containing 1000 records called MyFile_1.txt, MyFile_2.txt, etc.
In my task of a script component in a data stream, I fill the output buffer with columns from a RecordSet. With this I create a new OleDbAdapter and DataTable and then populate them with an object variable.
This works great the first time. However, the second time, the DataAdapter.Fill (DataTable, ObjectVariable) method runs without error, but there are no rows in the DataTable. While debugging, I can drill down to see that there are still entries in the Object variable.
Does anyone have any idea?
source to share
This is where the Script Task works. This requires a link 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 data from the stream into a new recordset as many times as you like.
source to share