How to read data from stored procedure & pass input parameters from a variable using SSIS?

Task:?Execute the stored procedure by passing each unique product name as input variable and exporting the data within the directory (“F:\Files”) for each product name with a separate CSV file by tagging the product name as a file name with the following input table and stored procedure.

·???Product_export table which keeps the record of unique product names        

·??Order_name_retrivale is the name of the procedure.         

·??The following snip demonstrates the stored procedure executions with sample input parameter #Chocolate Chip.

Solution:

1.??????Defining four variables in SSIS as below:

a.??????export_path: export path variable with value of F:\Files

b.?????obj_prod: object variable which stores all unique product names.

c.??????p_name:?input parameter

d.?????sql_query: For executing the procedure

2.??Configure expression for sql_query variable as follow:

??? Syntax: “procedure_name’”+user_variable_name+”’”

3. Configuring the object variable obj_prod with execute sql task control flow to fetch and store all unique product names. I make the value for result set as Full result set to store all the unique product names.

4. Use Foreach loop container for defining a repeating control flow to iterate through on unique product names and store the value to the input parameter (p_name).

·???store the value to the input parameter (p_name):

5. configuring the data flow task using sql_query variable and perform mapping:

6. configuring the flat file to change the exported file name with respect to input product name:

7. ?Run the SSIS package and check the final exported result in f:\file directory path:

                      Thank you!