November 19, 2017 at 2:18 am
hi, the issue is like this
i want to loop over set of unknown values for that i've:
1. created an 'Execute SQLtask' and set the 'Resultset' property to 'Full' and
2. created an object type variable and assigned it at the mapping property
3. set the enumerator to be 'Foreach ADO Enumerator' with the 'ADO Object Source' to be the mapped variable
(i want to have an unknown number of iterations based on distinct values which will be retrieved by the SQL statement)
the 'Data Flow' task retrieves data with that variable as an input successfully BUT i want to add that current iteration's variable value as a derived column to the data retrieved but the derived column doesn't get that variable (since it's an object)
How can I assign that value as a derived column?
regards,
Shay
November 19, 2017 at 7:32 am
managed...
before the 'Data Flow Task' i've created a 'Script Task' which assigned the object's variable to a string and passed it on:
(Dts.Variables["NewStringVar"].Value = string.Format("{0}", Dts.Variables["ObjectIterarionsVar"].Value.ToString());
then, i could assign that new variable ("NewStringVar") to a new derived column .
Shay
November 19, 2017 at 1:28 pm
This is possible and does not require any scripting, just a technique known as 'recordset shredding'. Take a read here, if you'd like to know more.
It assigns the values to a package variable for each loop iteration, and, of course, you can assign these variables to derived columns in a data flow.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply