SSIS Logging Template using BIML


Purpose :

As a BI developer you write lot of repetitive code if there are products in your company which are using same dataset but presented in different way by applying some different transformations to data or some front end changes.

One of things which is common in SSIS Packages is Event Handlers (Logging). There are quite a few event handlers (OnError, OnPreExecute,OnPostExecute to name few).

In this post I will be showing how to write OnPostExecute Event in BIML and use it as a template, so that you can re-use it in other SSIS Packages by just copying and pasting the code.

Prerequisite :

I will be using AdventureWorks2012 and AdventureWorksDW2012 databases, which are available on CodePlex.

Also please create the Event Log table to log events into and stored procedure which will be used to insert records into EventLog Table as shown in below:

–Drop objects if already exist in the database

USE AdventureWorks2012
IF OBJECT_ID (N'dbo.EventLog') IS NOT NULL
DROP TABLE dbo.EventLog;

IF OBJECT_ID (N’dbo.uspLogEvent’) IS NOT NULL
DROP PROCEDURE dbo.[uspLogEvent]
–Create EventLog table

CREATE TABLE [dbo].[EventLog](
[EventID] [int] IDENTITY(1000,1) NOT NULL,
[EventType] [varchar](20) NOT NULL,
[PackageName] [varchar](150) NOT NULL,
[PackageID] [varchar](50) NOT NULL,
[TaskName] [varchar](150) NULL,
[EventCode] [int] NULL,
[EventDescription] [varchar](max) NULL,
[PackageDuration] [int] NULL,
[ContainerDuration] [int] NULL,
[InsertRows] [int] NULL,
[UpdateRows] [int] NULL,
[TotalRows] [int] NULL,
[DateStamp] [datetime] NULL,
[HostName] [varchar](50) NULL
)

–Create stored procedure to insert records into Event Log Table:
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[uspLogEvent]
@EventType varchar (20)
, @PackageName varchar(150)
, @PackageID [uniqueidentifier]
, @TaskName varchar(150)
, @InsertRowCount [int]
, @UpdateRowCount [int]
, @TotalRows [int]
AS

INSERT INTO EventLog (
[EventType]
,[PackageName]
,[PackageId]
,[TaskName]
,[InsertRows]
,[UpdateRows]
,[TotalRows]
,DateStamp
,HostName
)
VALUES(
@EventType
, @PackageName
, @PackageID
, @TaskName
, @InsertRowCount
, @UpdateRowCount
,@TotalRows
,GETDATE()
,@@SERVERNAME)

Solution :

First we will create the package and package level variables as shown in the Figure 1:

EventLoggingFigure_1

Figure 1 : I created a new package 001_Package and 3 package level variables.

I will write BIML Script to log OnPostExecute event as showin in the figure 2:

2015-06-08 15_44_27-BIML - Microsoft Visual Studio (Administrator)

Figure 2 : This show an execute SQL Task is created in Event Handler which executes uspLogEvent stored procedure;

Renamed the .BIML files to EventHandler.biml as shown in the figure 3:

2015-06-08 15_48_50-BIML - Microsoft Visual Studio (Administrator)

Figure 3: Renamed the BimlScript.biml file to EventHandlers.biml.

Now we will write BIML code to create two new packages and copy and paste the event handlers from EventHandlers.biml in a new BIML Script and generate the new packages:

Steps:

1. Add a new BIML file BimlScript.biml.

2. Added two new packages as shown in figure 4:

2015-06-09 14_30_48-BIML - Microsoft Visual Studio (Administrator)

Figure 4 : Creating 2 packages using BIML:

3. Now copy the events code as highlighted in EventHandlers.biml file into BimlScript.biml as shown in Figure 5.

2015-06-09 15_06_46-BIML - Microsoft Visual Studio (Administrator)4

Figure 5: Copying Events code from a template to different packages. Reusing the same Events Handlers code in BIML.

4. Right the BimlScript.biml and click on generate SSIS Packages, this will generate 2 new pacakges 001_Package and 002_Package with OnPostExecute Event Handlers as shown in figure 6

2015-06-09 15_15_19-BIML - Microsoft Visual Studio (Administrator)

 

Figure 6: Shows OnPostExecutre Event in both the packages after the packages were generated using BIML:

Conculsion

In this post we saw how easy it is to copy and paste the repetitive code using BIML, even better create a template and then copy and paste in new packages and off you go. In future post I will show another easy way of doing the same task by calling a BIML file.