SSIS OLE DB source with Stored Procedure

Introduction

Stored Procedure (SP) is an important factor of very SQL Developer. This article is related to, how to handle the Stored Procedure by SSIS.

Mapping the SP from SSIS OLE DB source

The steps are mentioned bellow.

Step-1 [ Create the Base Table and Stored Procedure ]       

Here the name of the base table is "Tbl_ProductMaster" and by the use of stored procedure called "sproc_FindProduct" we are just going to find the product details by passing the parameter product id.

-- Creat the Base Table

IF OBJECT_ID('Tbl_ProductMaster') IS NOT NULL

   BEGIN

     DROP TABLE Tbl_ProductMaster

   END

GO

CREATE TABLE Tbl_ProductMaster

       (PRODID         INT          NOT NULL IDENTITY PRIMARY KEY,

        PRODNAME       VARCHAR(50)  NOT NULL,

        PRODCATEGORY   VARCHAR(50)  NOT NULL);

GO

-- Inserting Records

INSERT INTO Tbl_ProductMaster

       (PRODNAME, PRODCATEGORY)

VALUES ('Tooth Paste', 'Dental'),

       ('Tooth Brush', 'Dental'),

       ('Saving Brush', 'Cosmetics'),

       ('Saving Gel', 'Cosmetics');

      

-- Creating the Stored procedure

IF OBJECT_ID('sproc_FindProduct') IS NOT NULL

   BEGIN

     DROP PROCEDURE sproc_FindProduct;

   END

GO                          

CREATE PROCEDURE sproc_FindProduct

       (

          @var_ProductId  INT = 0

       )  

AS

BEGIN

   SELECT PRODID, PRODNAME, PRODCATEGORY

    FROM  Tbl_ProductMaster

     WHERE PRODID =  @var_ProductId;

END      

-- T-SQL Testing of procedure

EXECUTE dbo.sproc_FindProduct

        @var_ProductId = 2

       

-- Output Result

PRODID      PRODNAME          PRODCATEGORY

2           Tooth Brush       Dental       

Step-2 [ Control Flow Setup ]                

Drag a Data Flow Task in the Control flow tab.

Step-3 [ Creating a Package level variable ]

Go to the Menu bar àSSIS àVariables

In the variable window click on the add variable.

Name              : var_ProductId

Scope              : Package level

Data type        : Int32

Value               : 2

Step-4 [ Editing the OLE DB Source ]

Right click the Data Flow Task and select the Edit, it will open the Data Flow tab. Right click the OLE DB Source and open the OLE DB Source Editor.

In OLE DB Source Editor Select the OLE DB Connection manages. In our case it is "JOYDEE-PC.TEST_DB". In the Data Access mode select the SQL Command. In the SQL Command text writes down the following code.

EXECUTE dbo.sproc_FindProduct ?

The "spproc_DindProduct" is our stored procedure name and the "?" is used for passing parameters.

Step-5 [ Stings the parameters for our Stored Procedure ]

In the OLE DB Source editor to set the parameters just click on the "Parameters …" button. The Set Query Parameter dialog appears.

As we have single parameters in our SP, so we are using single "?" in SQL command with the name of our stored procedure hence the Set Query Parameter dialog appears with Parameter 0.

Then in the valuables section we select the User::var_ProductId the variable that we make before and in Parameters we provide the Stored procedure Parameter name. In our case it is @var_ProductId.

EXECUTE [dbo].[sproc_FindProduct]   ?

Important Note

A lot of developer discuss about this error mentioned bellow




The SQL command requires a parameter named "@ParameterName", which is not found in the parameter mapping.

This error came due to the developer not perfectly provide the value in the Set Query Parameter value window. There are two columns in Set Query Parameter value dialog box mentioning bellow.

Parameters	Variables
This must be the stored procedure parameter. In our case it is @var_ProductId	This must be the package level variable that we created earlier. In our case it is var_ProductId

Step-6 [ Flat file Destination and run the Package]

Now drag a flat file destination and drag the OLE DB Source data flow path in it. Then configure the flat file destination.

Here I assume that you know to configure the flat file destination. If you need guidance please follow my previous post.

The final output in text file

Hope you like it.

Posted by: MR. JOYDEEP DAS