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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 SSIS and Import/Export (2012)
 Need to add both SSIS variable and rows to result
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Yak Posting Veteran

92 Posts

Posted - 01/24/2014 :  11:13:04  Show Profile  Reply with Quote
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:



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.

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

Very Important crosS Applying yaK Herder

52326 Posts

Posted - 01/27/2014 :  07:42:54  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.02 seconds. Powered By: Snitz Forums 2000