Hi All I have to create an SSIS package which runs Stored Procedures but the Stored Procedures get the Input parameters from a query. So i want to know how to call the parameters in execute sql task. Query: Select @var1 = col1 @var2 = col2 From Table1 The stored procedures which i add in the Execute Sql Task in SSIS is EXEC sp_1 @var1, @var2 EXEC sp_2 @var1, @var2 I hope i was clear, will explain in detail if i am not clear. @var1 and @var2 are datetime format. Thanks

Take 2 execute sql tasks. First will use the query: Select col1,col2 from table with result set as single row and save the values in string variables by using the Result Set option in the left pane Select result name as 0 and select the variable to capture Col1. Similarly capture the value of Col2 in another variable. Use second execute sql task to execute the SP by passing the variables (used in frst execute sql task) as parameters to the SP. Query in 2nd execute sql task: Exec SPName ?,?. Then go to parameter option and select the appropriate variables with direction as input; datatype as varchar; parameter name as 0 and 1.Nitesh Rai- Please mark the post as answered if it answers your question

You already populate the parameters in SQL...? Declare the variables @var say Declare @var1 as datetime...etc , and then simply use Execute SQL Task with the code above as one unit.Arthur My Blog

hi nitesh the first execute sql task fails as i mentioned the col1 value is datetime and you asked me to declare the @var1 variable in ssis as string. [Execute SQL Task] Error: An error occurred while assigning a value to variable "start": "The type of the value being assigned to variable "User::start" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object. ". Thanks

hi arthurz if i follow your method i need to add the same code in 10 execute sql task as i have 10 stored procedures to be called with same parameters. thanks

No - you can place multiple statements in one Execute SQL Task. For example: DECLARE @var1 DATETIME SELECT @var1 = col1 FROM table EXEC sp_1 @var1 EXEC sp_2 @var1 EXEC sp_3 @var1 Talk to me now on

Thanks todd.