Using variables in SSIS – Simple example

Working with variables in SSIS: A simple demonstration

Before building the ETL package we need create a sales history table:

CREATE TABLE Product_Sales_History(
SaleID     BIGINT      NOT NULL UNIQUE,
ProductID  VARCHAR(50) NOT NULL,
Total      INT         NOT NULL,
FYEAR      CHAR(4)     NOT NULL);

GO

Insert sample data as below.

INSERT INTO Product_Sales_History (SaleID,ProductID,Total,FYEAR)

VALUES(112889,'ELS2550',1289,2006),

(112811,'ELS2550',1289,2007),

(112812,'ELS2550',1700,2008),

(112813,'ELS2550',2400,2009),

(112814,'ELS2550',2800,2010),

(112815,'ELS2550',2918,2011),

(112816,'ELS2550',3100,2012),

(112817,'ELS2550',3000,2013);

INSERT INTO Product_Sales_History (SaleID,ProductID,Total,FYEAR)
VALUES
(112818,'DSL-14',210,2007),

(112819,'DSL-14',200,2008),

(112820,'DSL-14',189,2009),

(112821,'DSL-14',199,2010),

(112822,'DSL-14',202,2011),

(112823,'DSL-14',209,2012),

(112824,'DSL-14',304,2013);

INSERT INTO Product_Sales_History (SaleID,ProductID,Total,FYEAR)
VALUES
(112825,'AGCL-112',1210,2007),

(112826,'AGCL-112',2200,2008),

(112827,'AGCL-112',2189,2009),

(112828,'AGCL-112',2199,2010),

(112829,'AGCL-112',3202,2011),

(112830,'AGCL-112',3209,2012),

(112831,'AGCL-112',3304,2013);

go

ETL requirement: Design a ETL package to extract data from sales history to a staging table. Data needs to be loaded for the given financial year only. This financial year value will be changing.

Solution:

Create a new package and name it as “Sales_Stage_Load”.

1. Add connection managers to source and destination
2. Create a variable “Year” and assign a default value “2006”.
3. Add an “Execute SQL Task” to clean up the staging table. Map the parameter value with the variable as data needs to be cleaned for the given financial year only.
4. Add a “Data Flow” task.
5. Inside the data flow, add OLEDB source and OLEDB destination.
6. OLEDB Source: Select data from source for the given financial year. Since the value is dynamic the parameter needs to be mapped with the variable “Year”.
7. OLEDB Destination: Load data into staging table.

Implementation:

Create a package and add connection managers to source and destination.

Create a variable “Year” as below.

Add “Execute SQL Task” to the control flow and apply the properties as below.

Connection should point to the destination.

SQL statement is to delete data from stage table for the given year value.

Now MAP parameters in Parameter Mapping tab as below.

Now add a dataflow task and now the control flow looks like below.

Inside the dataflow create an OLEDB source and map it with the connection “Source”.

Data access should be “SQL Command”.

Map the parameter to the user defined parameter “Year” as below.

Map the columns as below.

Add destination and map it with the connection manager “Destination”.

Now the dataflow looks like below.

Now add “Data viewers” which can be helpful in validating the output.

Finally Dataflow looks like as below.

Execute the package:

Execution 1:

Initially the variable “Year” value is: 2006.

Now execute the package and see the output as below.

We can see there’s only one row available for the year 2006.

Execution 2:

Change the variable value to 2010.

Now execute the package and see the output as below.

We can see there are three rows available for the year 2010.

Note: This is just a sample to showcase how variables are being used in SSIS packages.