I tried to make this SSIS package as below:
- Create an Object type variable
varand an Execute SQL Task, inside the component make proper configs so a table with some rows are filled intovar. - Create a Script Task and connect it after Execute SQL Task, set
varas read only variable. inside the script add below code:
DataTable tb = new DataTable();
OleDbDataAdapter adapter = new OleDbDataAdapter();
adapter.Fill(tb, Dts.Variables["User::var"].Value);
Dts.TaskResult = (int)ScriptResults.Success;
Place a break point on the last line.
- Copy-paste the previous Script Task and connect it after it.
So the overall progress is generate a dataset -> Read into DataTable -> Read again into DataTable.
Debug result shows in step 2 the row count and dataset is expected, but in Step 3, all data in dataTable is cleared, leaving an empty dataTable with only table structure exists.
What's the reason for this result?