This was a weird and frustrating one. I’ve got a package that has a ForLoop in it and inside the ForLoop I’m loading records that need to use a Lookup. I thought what better time to use a CascheTransform than now! The only issue was that I needed to use the Lookup from inside a Script Component in my Data Flow (not a Script Task).
Now, knowing little about c# I figured I could simply load a new ADO RecordSet into a package variable and use that in my Script Component. so I created a OleDb source and a RecordSet destination:
Then populated a variable (User::Result) of type Object with my RecordSet for use later in my Script Component
Now later in my Script Component I passed the variable in as a ReadOnly variable and populated a new DataTable using the source variable. And it worked for the first run of the ForLoop and then the underlying variable (User::Result) got set to Null. Turns out the issue was caused by using an OleDb adapter to fill a data table:
DataTable dt = new DataTable();
OleDbDataAdapter adapter = new OleDbDataAdapter();
The above section of code causes the object to be emptied.
The solution to this is to populate the DataTable first, not a RecordSet and pass the DataTable into the object. This avoids having to create the DataTable from the RecordSet each time.
You can use code similar to the below to populate the DataTable:
I hope this can help you too 🙂