Using variables inside Execute SQL task query

Question

I am a complete beginner in terms of SSIS packages.

I really want to execute a stored procedure that takes in parameters with different values at each iteration of the foreach loops. So I'm wondering if anyone can give me an example (VERY VERY VERY basic example) on how I can use variables as values inside an Execute SQL Task like this:

UPDATE tbName   SET  c1 = Var1,  C2 = Var2 etc... 

OR

@bDate = VarDate1 
@eDate = VarDate2 

where Var2, VarDate1, VarDate2 are variables in BIDS

Answer 1

First you need to create the stored procedure on the SQL Server end. This is done with a statement like this. After this has been ran a new stored procedure object called "yourProcedure" will exist in the database.

CREATE PROCEDURE yourProcedure
   @pKeyVar int,                 /* declare variables to be passed here */
   @pFirstVar varchar(40), 
   @pSecondVar int,
   @pThirdVar decimal(18,2)
AS 
BEGIN

   Update yourTable Set          /* place what will be done here */
       Col1 = @pFirstVar, 
       Col2 = @pSecondVar, 
       Col3 = @pThirdVar 
   WHERE KeyColumn = @pKeyVar

END
GO

Once the stored procedure has been created you can call it like this:

exec dbo.yourProcedure 12345, 'string value', 2, 2.05

There are a couple ways to call it from SSIS, but the most common is the Execute SQL Task. You can map the parameters that will be passed to the variables that hold the content and put the task inside your looping logic.

Here is a decent walkthrough of the Execute SQL Task.

Pay close attention to the section on mapping parameters to variables etc. The version of SSIS is 2005 but the concepts are all the same.

---

Update after comment.

In order to loop through a recordset and obtain values to pass back to the proc you can follow the information as provided in this article.