Using Stored Procedures in SSIS

When you are creating an SSIS package with a data flow, it is a Best Practice to use a stored procedure for the queries. This makes it easy to adjust the queries without having to redeploy the packages.

Here is stored procedure I am using. It is pulling from the Adventureworks2008 database which is free on codeplex.

USE [AdventureWorks2008]
GO
/****** Object:  StoredProcedure [dbo].[Address]    Script Date: 02/16/2010 10:14:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[Address]
@City varchar(20)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
SELECT        Address_1.AddressLine1, Address_1.AddressLine2, Address_1.City, Address_1.PostalCode, Person.StateProvince.StateProvinceCode,
                         Person.StateProvince.CountryRegionCode
FROM            Person.Address AS Address_1 INNER JOIN
                         Person.StateProvince ON Address_1.StateProvinceID = Person.StateProvince.StateProvinceID
WHERE        (Person.StateProvince.CountryRegionCode = N'US') AND
 City IN (SELECT Item FROM dbo.Split (@City, ','))
END

This is a simple stored procedure with one parameter called City. To set this up as a source in an OLE DB source in a data flow we will type in exec and the name of the stored procedure. Notice the question mark after the stored procedure name.This represents the parameter.

 

Then we click on the parameter button to the right in the OLE DB source in the data flow. Type in the parameter name of City in the parameter name column and select a variable you want to use to pass the value in. This can be filled with an Execute SQL task in the control flow, or passed in from a configuration file or table.

 

 

 

 

 

 

 

 

I placed a data viewer after the source and before the terminator destination.

I set the value of the variable to Orlando and ran the package. You should see Orlando in the city column.