This forum post would guide you through the process detailing on how a Stored Procedure can be used as a Source/Target. The Output File created using Stored Procedure can be used for further process the Process Flow. Or in case of Target, any type of file can be stored in the DB using Stored Procedure.
Using Stored Procedure as Source:
Follow these steps to fetch the data from Database using Stored Procedure and for storing it in a file such as .csv format:
- Create a Stored Procedure in Database using the statements as shown below- In the stored procedure created we have dynamically passed the Database query using @SelectQuery as a variable. The File path where the resultset of Stored Procedure will be stored is defined in the Procedure script such as "c:\bcp\Tom.csv" .
- Now create a process flow and add a Put-Context Var just before the Stored procedure. Using this variable we will be over-riding the Query of Stored Procedure Dynamically.
- File Source Activity in the 3rd step of Process Designer will have the same File Path and File Name as defined in the Step1 when Stored Procedure was created.
In this way we will be able to use the Output of Stored Procedure in a File that can be further processed in Process Flow.
Using Stored Procedure as Target:
Follow these steps to fetch the data from any file such as a .csv file using Stored Procedure and can be use to Insert/Updat in the Database.
- In the Process flow, ensure that the file is created at Target Location (Step 4). The File created will be used by Stored Procedure activity to be Inserted/Updated in Database.
- In Put context var, we will be passing the same file path and file name as defined in Target Activity.
- Now create a Stored Procedure in Database using statements as shown below:-
In the stored procedure created above we have dynamically passed the FilePath using @filePath as a variable. The Database Table where the resultset of Stored Procedure will be stored is defined in the Procedure script such as "test.dbo.Persons".
Note: In above case please make sure that the Stored Procedure has exact number of columns that has to be updated when using it as Target.