Please forgive me if this is stupid but I've search everywhere and can't seem to find anything on this.
Object of SSIS: I need to go through a collection (from one select statement) and query off of that in order to set a value. For example...given results from query named "A". I then take "A" and for each record, set a column on the table to yes where certain conditions are met.
I have a fairly simple SSIS package. There is an "Execute SQL Task" that feeds a "Foreach Loop Container" that has another "Execute SQL Task" inside of it.
Feeder SQL Task Query SELECT isnull(MonitorAlert,'No'), WEBfeWBPhaseID FROM tbl_WRPhase WHERE (DATEDIFF(day, GETDATE(), ManualAlertDate) > 7) OR (ManualAlertDate IS NULL)
ResultSet: Full result set
On the Result Set tab: Result Name: 0 Variable Name: User:: PhasesToCheck of type Object
Foreach Loop Container Collection Tab Enumerator: Foreach ADO Enumerator ADO object source variable: User:: PhasesToCheck Rows in the first table checked
Variable Mappings Tab Variable: User::MonitorAlert Index: 0 Variable: User::WEBfeWBPhaseID Index: 1
Inner SQL Task In a query within this, how do I reference the User::WEBfeWBPhaseID variable? I've tried [User::WEBfeWBPhaseID] and @WEBfeWBPhaseID but both don't seem to work. Do I need to set the parameter mappings? I'm so confused if I've even done it right.
Thank you for any and all help. I hope I've explained it well enough. (Also, if there's a better way of doing it, please dont hesitate to say so)
|