Calling the SP on OLEDB Source in data flow task

 OLEDB source is used to pull the data from the database using table or view or we can write the sql script to pull the data.

Here we will see how we can call the parameterized SP to pull the data and loading that data into the destination.

Below is my sp

alter PROCEDURE [dbo].[P_get_sales]     @SalesOrderNumber varchar(20) AS /* exec P_get_sales @SalesOrderNumber=SO74958 */ BEGIN  Select  SalesOrderNumber  ,SalesAmount    ,UnitPrice            ,ExtendedAmount          ,TaxAmt from sales where SalesOrderNumber=@SalesOrderNumber END;

Now taking Data flow task and taking Source as OLEDB source.

Creating the connection. 

 And selecting the Data Access mode as SQ Command and writing the code to execute the SP.

Keep in mind we need to provide the input parameter name. Now we need to click on the parameters button and need to configure the 

  

Here we need to provide the input parameter name and assign the variable value. Based on the SP parameter we need to select param Direction (Input , Output or Input output).

Click ok.

Click on the column we will get the list of column.

    

We can select the list of column.

Now taking the multicast to see the result.

 

Package is ready to run.

Running  sp in db 

Running the package. 

Getting the same result.