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
