ssis etl framework by tracking guids in event handlers version 1[1]

www.charles-chen.com

SSIS ETL Framework by Tracking GUIDs in Event Handlers

SSIS ETL FRAMEWORK BY TRACKING GUIDS IN EVENT HANDLERS

1 | P a g e

I. Overview ......................................................................................................................................... 2

II. Minimum Software Requirements ................................................................................................... 3

III. Installation....................................................................................................................................... 4

IV. Functionalities ................................................................................................................................. 6

a. Logging to SQL tables ................................................................................................................... 6

b. Sending Email Notifications .......................................................................................................... 6

c. Writing variables value to text files for troubleshooting ............................................................... 7

V. Demonstrations ............................................................................................................................... 9

a. Sol_1_TGIEH_SSIS_ETL_Framework_Template ........................................................................... 10

b. Sol_2_AWDataWarehouseRefresh_Using_TGIEH_SEF_V1 .......................................................... 11

c. Sol_2_AWDataWarehouseRefresh_Using_TGIEH_SEF_V2 .......................................................... 12

VI. SSIS Development Using the SSIS ETL Framework .......................................................................... 15

VII. Further Developments ................................................................................................................... 17

a. ETL Logging Reporting Creation .................................................................................................. 17

b. Email Notification Customization ............................................................................................... 17

c. Debug Output Text Files Customization...................................................................................... 18

VIII. Suggestions ................................................................................................................................... 19


2 | P a g e

I. Overview

For .NET development, Microsoft Enterprise Library that can be used for logging and many other

functions. However, there are no SSIS Framework for logging and email notification. Even

though SSIS comes with logging functionality, significant effort is required to configure it. For

this reason, I created the SSIS ETL framework with a .NET DLL – SSIS ETL Framework by Tracking

GUIDs in Event Handlers.

A C# script task exists within the four event handlers (OnPreExecute, OnPostExecute, OnError,

and OnVariableValueChanged) of the SSIS ETL Framework package. In the script tasks, it writes

the variables to the log files for troubleshooting and passes the GUIDs to the DLL.

Every object, Task and Package, in SSIS package has a GUID, like below, and it will pass its GUID

to the Event Handlers whenever it’s running. Based on this, I created the DLL to track GUIDs

(that pass from the SSIS Package OnPreExecute and OnPostExecute Event Handlers), identified

the relationship for Job -> Package -> Task, and saved the start and stop time to SQL tables.

While saving the information to SQL Server, the DLL also sends out email notifications per

configuration. Besides tracking run time information, the DLL also track errors from OnError

Event Handler and variables changed from OnVariableValueChanged Event Handler.

SourceParentGUID = {DC7801F1-5F0E-4667-AA77-FE6A60D2067C}

ExecutionInstanceGUID = {9822F74D-07CD-4319-99AE-5DFE89FCB3D1}

ParentContainerGUID = {11BF118C-FCBB-4006-82D3-B77238FE8A6E}

PackageID = {DC7801F1-5F0E-4667-AA77-FE6A60D2067C}

SourceID = {97160538-AE6C-4D29-83A9-D7A1E9B5C305}

So how do we ensure the uniqueness of GUID after copying objects –Tasks or Packages? I always

use BIDS Helper to reset GUIDs (see Figure 1 - BIDS Helper – Reset GUIDs) before deploying the

packages. Of course, BIDS Helper can do more than reset GUIDS. If you’re a SSIS or BI

developer, I highly recommend evaluating this utility and use it.

Figure 1 - BIDS Helper – Reset GUIDs


3 | P a g e

II. Minimum Software Requirements

This framework was created for SSIS developers. The following are the minimum software requirements

to use the framework:

1) Operating System, Windows 7, Windows Vista, Windows 2008 or Windows 2008 R2

2) SQL Server Business Intelligence Development 2008

3) SQL Server 2008 or SQL Server 2008 R2 with AdventureWorks and AdvWorksDWX databases

4) SQL Server 2005/2008 AWDataWarehouseRefresh Package Sample

(http://msdn.microsoft.com/en-us/library/ms160706(v=sql.90).aspx)

(http://sqlserversamples.codeplex.com)

5) Excel Add-in SQL Utilities

Utility available for download at (http://www.charles-chen.com)

6) Database Mail Setup

If you want to setup Database Mail with Gmail account, Figure 2 – Database Mail

Configuration Sample is a sample setup for your reference.

Figure 2 – Database Mail Configuration Sample

http://msdn.microsoft.com/en-us/library/ms160706(v=sql.90).aspx)

http://sqlserversamples.codeplex.com/

http://www.charles-chen.com/


4 | P a g e

III. Installation

Follow the steps below for installation:

1) Download SSIS ETL Framework by Tracking GUIDs in Event Handler from

www.charles-chen.com

2) Save the download to a folder, unzip it to C:\ (See Figure Figure 3 –

TGIEH_SSIS_ETL_Framework Folder Structure).

Figure 3 – TGIEH_SSIS_ETL_Framework Folder Structure

3) Restore TGIEH_SSIS_ETL_Framework , AdventureWorks and AdvWorksDWX databases from

C:\TGIEH_SSIS_ETL_Framework\Backup

4) Run C:\TGIEH_SSIS_ETL_Framework\Commands\SetSysVar.cmd to setup System

Environment variable TGIEH_SSIS_ETL_Framework and define the path and file name for

SSIS XML configuration file

5) Run C:\TGIEH_SSIS_ETL_Framework\Commands\DeployDLL_Laptop.cmd to deploy the DLL

If your computer is NOT 64 bit, then you will need to modify the command.

6) Review SSIS XML configuration file

C:\TGIEH_SSIS_ETL_Framework\TGIEH_SSIS_ETL_Framework_Solution\TGIEH_SSIS_ETL_Fra

http://www.charles-chen.com/


5 | P a g e

mework_Solution.dtsConfig and change the Data Source if the SQL Server installed is not

default instance

7) Review SSIS configuration table dbo.TGIEH_SEF_Configurations in

TGIEH_SSIS_ETL_Framework and change the following:

a. Email Recipients (Please change it to your Email.)

\Package.Variables[User::ETL_Db_Mail_Send_Mail_Package_Recipients].Propert

ies[Value]

\Package.Variables[User::ETL_Db_Mail_Send_Mail_Job_Recipients].Properties[

Value]

b. Database Mail Profile Name (if Database Profile name is not Database Mail)

\Package.Variables[User::ETL_Db_Mail_Send_Mail_Package_Profile_Name].Pro

perties[Value]

\Package.Variables[User::ETL_Db_Mail_Send_Mail_Job_Profile_Name].Properti

es[Value]

c. SQL Server Name (if SQL Server installed is not default instance)

\Package.Connections[localhost.master].Properties[ConnectionString]

\Package.Connections[localhost.AdvWorksDWX].Properties[ConnectionString]

\Package.Connections[localhost.AdventureWorks].Properties[ConnectionString]


6 | P a g e

IV. Functionalities

The SSIS ETL Framework provides three major functions: logging to SQL tables, sending Email

Notifications, and writing variables value to text files for troubleshooting. The following are the

explanations:

a. Logging to SQL tables

Logging SSIS packages run information into five tables: ETL_Job, ETL_Package,

ETL_Rowcount, ETL_Task, ETL_Error, see Figure 4 – TGIEH_SSIS_ETL_Framework Database

Diagram. Therefore, you no longer need to use SSIS Logging and dbo.sysssislog table.

ETL_Rowcount

PK,FK1 Job_IdPK,FK1 Package_IdPK Object_Name

RowCount

ETL_Error

PK,FK1 Job_IdPK,FK1 Package_IdPK,FK1 Task_Id

Error_Code Error_Description

ETL_Task

PK,FK1 Job_IdPK,FK1 Package_IdPK Task_Id

Task_Sequence Execution_Id Source_Name SourceDescription Source_Id Source_Parent_Id Start_Date End_Date Task_Status Duration

ETL_Job

PK Job_Id

Machine_Name User_Name Package_Name Job_Description System_PackageID Execution_Id Start_Date End_Date Job_Status Duration

ETL_Package

PK,FK1 Job_IdPK Package_Id

Package_Name System_PackageID Execution_Id Start_Date End_Date Package_Status Duration

Figure 4 – TGIEH_SSIS_ETL_Framework Database Diagram

Sample output files by using Excel Add-in SQL Utilities:

C:\TGIEH_SSIS_ETL_Framework\Outputs\Sol_1_TGIEH_SSIS_ETL_Framework_Templ

ate.xlsx

C:\TGIEH_SSIS_ETL_Framework\Outputs\Sol_2_AWDataWarehouseRefresh_Using_

TGIEH_SEF_V1.xlsx


TGIEH_SEF_V2.xlsx

b. Sending Email Notifications


7 | P a g e

The DLL calls the stored procedure dbo.usp_ETL_SendMail and sends out email

notifications, see Figure 5 – Email Notifications Sample. The stored procedure uses

Database Mail of SQL Server to send out email but you need to configure Database Mail

Profile Name, Email Recipients, and the Event Flags in SSIS configuration table

dbo.TGIEH_SEF_Configurations of database TGIEH_SSIS_ETL_Framework.

Figure 5 – Email Notifications Sample

Database Mail Profiles, Email Recipients, and the Event Flags:

ETL_Db_Mail_Send_Mail_Job_Profile_Name <Database Mail Profile Name for Job>

ETL_Db_Mail_Send_Mail_Job_Recipients <Database Mail Recipient for Job>

ETL_Db_Mail_Send_Mail_Package_Profile_Name <Database Mail Profile Name Package Job>

ETL_Db_Mail_Send_Mail_Package_Recipients <Database Mail Recipient for Package>

ETL_Db_Mail_Send_Mail_Event_Job_Flag Description

0 Don’t Send

1 Before Only

2 After Only

3 Before and After

ETL_Db_Mail_Send_Mail_Event_Job_Flag Description

1 Success Only

2 Failure Only

3 Completion (Success or Failure)

ETL_Db_Mail_Send_Mail_Event_Package_Flag Description

1 Success

2 Failure

3 Completion (Success or Failure)

c. Writing variables value to text files for troubleshooting


8 | P a g e

If the flag ETL_Write_Text_Log is set to True and the file name ETL_Log_File is assigned with

path and file name, the C# script task in the Event Handlers writes variables information to

the text files for troubleshooting. The output file name format is (File name of ETL_Log_File

without file extention)(Package name)(Task name)(Event handler name)(Run time).txt, see

Figure 6 – Output Files Name Sample and Figure 7 – Output Sample.

Figure 6 – Output Files Name Sample

Figure 7 – Output Sample

Full Output Files Sample:

C:\TGIEH_SSIS_ETL_Framework\Outputs\Sol_1_TGIEH_SSIS_ETL_Framework_Templ

ate_Log.zip


TGIEH_SEF_V1_Log.zip


TGIEH_SEF_V2_Log.zip


9 | P a g e

V. Demonstrations

The first time I opened AWDataWarehouseRefresh package, package sample of Integration Services

from SQL Server 2005/2008, my monitor was not big enough to view it all (see Figure 8 –

AWDWRefresh.dtsx Package)!

I am going to use the SSIS framework with AWDataWarehouseRefresh package to capture the tasks run

sequence and group the tasks by function in a package so it’s easy to view.

Figure 8 – AWDWRefresh.dtsx Package

Before you run the demonstration, you need to follow the Installation instruction and install it. Since

you’re a SSIS developer, I am assuming that you’re familiar with SSIS Package Configurations, Variables,

Connections, and Properties setup and going to skip the setup. If you have any questions, please send

me an email.

In addition, I am going to assume you’ve installed Excel Add-in SQL Utilities as outlined in the minimum

software requirements. Ensure that you are familiar with this utility as you will often use it to output

the results into worksheets (See Figure 9 – Use Excel add-in SQL utilities to output the SQL tables). If you

want to reset the tables, you can run C:\TGIEH_SSIS_ETL_Framework\SQL\Create Tables.sql.


10 | P a g e

Figure 9 – Use Excel add-in SQL utilities to output the SQL tables

a. Sol_1_TGIEH_SSIS_ETL_Framework_Template

The first demo shows the SSIS framework with the package having only one task - Execute

SQL Task. Run the SQL script Create Tables.sql to reset tables, execute the package, and use

Excel Add-in SQL Utilities to output the tables to worksheets.

Change the SQL Statement to SELECT * FROM SYS.TABLESxx in the Execute SQL Task in the

package and run the test one more time.

What happened? I am going to leave this to you to find out yourself. Hint: Check the error in ETL_Error table.

SSIS Codes:

SSIS Codes Folder C:\TGIEH_SSIS_ETL_Framework\TGIEH_SSIS_ETL_Framework_Solution\Sol_1_TGIEH_SSIS_ETL_Framework_Template

SSIS Project Sol_1_TGIEH_SSIS_ETL_Framework_Template.dtproj

SSIS Package TGIEH_SEF_Template_Pkg.dtsx

Output Sample:

Output Sample Folder C:\TGIEH_SSIS_ETL_Framework\Outputs


11 | P a g e

SQL Tables Output Sample Sol_1_TGIEH_SSIS_ETL_Framework_Template.xlsx

Text Files Outpue Sample Sol_1_TGIEH_SSIS_ETL_Framework_Template_Log.zip

b. Sol_2_AWDataWarehouseRefresh_Using_TGIEH_SEF_V1

The second demo is to show you the SSIS framework and the package AWDWRefresh.

Similar to Demo one, run the SQL script Create Tables.sql to reset tables, execute the

package, and use Excel Add-in SQL Utilities to output the tables to worksheets. Review SQL

table dbo.ETL_Task and group the similar tasks together (see below AWDWRefresh Tasks

Grouping).

AWDWRefresh Tasks Grouping:

Task Sequence Source Name Source Description Grouping

1 Prepare New Database Sequence Container PreLoad

2 Drop temp tables Foreach Loop Container TempTables

3 Drop database Execute SQL Task PreLoad

4 Execute SQL Task Execute SQL Task TempTables

5 Create database Execute SQL Task PreLoad

6 Set database options Execute SQL Task PreLoad

7 Create udfMinimumDate function Execute SQL Task PreLoad

8 Create tables Foreach Loop Container PreLoad

9 Execute SQL Task Execute SQL Task PreLoad

10 Create temp tables Foreach Loop Container TempTables

11 Execute SQL Task Execute SQL Task TempTables

12 Bulk Insert tempSpecialOffer-ForeignData Bulk Insert Task TempTables

13 Bulk Insert tempProduct-ForeignNames Bulk Insert Task TempTables

14 Bulk Insert tempCustomer-YearlyIncome Bulk Insert Task TempTables

15 Bulk Insert tempIndividual-ForeignData Bulk Insert Task TempTables

16 Bulk Insert tempProductSubcategory-ForeignNames Bulk Insert Task TempTables

17 Bulk Insert tempStore-MinPayment Bulk Insert Task TempTables

18 Bulk Insert tempProductCategory-ForeignNames Bulk Insert Task TempTables

19 Bulk Insert AdventureWorksDWBuildVersion Bulk Insert Task LoadDim

20 Bulk Insert DimTime Bulk Insert Task LoadDim

21 Bulk Insert DimOrganization Bulk Insert Task LoadDim

22 Bulk Insert DimScenario Bulk Insert Task LoadDim

23 Bulk Insert DimAccount Bulk Insert Task LoadDim

24 Data Flow Task - DimGeography Data Flow Task LoadDim

25 Data Flow Task - DimDepartmentGroup Data Flow Task LoadDim

26 Data Flow Task - DimProduct Data Flow Task LoadDim

27 Data Flow Task - DimPromotion Data Flow Task LoadDim


12 | P a g e

28 Data Flow Task - DimEmployee Data Flow Task LoadDim

29 Data Flow Task - DimSalesTerritory 1 Data Flow Task LoadDim

30 Data Flow Task - FactInternetSalesReason Data Flow Task LoadFact

31 Data Flow Task - DimCurrency Data Flow Task LoadDim

32 Bulk Insert FactFinance Bulk Insert Task LoadFact

33 Data Flow Task - DimSalesReason Data Flow Task LoadDim

34 Data Flow Task - DimReseller Data Flow Task LoadDim

35 Data Flow Task - DimCustomer Data Flow Task LoadDim

36 Data Flow Task - FactSalesQuota Data Flow Task LoadFact

37 Data Flow Task - FactCurrencyRate Data Flow Task LoadFact

38 Data Flow Task - DimProductCategory Data Flow Task LoadDim

39 Data Flow Task - DimSalesTerritory 2 Data Flow Task LoadDim

40 Data Flow Task - DimDepartmentGroup 1 Data Flow Task LoadDim

41 Data Flow Task - FactInternetSales Data Flow Task LoadFact

42 Data Flow Task - FactResellerSales Data Flow Task LoadFact

43 Data Flow Task - DimProductSubcategory Data Flow Task LoadDim

44 Add Primary Keys Execute SQL Task PostLoad

45 Add Indexes Execute SQL Task PostLoad

46 Drop udfMinimumDate Function Execute SQL Task PostLoad

47 Drop temp tables 1 Foreach Loop Container PostLoad

48 Execute SQL Task Execute SQL Task PostLoad

49 Add Foreign Key Constraints Execute SQL Task PostLoad

SSIS Codes:

SSIS Codes Folder C:\TGIEH_SSIS_ETL_Framework\TGIEH_SSIS_ETL_Framework_Solution\Sol_2_AWDataWarehouseRefresh_Using_TGIEH_SEF_V1

SSIS Project Sol_2_AWDataWarehouseRefresh_Using_TGIEH_SEF_V1.dtproj

SSIS Package AWDWRefresh_Using_TGIEH_SEF.dtsx

Output Sample:


SQL Tables Output Sample

Sol_2_AWDataWarehouseRefresh_Using_TGIEH_SEF_V1.xlsx

Text Files Outpue Sample

Sol_2_AWDataWarehouseRefresh_Using_TGIEH_SEF_V1_Log.zip

c. Sol_2_AWDataWarehouseRefresh_Using_TGIEH_SEF_V2

I created five packages, AWDWRefresh_PreLoad.dtsx, AWDWRefresh_TempTables.dtsx,

AWDWRefresh_LoadDim.dtsx, AWDWRefresh_LoadFact.dtsx, and

AWDWRefresh_PostLoad.dtsx, by using TGIEH_SEF_Template_Pkg and put tasks into the


13 | P a g e

packages according to above AWDWRefresh Tasks Grouping. Like above demos, Run the SQL

script Create Tables.sql to reset tables, execute the package, and use Excel Add-in SQL

Utilities to output the tables to worksheets.

Have you noticed the records in dbo.ETL_Rowcount? Below are the steps to capture the

record count for DimAccount:

Create the variable: RowCount_DimAccount

Change RaiseChangedEvent True

Because it’s used Bulk Insert Task, I setup a SQL Task SQL - DimAccount Row Count

to set the RowCount_DimAccount. For Data Flow Task, I used Row Count to set the

variables.

Okay, I can see AWDataWarehouseRefresh Project clearly now and it’s easy for me to

maintain it.

SSIS Codes:

SSIS Codes Folder C:\TGIEH_SSIS_ETL_Framework\TGIEH_SSIS_ETL_Framework_Solution\Sol_2_AWDataWarehouseRefresh_Using_TGIEH_SEF_V2

SSIS Project Sol_2_AWDataWarehouseRefresh_Using_TGIEH_SEF_V2.dtproj

SSIS Package (Master)

AWDWRefresh_Master.dtsx

SSIS Package (PreLoad)

AWDWRefresh_PreLoad.dtsx

SSIS Package (Load Temp Tables)

AWDWRefresh_TempTables.dtsx

SSIS Package (Load Dimensions)

AWDWRefresh_LoadDim.dtsx

SSIS Package (Load Fact Tables)

AWDWRefresh_LoadFact.dtsx

SSIS Package AWDWRefresh_PostLoad.dtsx


14 | P a g e

(Poast Load)

Output Sample:


SQL Tables Output Sample

Sol_2_AWDataWarehouseRefresh_Using_TGIEH_SEF_V2.xlsx

Text Files Outpue Sample

Sol_2_AWDataWarehouseRefresh_Using_TGIEH_SEF_V2_Log.zip


15 | P a g e

VI. SSIS Development Using the SSIS ETL Framework

Recommend to use the following steps for SSIS ETL development using the SSIS ETL Framework:

1) Deploy the Framework DLL (Refer to Installation Instructions)

2) Create a Visual Studio Solution, add a Integration Services Project, and add the Framework

package TGIEH_SEF_Template_Pkg.dtsx

(C:\TGIEH_SSIS_ETL_Framework\TGIEH_SSIS_ETL_Framework_Solution\Sol_1_TGIEH_SSIS_

ETL_Framework_Template\TGIEH_SEF_Template_Pkg.dtsx) to the project

3) Review and setup SSIS Package Configurations and SSIS XML configuration file

4) Review and define SSIS Variables

5) Review and create SSIS Connections

6) Review and Setup SSIS Package Properties

7) Review and setup SSIS configuration table dbo.TGIEH_SEF_Configurations in

TGIEH_SSIS_ETL_Framework

8) Reset GUIDs by using BIDS Helper

9) Copy the package to C:\Program Files (x86)\Microsoft Visual Studio

9.0\Common7\IDE\PrivateAssemblies\ProjectItems\DataTransformationProject\DataTransf

ormationItems (64 bit machine) and save it as a template so you can add it to your SSIS

project (see Figure 10 – Add New Item from Visual Studio install templates).

Figure 10 – Add New Item from Visual Studio install templates

10) Start your SSIS package development

11) You might create many templates by functionalities, i.e.: E Package template, T Package and

L Package Template.


16 | P a g e

Once you’re familiar with the framework, you might change above to accommodate your development

and environment.


17 | P a g e

VII. Further Developments

There are three customizable components and I am going leave the creations and customizations to you

because you know your environment better.

a. ETL Logging Reporting Creation

In above demos, I used my SQL development tool - Excel add-in SQL utilities to show you the

results but you should create SSRS Reports with five tables -- ETL_Job, ETL_Package,

ETL_Rowcount, ETL_Task, and ETL_Error -- for ETL logging and deploy them to SQL Server Report

Server so users can view the logs through SQL Server Report Manager.

b. Email Notification Customization

The DLL sends out Email notifications by calling the stored procedure dbo.usp_ETL_SendMail

(C:\TGIEH_SSIS_ETL_Framework\SQL\usp_ETL_SendMail.sql), see Figure 11 – Email Notifications

Content Sample. You can modify the subject and content (body) format of email in the stored

procedure to suit your environment.


18 | P a g e

Figure 11 – Email Notifications Content Sample

c. Debug Output Text Files Customization

The C# script task in the Event Handlers not only calls the DLL but also write variables

information to the text files if the flag ETL_Write_Text_Log is set to True and the file name

ETL_Log_File is assigned with path and file name. You can customize the output by modifying

the Write to Log File section (see Figure 12 – C# Write to Log File Section) in the C# script to

meet your troubleshooting needs.

Figure 12 – C# Write to Log File Section


19 | P a g e

VIII. Suggestions

If you have any comments or suggestions regarding SSIS ETL Framework by Tracking GUIDs in Event

Handlers, please send me an email to [email protected].

Happy SSIS Coding!

ssis etl framework by tracking guids in event handlers version 1[1]

Documents