August 29, 2011 at 3:40 am
hi..
I have one table which contains 1 - 10 records ..
and i have one SP like select * from EMP All the 10 records are retrieved...
what i want is if i execute the SP for First time in Execute sql task
it should return the value 1 and next time if execute it should return 2
similarly up to 10 ... after executing this it should stop the process
while executing the results of the SP should assign to the variable which
is used for further tasks ...
whether it is possible for using a variable Globally for all the packages ..
August 29, 2011 at 7:26 am
Your stored procedure should have a parameter that will help it select the proper row. Then every time you execute, feed it the proper input parameter to get the output you want.
September 3, 2011 at 2:19 pm
vinayak.v (8/29/2011)
what i want is if i execute the SP for First time in Execute sql taskit should return the value 1 and next time if execute it should return 2
similarly up to 10 ... after executing this it should stop the process
while executing the results of the SP should assign to the variable which
is used for further tasks ...
if you only want to track the number of times the procedure is called from a single package, then you can add...
1. an SSIS variable (starting value = 0)
2. an input parameter to the procedure
3. an output parameter to the procedure
when you make the sp call from an execute sql task in the SSIS package, you will pass in the SSIS variable as the input and output parameter to the stored procedure. In the stored procedure, set the output parameter to 1 + the value of the input parameter. this will increment the SSIS variable by 1 each time the execute sql task is executed.
if you want to track the number of times the procedure is called from multiple packages, then you'll use a global variable (see below) and simply add step in the procedure to update that value each time it is called.
vinayak.v (8/29/2011)
whether it is possible for using a variable Globally for all the packages ..
the easiest way to implement a global variable in SSIS such that all packages can access (read/write) the value is to store it in a sql server table. Denali changes the framework a bit with the introduction of project variables...but if you're using 2005 or 2008r2, then you're stuck with a sql server table to hold the value.
Data Enthusiast | @SQLbyoBI | www.opifexsolutions.com
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply