Need to add both SSIS variable and rows to result

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

SSIS and Import/Export (2012)

Need to add both SSIS variable and rows to result

Author

Topic

stonebreaker
Yak Posting Veteran

92 Posts

Posted - 2014-01-24 : 11:13:04

I am new to SSIS. I need to convert the following code to a result set that I can then pass to a ForEach loop container:


DECLARE 
	@LoadDt	DATETIME;

SELECT @LoadDt = GETDATE();

SELECT @LoadDt, Branch__Number
FROM [dbo].[BranchMaster]
WHERE [LoadToDataStore] = 1;

What I have done so far is create an Execute SQL task that gets the date variable. I have created an Execute SQL task that returns the branch numbers I want. I need to return the branch numbers and the date in such a way that I can pass them to a ForEach loop that will call a number of tasks for each branch number, and pass both the branch number and date to the tasks as parameters. For tracking purposes, I want the date value to remain constant during the package run, so I can't get the date each time I loop.

Stonebreaker
The greatest obstacle to discovery is not ignorance - it is the illusion of knowledge.
-Daniel Boorstin

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-27 : 07:42:54

you can use above query itself inside a execute sql task and use it to fill an object Type variable created in SSIS. Make ResultSet option as Full Resultset and in resultset tab map 0 index to variable you created. Then entire resultset gets stored in object variable
Then you can use ForEachLoop with ADO Recordset enuerator to loop through values. just map to above created variable and have two variables created with type date and varchar (or int depending on type of branch_number field) and add them inside ForEachLoop with indexes 0 and 1 to get individual values out during each loop iteration.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources