How to configure an SSIS package which uses a parameterized stored procedure as data source and populates SQL Server destination table.

All Replies

• Saturday, January 26, 2013 8:07 PM

  Moderator

   

   
  

  0

  Please review the following post which outlines just what you are asking. 

  http://sqlsafety.blogspot.com/2013/01/t-sql-parameters-in-ssis-execute-sql.html

  Hope this helps

  ---

  David Dye My Blog

  • Reply
  • Quote

   
• Saturday, January 26, 2013 9:08 PM

   

   
  

  0

  This works fine if we hard code the value. But in my my query the two parameters should be assigned dynamically.

  For example i have 2 parameters,   1. Employee_ID and 2. Department_ID. Lets say there are 6 employees and 15 departments. One employee can be assigned to many departments. That is the trick and i have to build a package which can dynamically pick the employees and check for their departments in the query.

  My query looks like EXEC MyProcedure   @Param1, @Param2                               

  Param1 one should take all the employees and Param2 should take all the values of departments.

  Finally the result set from the SP should be pushed to a SQL Server Destination.

  
  

  • Edited by Sandyrp Saturday, January 26, 2013 9:38 PM
  •  
  • Reply
  • Quote

   
• Saturday, January 26, 2013 9:43 PM

  Moderator

   

   
  

  0

  Here is a post that shows how to dynamically define your statement using variables:

  http://sqlsafety.blogspot.com/2013/01/ssis-replace-dynamic-sql-with-variables.html

  ---

  David Dye My Blog

  • Marked As Answer by Eileen ZhaoMicrosoft Contingent Staff, Moderator Friday, February 01, 2013 2:29 AM
  •  
  • Reply
  • Quote