SIVANANDA REDDY GANGIREDDY: 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

CREATE TABLE Tbl_ProductMaster

(PRODID INT NOT NULL IDENTITY PRIMARY KEY,

PRODNAME VARCHAR(50) NOT NULL,

PRODCATEGORY VARCHAR(50) NOT NULL);

-- 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

CREATE PROCEDURE sproc_FindProduct

(

@var_ProductId INT = 0

)

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 isvar_ProductId