Solved
SSIS, how to take a field from source query and move to a variable for later us
Posted on 2013-10-31
I have a simple task but can't figure out the mechanics.
I am successfully moving data from point A to point B, using a Sql command from variable, this works great.
But now I want to take one of the columns from this query and populate another variable so I can use that in another subsequent data flow.
So how do I do this ? Copy column didn't work, and Derived Column looked promising, but that didn't work as I thought it might either.
My source query returns 1 row, and I want to take the value of one of the columns and populate a variable for later use.
Here's the data I'm trying to move
HDR
Detail
Detail Child record
Trailer
I think the issue is I don't understand how SSIS works . . .
In oracle, I would write a cursor for HDR, maybe it pulls 5 records.
So I fetch first record, insert it to the dest table HDR in a different d.b.
then, while holding this record,
I pull all related detail records that have the Header.UniqueKey as FK
while processing each detail record, I similarly look for children of the detail
after all details are inserted, I'll then use the header in context and get the trailer
then fetch the next header record and begin the above sequence again.
So how do I accomplish in SSIS ?