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.
Create a template package
Step 1: for demonstration, I created 2 example files which are extracted from a retail system
|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|
|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.
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
- Open SSIS project, I want to use this template package.
- In Solution Project, right-click on the project, point to Add and then click Add Item
- 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.
- Click Add. You can see a SSIS package created from a template SSIS