Wednesday, 24 April 2013

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

7 comments:

  1. Hello Joydeep, I have one issue with related to SSIS OLE DB source connectivity. I want to Pass dynamic Server Name to OLE Db jet 4.0 connection for Data Migration from Execute SQL Task to be generate the file Path.

    ReplyDelete
    Replies
    1. Hi Vivek,

      Create 4 variables and assign those variables as parameters and while excution time just pass the parameters for dynamic

      Delete
    2. Hello Prasad, I got the solution it is working now.
      as connection string "Data Source=" + @[User::vSFilePath] + ";Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Password=;"
      and the ServerName as @[User::vFileName] in which the statically define the databasepath with server name variable

      issue is if I am passing the @[User::vSFilePath] as ServerName. it is giving error.
      How i can use the same variable path with connection string and with Server Name.
      but It is working properly.

      Delete
  2. Thank you for sharing, i think it's really helpful for beginners in SSIS

    ReplyDelete
  3. This comment has been removed by a blog administrator.

    ReplyDelete
  4. Does this only work in SQL 2012, I'm trying it in 2008 and can't get it to recognize the columns for output to the the next component in the DFT?

    ReplyDelete
  5. Thanks a lot for sharing with a clean doc. Really it's helpful.

    ReplyDelete