SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
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
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

stonebreaker
Yak Posting Veteran

USA
85 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:


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

India
52249 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
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New 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.05 seconds. Powered By: Snitz Forums 2000