Problem

 You have multiple data sources (csv, txt,..). They have different formats and but they have same business logic in transformation stage. For example: all of your files have the same fomulars :

If ProductCode is Empty, it is converted to (Unspecifiled)

Total Amount = Price x Quantity – Discount Amount

Now, you must implement SSIS packages to load and apply above busuness logic to all your files. Beside, you need also share these business to other member in your team.

Solution

 We can use a template package in SSIS. In this article, I will desribe how to use a template package in SSIS

Create a template package

Step 1: for demonstration, I created 2 example files which are extracted from a retail system

001_RETAIL_A.txt

TranactionID ProductCode ProductName Price Quantity DiscountAmount TransactionDate
R-001-001 R001-BY Bicycle 200M 1000 1 100 2015-10-10 05:06:20
R-001-002 R002-PE Pencile 1M 2 10 0 2015-10-10 06:06:30

002_RETAIL_B.txt

TranactionID ProductCode ProductName Price Quantity DiscountAmount POSNumber TransactionDate
R-001-001 R001-BY Bicycle 200M 1000 1 100 0121412122 2015-10-10 05:06:20
R-001-002 R002-PE Pencile 1M 2 10 0 0121412122 2015-10-10 06:06:30

 

Step 2: Created a database Demo containing table stgFactRetail

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[stgFactRetail]') AND type in (N'U'))
DROP TABLE [dbo].[stgFactRetail]
GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[stgFactRetail]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[stgFactRetail](
	[TranctionID] [nvarchar](100) NULL,
	[ProductCode] [nvarchar](50) NULL,
	[ProductName] [nvarchar](100) NULL,
	[TotalSaleAmount] [numeric](18, 0) NULL,
	[Price] [numeric](18, 0) NULL,
	[Quantity] [int] NULL,
	[DiscountAmount] [numeric](18, 0) NULL,
	[TransactionDate] [datetime] NULL,
	[CreateDate] [datetime] NULL
) ON [PRIMARY]
END
GO

Step 3: Created a package LoadRetailTransaction_Template.dtsx in SSIS with Data Tool 2013. This package is responsible for calculating TotalSalesAmount and converting ProductCode to (Unspecified) if it is empty.

Control flow of the template package
Control flow of the template package

 

Data flow of the template package
Data flow of the template package

Step 4: Run the package to make sure it is run successfully.

Step 5: In the file system, located to the pacakge. Copied it to the DataTransformationItems folder. By default this folder is in C:\Program Files (x86)\Microsoft Visual Studio 12.0\Common7\IDE\PrivateAssemblies\ProjectItems\DataTransformationProject\DataTransformationItems. Depends on your Data Tool version or BIDS tool you installed, this path is different.

To use a template package

  1. Open SSIS project, I want to use this template package.​
  2. In Solution Project, right-click on the project, point to Add and then click Add Item
  3. In the Add New Item – dialog box, click the package that I want to use as a template. The list of templates includes the template package named  LoadRetailTransaction_Template,  I created before.
  4. Create new SSSI from template
    Create new SSSI from template with named  LoadRetailTransactions_Template1.dtsx
  5. Click Add. You can see a SSIS package created from a template SSIS
New SSIS package
New SSIS package
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s