Why DataTable in SSIS script task loses data?

22 Views Asked by At

I tried to make this SSIS package as below:

  1. Create an Object type variable var and an Execute SQL Task, inside the component make proper configs so a table with some rows are filled into var.
  2. Create a Script Task and connect it after Execute SQL Task, set var as 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.

  1. 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?

0

There are 0 best solutions below