Evolution of an SSIS Framework - SQL Server Integration Services Design Patterns, Second Edition (2014)

SQL Server Integration Services Design Patterns, Second Edition (2014)

APPENDIX A. Evolution of an SSIS Framework

SSIS frameworks are the next logical step after SSIS design patterns because frameworks comprise many patterns. At a minimum, an SSIS framework should provide package execution control and monitoring. That sounds simple, but I assure you, it is not. Execution control requires a working knowledge of the Integration Services runtime API. Understanding the subtleties of tight—and loose—coupling is not merely helpful, it can make or ruin your day (or data integration solution).

Why would anyone need an SSIS framework if SSIS 2012 and 2014 include the SSIS Catalog? That is an excellent question. The SSIS 2012 and 2014 Catalogs utilize the project deployment model—the default for SSIS projects developed in SQL Server Data Tools - Business Intelligence (SSDT-BI). But SSIS 2012 and 2014 also include the package deployment model so they support upgrading legacy SSIS projects to SSIS 2012 or 2014. In addition, there are use cases for using the SSIS Catalog for execution and monitoring and also for using a custom framework and the package deployment model. As a data integration architect, I am very grateful to the Microsoft SSIS Team for both options.

In this appendix, I will walk you through designing and building a custom, serial SSIS framework that I’ve named the SSIS Execution and Monitoring Framework. This framework will work with SSIS 2012/2014’s package deployment model, complete with a SQL Server Reporting Services solution. Building an SSIS framework is an advanced task. I will help you build it from the ground up using many of the design patterns covered earlier in this book.

Starting in the Middle

We begin at the heart of execution control with the parent–child pattern. You need to start by creating a new SSIS solution and project named SSISConfig2014. Rename the default Package.dtsx to Child1.dtsx. Open the Child1 SSIS package and add a Script task to the control flow. Rename the Script task Who Am I? and open the Script task’s editor. On the Script page, set the ScriptLanguage property to Microsoft Visual Basic 2012. Click the ellipsis in the ReadOnlyVariables property value textbox and add the System::TaskName andSystem::PackageName variables. Open the script editor and add the following code in Sub Main().

Listing A-1. Sub Main from Who Am I? Script Task in Child1.dtsx Package

Public Sub Main()

Dim sPackageName As String = Dts.Variables("PackageName").Value.ToString
Dim sTaskName As String = Dts.Variables("TaskName").Value.ToString

MsgBox("I am " & sPackageName, , sTaskName)

Dts.TaskResult = ScriptResults.Success
End Sub

The code shown in Listing A-1 pops up a message box that informs an observer of the name of the package from which the message box originates. This is reusable code. You can copy and paste this Script task into any SSIS package and it will perform the same way each time.

Now close the editor and execute the Child1.dtsx package in the SSDT debugger. When I execute the package, I see a message box similar to the one shown in Figure A-1.

9781484200834_FigAppA-01.jpg

Figure A-1. Message box from Child1.dtsx

Child.dtsx will be your first test package. We will use Child1.dtsx going forward to conduct tests of the SSIS Execution and Monitoring Framework.

Before we proceed, change the deployment model for the SSIS from project deployment model—the default—to package deployment model. To accomplish the conversion, right-click the SSIS project in Solution Explorer and click Convert to Package Deployment Model, as shown inFigure A-2.

9781484200834_FigAppA-02.jpg

Figure A-2. Converting the project to the package deployment model

You will need to click the OK button on the dialog to acknowledge that you understand that this will change the features that are available for you to use in SSIS. Once the conversion is complete, you will see a result pane informing you that the project Child1.dtsx was converted to the package deployment model. The project in Solution Explorer will also indicate that the non-default deployment model has been selected, as shown in Figure A-3.

9781484200834_FigAppA-03.jpg

Figure A-3. Package deployment model

Add a new SSIS package and rename it Parent.dtsx. Add an Execute Package task to the control flow of Parent.dtsx. Rename the Execute Package task Execute Child Package and open the editor. On the Package page, set the Location property to File System and click the drop-down for the Connection property value. Click <New connection…> to configure a new file connection manager. Set the File Connection Manager Editor’s Usage Type property to Existing File. Browse to the location of the SSISConfig2014 project and select Child1.dtsx. Click the OK button to close the File Connection Manager Editor and click OK again to close the Execute Package Task Editor. Note the file connection manager that was created when you were configuring the Execute Package task. It is named Child1.dtsx; rename it Child.dtsx.

Test the Parent.dtsx package by executing it in the SSDT debugger. If all goes as planned, Child1.dtsx will execute and display the message box shown in Figure A-1. Acknowledge the message box and stop the debugger.

This is the parent-child pattern in action. You can improve upon the parent-child with a little metadata. How? I’m glad you asked. First, add an SSIS variable named ChildPackagePath (String). Click on the Child.dtsx connection manager, and then press F4 to display properties. The ConnectionString property of the file connection manager is the path to the file. Select the ConnectionString property, copy it to the clipboard, and then paste it into the Value property of the ChildPackagePath SSIS variable. Return to the properties of the file connection manager named Child.dtsx and click the ellipsis in the Value textbox of the Expressions property. When the Property Expressions Editor displays, select ConnectionString from the Property drop-down, as shown in Figure A-4.

9781484200834_FigAppA-04.jpg

Figure A-4. The file connection manager Property Expressions Editor

Click the ellipsis in the Expression textbox beside ConnectionString. Expand the Variables and Parameters virtual folder in the upper left of the Expression Builder. Drag the variable User::ChildPackagePath from the virtual folder to the Expression textbox and click the Evaluate Expression button, as shown in Figure A-5.

9781484200834_FigAppA-05.jpg

Figure A-5. Assigning the User::ChildPackagePath variable to the ConnectionString expression

Click the OK button to close the Expression Builder and then click the OK button to close the Property Expressions Editor. At this point, the ConnectionString property of the Child.dtsx file connection manager is managed by the User::ChildPackagePath SSIS variable. You can test this functionality by creating a second test child package. Fortunately, creating a second test child package is relatively simple.

In Solution Explorer, right-click the Child1.dtsx SSIS package and then click Copy. Right-click the SSIS Packages virtual folder and click Paste. Change the name of the new package from Child1 1.dtsx to Child2.dtsx.

Return to the Parent.dtsx package and change the value of the ChildPackagePath variable, substituting Child2.dtsx for Child1.dtsx. Execute Parent.dtsx in the SSDT debugger and observe the results, as shown in Figure A-6.

9781484200834_FigAppA-06.jpg

Figure A-6. Executing Child2.dtsx in the parent-child pattern

Pretty cool, huh? We’re just getting started!

Let’s create a database to hold package metadata. Open SQL Server Management Studio (SSMS) and execute the T-SQL script shown in Listing A-2.

Listing A-2. Creating the SSISConfig Database

Use master
go

/* SSISConfig database */
If Not Exists(Select name
From sys.databases
Where name = 'SSISConfig')
begin
print 'Creating SSISConfig database'
Create Database SSISConfig
print 'SSISConfig database created'
end
Else
print 'SSISConfig database already exists.'
print ''
go

The script in Listing A-2 is re-executable. Plus, it informs the person executing the script about its actions via Print statements. The first time you execute this script, you will see the following messages in the SSMS Messages tab:

Creating SSISConfig database
SSISConfig database created

The second time—and each subsequent time—you execute the same script, you will see this message:

SSISConfig database already exists.

Writing re-executable T-SQL is not always feasible, but when feasible, it is a good idea. Now that we have the database, we’ll build a table to hold SSIS package metadata. Listing A-3 contains T-SQL for such a table.

Listing A-3. Building the cfg Schema and cfg.Packages Table

Use SSISConfig
go

/* cfg schema */
If Not Exists(Select name
From sys.schemas
Where name = 'cfg')
begin
print 'Creating cfg schema'
declare @sql varchar(100) = 'Create Schema cfg'
exec(@sql)
print 'Cfg schema created'
end
Else
print 'Cfg schema already exists.'
print ''

/* cfg.Packages table */
If Not Exists(Select s.name + '.' + t.name
From sys.tables t
Join sys.schemas s
On s.schema_id = t.schema_id
Where s.name = 'cfg'
And t.name = 'Packages')
begin
print 'Creating cfg.Packages table'
Create Table cfg.Packages
(
PackageID int identity(1,1)
Constraint PK_Packages
Primary Key Clustered
,PackageFolder varchar(255) Not Null
,PackageName varchar(255) Not Null
)
print 'Cfg.Packages created'
end
Else
print 'Cfg.Packages table already exists.'
print ''

The script in Listing A-3 creates a schema named cfg if one doesn’t already exist; it then creates a table named cfg.Packages, which contains three columns:

· PackageID is an identity column that serves as the primary key.

· PackageFolder is a VarChar(255) column that holds the path to the folder containing the SSIS package.

· PackageName is a VarChar(255) column that contains the name of the SSIS package.

I recently began identifying the stored procedures, functions, and views that support such a repository as a database programming interface (DPI), and not as an application programming interface, or API, because databases are not applications. Here, you should begin building theSSISConfig DPI with a stored procedure to load data into the cfg.Packages table, as shown in Listing A-4.

Listing A-4. The cfg.AddSSISPackages Stored Procedure

/* cfg.AddSSISPackage stored procedure */
If Exists(Select s.name + '.' + p.name
From sys.procedures p
Join sys.schemas s
On s.schema_id = p.schema_id
Where s.name = 'cfg'
And p.name = 'AddSSISPackage')
begin
print 'Dropping cfg.AddSSISPackage stored procedure'
Drop Procedure cfg.AddSSISPackage
print 'Cfg.AddSSISPackage stored procedure dropped'
end
print 'Creating cfg.AddSSISPackage stored procedure'
print ''
go

Create Procedure cfg.AddSSISPackage
@PackageName varchar(255)
,@PackageFolder varchar(255)
,@PkgID int output
As

Set NoCount On

declare @tbl table (PkgID int)

If Not Exists(Select PackageFolder + PackageName
From cfg.Packages
Where PackageFolder = @PackageFolder
And PackageName = @PackageName)
begin
Insert Into cfg.Packages
(PackageName
,PackageFolder)
Output inserted.PackageID Into @tbl
Values (@PackageName, @PackageFolder)
end
Else
insert into @tbl
(PkgID)
(Select PackageID
From cfg.Packages
Where PackageFolder = @PackageFolder
And PackageName = @PackageName)

Select @PkgID = PkgID From @tbl
go
print 'Cfg.AddSSISPackage stored procedure created.'
print ''

Note the cfg.AddSSISPackage stored procedure returns an integer value that represents the identity column—PackageID—from the cfg.Packages table. You will use this integer value later. Once this stored procedure is in place, you can use the T-SQL script in Listing A-5 to add the packages in the project.

Listing A-5. Adding the Packages to the cfg.Packages Table

/* Variable Declaration */
declare @PackageFolder varchar(255) = F:\Andy\Projects\PublicFramework_PackageDeployment_2014\SSISConfig2014\F:\Andy\Projects\
PublicFramework_PackageDeployment_2014\SSISConfig2014\'
declare @PackageName varchar(255) = 'Child1.dtsx'
declare @PackageID int

/* Add the Child1.dtsx SSIS Package*/
If Not Exists(Select PackageFolder + PackageName
From cfg.Packages
Where PackageFolder = @PackageFolder
And PackageName = @PackageName)
begin
print 'Adding ' + @PackageFolder + @PackageName
exec cfg.AddSSISPackage @PackageName, @PackageFolder, @PackageID output
end
Else
begin
Select @PackageID = PackageID
From cfg.Packages
Where PackageFolder = @PackageFolder
And PackageName = @PackageName
print @PackageFolder + @PackageName + ' already exists in the Framework.'
end

set @PackageName = 'Child2.dtsx'
/* Add the Child2.dtsx SSIS Package*/
If Not Exists(Select PackageFolder + PackageName
From cfg.Packages
Where PackageFolder = @PackageFolder
And PackageName = @PackageName)
begin
print 'Adding ' + @PackageFolder + @PackageName
exec cfg.AddSSISPackage @PackageName, @PackageFolder, @PackageID output
end
Else
begin
Select @PackageID = PackageID
From cfg.Packages
Where PackageFolder = @PackageFolder
And PackageName = @PackageName
print @PackageFolder + @PackageName + ' already exists in the Framework.'
End

We now have enough to test the next step of my SSIS Execution and Monitoring Framework, so let’s returning to SSDT. Add an Execute SQL task to the control flow and rename it Get PackageMetadata. Open the editor and change the ResultSet property to Single row. Change the ConnectionType property to ADO.Net. Click the drop-down in the Connection property and click <New connection…>. Configure an ADO.Net connection to the SSISConfig database. Set the SQLStatement property to the following T-SQL script:

Select PackageFolder + PackageName
From cfg.Packages
Where PackageName = 'Child1.dtsx'

On the Result Set page, add a resultset. Set the Result Name to 0 and the Variable Name to User::ChildPackagePath. Connect a precedence constraint between the Get Package Metadata Execute SQL task and the Execute Child Package Execute Package task. Execute theParent.dtsx package to test it. What happens? The Get Package Metadata Execute SQL task runs a query that returns the full path to the Child1.dtsx package stored in the SSISConfig.cfg.Packages table. The returned path is sent into the ChildPackagePath variable. Remember, this variable controls the Child.dtsx file connection manager, which is used by the Execute Package task.

Alter the query in the Get Package Metadata Execute SQL task to return Child2.dtsx and retest.

Introducing SSIS Applications

An SSIS application is a collection of SSIS packages that execute in a prescribed order. Let’s start by adding a couple of tables and supporting stored procedures to the SSISConfig database.

First, create a table named cfg.Applications, and a stored procedure to add them applications to the table, in SSISConfig using the T-SQL in Listing A-6.

Listing A-6. Building cfg.Applications and cfg.AddSSISApplication

/* cfg.Applications table */
If Not Exists(Select s.name + '.' + t.name
From sys.tables t
Join sys.schemas s
On s.schema_id = t.schema_id
Where s.name = 'cfg'
And t.name = 'Applications')
begin
print 'Creating cfg.Applications table'
Create Table cfg.Applications
(
ApplicationID int identity(1,1)
Constraint PK_Applications
Primary Key Clustered
,ApplicationName varchar(255) Not Null
Constraint U_Applications_ApplicationName
Unique
)
print 'Cfg.Applications created'
end
Else
print 'Cfg.Applications table already exists.'
print ''

/* cfg.AddSSISApplication stored procedure */
If Exists(Select s.name + '.' + p.name
From sys.procedures p
Join sys.schemas s
On s.schema_id = p.schema_id
Where s.name = 'cfg'
And p.name = 'AddSSISApplication')
begin
print 'Dropping cfg.AddSSISApplication stored procedure'
Drop Procedure cfg.AddSSISApplication
print 'Cfg.AddSSISApplication stored procedure dropped'
end
print 'Creating cfg.AddSSISApplication stored procedure'
print ''
go

Create Procedure cfg.AddSSISApplication
@ApplicationName varchar(255)
,@AppID int output
As

Set NoCount On

declare @tbl table (AppID int)

If Not Exists(Select ApplicationName
From cfg.Applications
Where ApplicationName = @ApplicationName)
begin
Insert Into cfg.Applications
(ApplicationName)
Output inserted.ApplicationID into @tbl
Values (@ApplicationName)
end
Else
insert into @tbl
(AppID)
(Select ApplicationID
From cfg.Applications
Where ApplicationName = @ApplicationName)

Select @AppID = AppID from @tbl
go
print 'Cfg.AddSSISApplication stored procedure created.'
print ''

Note the cfg.AddSSISApplication stored procedure returns an integer value that represents the identity column—ApplicationID—from the cfg.Applications table. Add an SSIS application to the table using the following T-SQL in Listing A-7.

Listing A-7. Adding an SSIS Application

declare @ApplicationName varchar(255) = 'SSISApp1'
declare @ApplicationID int

/* Add the SSIS First Application */
If Not Exists(Select ApplicationName
From cfg.Applications
Where ApplicationName = @ApplicationName)
begin
print 'Adding ' + @ApplicationName
exec cfg.AddSSISApplication @ApplicationName, @ApplicationID output
print @ApplicationName + ' added.'
end
Else
begin
Select @ApplicationID = ApplicationID
From cfg.Applications
Where ApplicationName = @ApplicationName
print @ApplicationName + ' already exists in the Framework.'
end
print ''

The script in Listing A-7 uses the cfg.AddSSISApplication stored procedure to add the SSISApp1 SSIS application to the cfg.Applications table in the SSISConfig database.

A Note About Relationships

An SSIS application is a collection of SSIS packages that execute in a prescribed order, so it is pretty obvious that the relationship between an SSIS application and SSIS packages is one-to-many. What may not be as obvious is the relationship between SSIS packages and SSIS applications. Herein is a key benefit of choosing patterns-based development: code reusability, specifically in reference to the SSIS package code. Consider the archive file pattern from the end of Chapter 7 on flat file design patterns. In an enterprise that loads data from dozens or hundreds of flat file sources, this package may be called many times by different SSIS applications. So the relationship between SSIS packages and SSIS applications is also one-to-many. If you do the set math, these relationships combine to create a many-to-many relationship between the applications’ and packages’ tables. This means you need a bridge or resolver table between them to create mappings between SSIS applications and SSIS packages.

I call this table cfg.AppPackages. Listing A-8 contains the T-SQL script that creates cfg.AppPackages and a stored procedure with which it is loaded.

Listing A-8. Creating cfg.AppPackages and cfg.AddSSISApplicationPackage

/* cfg.AppPackages table */
If Not Exists(Select s.name + '.' + t.name
From sys.tables t
Join sys.schemas s
On s.schema_id = t.schema_id
Where s.name = 'cfg'
And t.name = 'AppPackages')
begin
print 'Creating cfg.AppPackages table'
Create Table cfg.AppPackages
(
AppPackageID int identity(1,1)
Constraint PK_AppPackages
Primary Key Clustered
,ApplicationID int Not Null
Constraint FK_cfgAppPackages_cfgApplications_ApplicationID
Foreign Key References cfg.Applications(ApplicationID)
,PackageID int Not Null
Constraint FK_cfgAppPackages_cfgPackages_PackageID
Foreign Key References cfg.Packages(PackageID)
,ExecutionOrder int Null
)
print 'Cfg.AppPackages created'
end
Else
print 'Cfg.AppPackages table already exists.'
print ''

/* cfg.AddSSISApplicationPackage stored procedure */
If Exists(Select s.name + '.' + p.name
From sys.procedures p
Join sys.schemas s
On s.schema_id = p.schema_id
Where s.name = 'cfg'
And p.name = 'AddSSISApplicationPackage')
begin
print 'Dropping cfg.AddSSISApplicationPackage stored procedure'
Drop Procedure cfg.AddSSISApplicationPackage
print 'Cfg.AddSSISApplicationPackage stored procedure dropped'
end
print 'Creating cfg.AddSSISApplicationPackage stored procedure'
go

Create Procedure cfg.AddSSISApplicationPackage
@ApplicationID int
,@PackageID int
,@ExecutionOrder int = 10
As

Set NoCount On

If Not Exists(Select AppPackageID
From cfg.AppPackages
Where ApplicationID = @ApplicationID
And PackageID = @PackageID)
begin
Insert Into cfg.AppPackages
(ApplicationID
,PackageID
,ExecutionOrder)
Values (@ApplicationID, @PackageID, @ExecutionOrder)
end
go
print 'Cfg.AddSSISApplicationPackage stored procedure created.'
print ''

To create the mappings between SSIS applications and SSIS packages, you need the IDs of each. Executing the following queries returns the information you need:

Select * from cfg.Applications
Select * from cfg.Packages

You will now use that information to execute the cfg.AddSSISApplicationPackage stored procedure, building SSISApp1 in the metadata of the SSISConfig database and assigning it Child1.dtsx and Child2.dtsx—in that order. I use the T-SQL script shown inListing A-9 to accomplish the mapping.

Listing A-9. Coupling the Child1 and Child2 SSIS Packages to the SSISApp1 SSIS Application

declare @ExecutionOrder int = 10
declare @ApplicationID int = 1
declare @PackageID int = 1
declare @ApplicationName varchar(255) = 'SSISApp1'
declare @PackageFolder varchar(255) = 'F:\Andy\Projects\PublicFramework_PackageDeployment_2014\SSISConfig2014\'
declare @PackageName varchar(255) = 'Child1.dtsx'

If Not Exists(Select AppPackageID
From cfg.AppPackages
Where ApplicationID = @ApplicationID
And PackageID = @PackageID
And ExecutionOrder = @ExecutionOrder)
begin
print 'Adding ' + @ApplicationName + '.' + @PackageName
+ ' to Framework with ExecutionOrder ' + convert(varchar, @ExecutionOrder)
exec cfg.AddSSISApplicationPackage @ApplicationID, @PackageID, @ExecutionOrder
print @PackageName + ' added and wired to ' + @ApplicationName
end
Else
print @ApplicationName + '.' + @PackageName
+ ' already exists in the Framework with ExecutionOrder '
+ convert(varchar, @ExecutionOrder)

/*Child2.dtsx */
set @PackageName = 'Child2.dtsx'
set @ExecutionOrder = 20
set @PackageID = 2

If Not Exists(Select AppPackageID
From cfg.AppPackages
Where ApplicationID = @ApplicationID
And PackageID = @PackageID
And ExecutionOrder = @ExecutionOrder)
begin
print 'Adding ' + @ApplicationName + '.' + @PackageName
+ ' to Framework with ExecutionOrder ' + convert(varchar, @ExecutionOrder)
exec cfg.AddSSISApplicationPackage @ApplicationID, @PackageID, @ExecutionOrder
print @PackageName + ' added and wired to ' + @ApplicationName
end
Else
print @ApplicationName + '.' + @PackageName
+ ' already exists in the Framework with ExecutionOrder '
+ convert(varchar, @ExecutionOrder)

One note about the T-SQL script shown in Listing A-9. This is not the way I would load this metadata into production (or even test) environments. I would not re-declare the ApplicationName, PackageFolder, PackageName, ApplicationID, and PackageID variables; rather, I would reuse these values from the previous T-SQL scripts. I alluded to this earlier when I mentioned we will use the ApplicationID and PackageID values later. I will provide a full T-SQL Metadata Load script later in this appendix.

Retrieving SSIS Applications in T-SQL

We now have SSIS application metadata stored in the SSISConfig database. Awesome, now what? It’s time to build a stored procedure to return the SSIS package metadata we want for a given SSIS application. Listing A-10 contains the T-SQL data definition language (DDL) script to build such a stored procedure named cfg.GetSSISApplication.

Listing A-10. Creating the cfg.GetSSISApplication Stored Procedure

/* cfg.GetSSISApplication stored procedure */
If Exists(Select s.name + '.' + p.name
From sys.procedures p
Join sys.schemas s
On s.schema_id = p.schema_id
Where s.name = 'cfg'
And p.name = 'GetSSISApplication')
begin
print 'Dropping cfg.GetSSISApplication stored procedure'
Drop Procedure cfg.GetSSISApplication
print 'Cfg.GetSSISApplication stored procedure dropped'
end
print 'Creating cfg.GetSSISApplication stored procedure'
go

Create Procedure cfg.GetSSISApplication
@ApplicationName varchar(255)
As

Select p.PackageFolder + p.PackageName As PackagePath
, ap.ExecutionOrder
, p.PackageName
, p.PackageFolder
, ap.AppPackageID
From cfg.AppPackages ap
Inner Join cfg.Packages p on p.PackageID = ap.PackageID
Inner Join cfg.Applications a on a.ApplicationID = ap.ApplicationID
Where ApplicationName = @ApplicationName
Order By ap.ExecutionOrder
go
print 'Cfg.GetSSISApplication stored procedure created.'
print ''

The cfg.GetSSISApplication stored procedure shown in Listing A-10 accepts a single parameter—ApplicationName—and uses this value to look up the SSIS packages associated with the SSIS application of that name. Note the columns that are returned:

· PackagePath

· ExecutionOrder

· PackageName

· PackagePath

Also note that the SSIS packages are returned in the order specified by ExecutionOrder.

Now test the stored procedure using the existing metadata in the SSISConfig database by executing the following T-SQL statement:

exec cfg.GetSSISApplication 'SSISApp1'

My results appear as shown in Figure A-7.

9781484200834_FigAppA-07.jpg

Figure A-7. Results of cfg.GetSSISApplication statement

Figure A-7 shows the results of the stored procedure statement execution—a result containing two rows of data—and this data represents the SSIS package’s metadata associated with the SSIS application named SSISApp1 in the SSISConfig database.

That was a lot of work! Fortunately, we will not need to repeat most of it. When you want to add SSIS packages and associate them with SSIS applications in the future, the script will look like the T-SQL shown in Listing A-11.

Listing A-11. The Complete T-SQL Script for Adding SSISApp1 and Associated SSIS Packages

Use SSISConfig
go

/* Variable Declaration */
declare @PackageFolder varchar(255) = 'F:\Andy\Projects\PublicFramework_PackageDeployment_2014\SSISConfig2014\'
declare @PackageName varchar(255) = 'Child1.dtsx'
declare @PackageID int
declare @ExecutionOrder int = 10

declare @ApplicationName varchar(255) = 'SSISApp1'
declare @ApplicationID int

/* Add the SSIS First Application */
If Not Exists(Select ApplicationName
From cfg.Applications
Where ApplicationName = @ApplicationName)
begin
print 'Adding ' + @ApplicationName
exec cfg.AddSSISApplication @ApplicationName, @ApplicationID output
print @ApplicationName + ' added.'
end
Else
begin
Select @ApplicationID = ApplicationID
From cfg.Applications
Where ApplicationName = @ApplicationName
print @ApplicationName + ' already exists in the Framework.'
end
print ''

/* Add the Child1.dtsx SSIS Package*/
If Not Exists(Select PackageFolder + PackageName
From cfg.Packages
Where PackageFolder = @PackageFolder
And PackageName = @PackageName)
begin
print 'Adding ' + @PackageFolder + @PackageName
exec cfg.AddSSISPackage @PackageName, @PackageFolder, @PackageID output
end
Else
begin
Select @PackageID = PackageID
From cfg.Packages
Where PackageFolder = @PackageFolder
And PackageName = @PackageName
print @PackageFolder + @PackageName + ' already exists in the Framework.'
end

If Not Exists(Select AppPackageID
From cfg.AppPackages
Where ApplicationID = @ApplicationID
And PackageID = @PackageID
And ExecutionOrder = @ExecutionOrder)
begin
print 'Adding ' + @ApplicationName + '.' + @PackageName
+ ' to Framework with ExecutionOrder ' + convert(varchar, @ExecutionOrder)
exec cfg.AddSSISApplicationPackage @ApplicationID, @PackageID, @ExecutionOrder
print @PackageName + ' added and wired to ' + @ApplicationName
end
Else
print @ApplicationName + '.' + @PackageName
+ ' already exists in the Framework with ExecutionOrder '
+ convert(varchar, @ExecutionOrder)

/*Child2.dtsx */
set @PackageName = 'Child2.dtsx'
set @ExecutionOrder = 20

If Not Exists(Select PackageFolder + PackageName
From cfg.Packages
Where PackageFolder = @PackageFolder
And PackageName = @PackageName)
begin
print 'Adding ' + @PackageFolder + @PackageName
exec cfg.AddSSISPackage @PackageName, @PackageFolder, @PackageID output
end
Else
begin
Select @PackageID = PackageID
From cfg.Packages
Where PackageFolder = @PackageFolder
And PackageName = @PackageName
print @PackageFolder + @PackageName + ' already exists in the Framework.'
end

If Not Exists(Select AppPackageID
From cfg.AppPackages
Where ApplicationID = @ApplicationID
And PackageID = @PackageID
And ExecutionOrder = @ExecutionOrder)
begin
print 'Adding ' + @ApplicationName + '.' + @PackageName
+ ' to Framework with ExecutionOrder ' + convert(varchar, @ExecutionOrder)
exec cfg.AddSSISApplicationPackage @ApplicationID, @PackageID, @ExecutionOrder
print @PackageName + ' added and wired to ' + @ApplicationName
end
Else
print @ApplicationName + '.' + @PackageName
+ ' already exists in the Framework with ExecutionOrder '
+ convert(varchar, @ExecutionOrder)

Retrieving SSIS Applications in SSIS

Return to SSDT-BI and open the editor for the Get Package Metadata Execute SQL task. Change the ResultSet property from Single Row to Full Result Set and change the SQLStatement property to cfg.GetSSISApplication. Set the IsQueryStoredProcedure property to True. On the Parameter Mapping page, click the Add button. Click the drop-down in the Variable Name column and select <New variable…> (you will probably need to scroll up to find <New variable...>). In the Add Variable window, make sure the Container property is set to Parent. Change the Name property to ApplicationName. The Namespace should be User and the Value Type property should be set to String. For the Value property, enter SSISApp1. The Add Variable window should appear as shown in Figure A-8.

9781484200834_FigAppA-08.jpg

Figure A-8. Adding the ApplicationName variable

Click the OK button to close the Add Variable window and change the Data Type of the ApplicationName variable to String. Change the Parameter Name to ApplicationName. Navigate to the Result Set page and change the 0 result name variable fromUser::ChildPackagePath to a new variable with the following settings:

· Container: Parent

· Name: Packages

· Namespace: User

· Value Type: Object

Click the OK button to close the Add Variable window, and click the OK button to close the Execute SQL Task Editor. Delete the precedence constraint between the Get Package Metadata Execute SQL task and the Execute Child Package Execute Package task. Drag a Foreach Loop container onto the control flow and then drag the Execute Child Package Execute Package task inside it. Add a precedence constraint from the Get Package Metadata Execute SQL task to the new Foreach Loop container, and rename the Foreach Loop container Foreach Child Package. Open the Foreach Child Package Foreach Loop container’s editor and navigate to the Collection page. Change the Enumerator to Foreach ADO Enumerator. In the ADO Object Source Variable drop-down, select the User::Packages variable. Accept the default Enumeration Mode: Rows in the First Table.

Navigate to the Variable Mappings page in the Foreach Loop Editor. Click on the Variable drop-down and select the User::ChildPackagePath variable. The Index property will default to 0—do not change it.

The changes we just made accomplish the following:

1. Execute the cfg.GetSSISApplications stored procedure in the SSISConfig database, passing it the value contained in the ApplicationName variable.

2. Push the full result set returned by the stored procedure execution into an SSIS object variable named Packages.

3. Configure a Foreach Loop to point at each row stored in the Packages variable in the order returned.

4. Push the value contained in the first column (Column 0) of the row to which the Foreach Loop points into the User::ChildPackagePath variable.

When the value of the ChildPackagePath variable changes, the ConnectionString property of the Child.dtsx file connection manager is dynamically updated, aiming the connection manager at the path contained in User::ChildPackagePath.

Click the OK button to close the Foreach Loop Container Editor and execute the Parent.dtsx SSIS package in the SSDT debugger. When you do this, you get two message boxes. The first states “I am Child1” and the second appears as shown in Figure A-9.

9781484200834_FigAppA-09.jpg

Figure A-9. Executing a test serial SSIS framework

This code, as it stands, composes an SSIS execution framework. The database contains the metadata and the parent package executes the SSIS packages. Monitoring is next.

Monitoring Execution

Most experienced business intelligence developers will tell you to start with the reports and work your way back to the source data. The source data in this particular case is information collected from the data integration process. What kind of information? Things like start and end execution times, execution status, and error and event messages.

Instance data is recorded for each SSIS application and SSIS package execution. Each entry represents an execution, and there are two tables that hold these entries: Log.SSISAppInstance holds execution metrics about SSIS application instances, and Log.SSISPkgInstanceholds execution metrics for SSIS child package instances. When an SSIS application starts, a row is inserted into the log.SSISAppInstance table. When the SSIS application completes, the row is updated. log.SSISPkgInstance works the same way for each SSIS package in an SSIS application. An SSIS application instance is logically comprised of an application ID and a start time. An SSIS package instance is comprised of an application instance ID, application package ID, and a start time.

Error and event logging is relatively straightforward. You store a description of the error or event, the time it occurred, and the instance IDs. That’s what the reports will reflect, and that’s all there is to logging.

Building Application Instance Logging

Let’s return to SSMS to build the tables and stored procedures to support logging. You need to execute the T-SQL script shown in Listing A-12 to build the instance tables and stored procedures.

Listing A-12. Building the Application Instance Tables and Stored Procedures

/* log schema */
If Not Exists(Select name
From sys.schemas
Where name = 'log')
begin
print 'Creating log schema'
declare @sql varchar(100) = 'Create Schema [log]'
exec(@sql)
print 'Log schema created'
end
Else
print 'Log schema already exists.'
print ''

/* log.SSISAppInstance table */
If Not Exists(Select s.name + '.' + t.name
From sys.tables t
Join sys.schemas s
On s.schema_id = t.schema_id
Where s.name = 'log'
And t.name = 'SSISAppInstance')
begin
print 'Creating log.SSISAppInstance table'
Create Table [log].SSISAppInstance
(
AppInstanceID int identity(1,1)
Constraint PK_SSISAppInstance
Primary Key Clustered
,ApplicationID int Not Null
Constraint FK_logSSISAppInstance_cfgApplication_ApplicationID
Foreign Key References cfg.Applications(ApplicationID)
,StartDateTime datetime Not Null
Constraint DF_cfgSSISAppInstance_StartDateTime
Default(GetDate())
,EndDateTime datetime Null
,[Status] varchar(12) Null
)
print 'Log.SSISAppInstance created'
end
Else
print 'Log.SSISAppInstance table already exists.'
print ''

/* log.LogStartOfApplication stored procedure */
If Exists(Select s.name + '.' + p.name
From sys.procedures p
Join sys.schemas s
On s.schema_id = p.schema_id
Where s.name = 'log'
And p.name = 'LogStartOfApplication')
begin
print 'Dropping log.LogStartOfApplication stored procedure'
Drop Procedure [log].LogStartOfApplication
print 'Log.LogStartOfApplication stored procedure dropped'
end
print 'Creating log.LogStartOfApplication stored procedure'
go

Create Procedure [log].LogStartOfApplication
@ApplicationName varchar(255)
As

declare @ErrMsg varchar(255)
declare @AppID int = (Select ApplicationID
From cfg.Applications
Where ApplicationName = @ApplicationName)

If (@AppID Is Null)
begin
set @ErrMsg = 'Cannot find ApplicationName ' + Coalesce(@ApplicationName, '<NULL>')
raiserror(@ErrMsg,16,1)
return-1
end

Insert Into [log].SSISAppInstance
(ApplicationID, StartDateTime, Status)
Output inserted.AppInstanceID
Values
(@AppID, GetDate(), 'Running')
go
print 'Log.LogStartOfApplication stored procedure created.'
print ''

/* log.LogApplicationSuccess stored procedure */
If Exists(Select s.name + '.' + p.name
From sys.procedures p
Join sys.schemas s
On s.schema_id = p.schema_id
Where s.name = 'log'
And p.name = 'LogApplicationSuccess')
begin
print 'Dropping log.LogApplicationSuccess stored procedure'
Drop Procedure [log].LogApplicationSuccess
print 'Log.LogApplicationSuccess stored procedure dropped'
end
print 'Creating log.LogApplicationSuccess stored procedure'
go

Create Procedure [log].LogApplicationSuccess
@AppInstanceID int
As

update log.SSISAppInstance
set EndDateTime = GetDate()
, Status = 'Success'
where AppInstanceID = @AppInstanceID
go
print 'Log.LogApplicationSuccess stored procedure created.'
print ''

/* log.LogApplicationFailure stored procedure */
If Exists(Select s.name + '.' + p.name
From sys.procedures p
Join sys.schemas s
On s.schema_id = p.schema_id
Where s.name = 'log'
And p.name = 'LogApplicationFailure')
begin
print 'Dropping log.LogApplicationFailure stored procedure'
Drop Procedure [log].LogApplicationFailure
print 'Log.LogApplicationFailure stored procedure dropped'
end
print 'Creating log.LogApplicationFailure stored procedure'
go

Create Procedure [log].LogApplicationFailure
@AppInstanceID int
As

update log.SSISAppInstance
set EndDateTime = GetDate()
, Status = 'Failed'
where AppInstanceID = @AppInstanceID
go
print 'Log.LogApplicationFailure stored procedure created.'
print ''

Now let’s return to SSDT and add application instance logging to the Parent.dtsx package. Drag a new Execute SQL task to the control flow and rename it Log Start of Application. Set the ResultSet property to Single Row. Set the ConnectionType property to ADO.Net and Connection to the SSISConfig connection manager. Set the SQLStatement property to log.LogStartOfApplication and the IsQueryStoredProcedure property to True. Navigate to the Parameter Mapping page and add a new parameter: mapping the User::ApplicationNameSSIS variable to the ApplicationName parameter for the log.LogStartOfApplication stored procedure. Change the Data Type property of the ApplicationName parameter to String. On the Result Set page, add a new result named 0 and map it to a new Int32 variable namedAppInstanceID. Set the default value of the AppInstanceID variable to 0. Close the Execute SQL Task Editor and connect a precedence constraint from the Log Start of Application Execute SQL task to the Get Package Metadata Execute SQL task.

Drag another Execute SQL task onto the control flow beneath the Foreach Child Package Foreach Loop container and rename it Log Application Success. Open the editor, change the ConnectionType property to ADO.Net, and set the Connection property to the SSISConfigconnection manager. Enter log.LogApplicationSuccess in the SQLStatement property and set the IsQueryStoredProcedure property to True. Navigate to the Parameter Mapping page and add a mapping between the User::AppInstanceID SSIS variable and the Int32 AppInstanceID parameter for the log.LogApplicationSuccess stored procedure. Close the Execute SQL Task Editor and connect a precedence constraint from the Foreach Child Package Foreach Loop container to the Log Application Success Execute SQL task.

What did you just accomplish? You added SSIS application instance logging to the control flow of the Parent.dtsx SSIS package. To test this, you need to execute Parent.dtsx in the SSDT debugger.

Once execution completes, execute the following query to observe the logged results:

Select * From [log].SSISAppInstance

When I execute this query, I get the results that are shown in Figure A-10.

9781484200834_FigAppA-10.jpg

Figure A-10. Observing the results of querying the application instance log

What happens when an SSIS application fails? You want to update the log.SSISAppInstance row with an EndDateTime and set the Status to Failed. For this, you will use an Execute SQL task configured to execute the log.LogApplicationFailure stored procedure. The question is: Where? The answer is: the Parent.dtsx package’s OnError event handler.

In SSDT, click the Event Handlers tab on Parent.dtsx. In the Executable drop-down, select Parent; in the Event Handler drop-down, select OnError as shown in Figure A-11.

9781484200834_FigAppA-11.jpg

Figure A-11. Configuring the Parent package’s OnError event handler

Click the Click Here to Create an “OnError” Event Handler for Executable “Parent” link on the surface of the event handler to create the OnError event handler for the Parent.dtsx package. I could walk you through building another Execute SQL task to log the SSIS application failure; however, it’s easier and simpler to just copy the Log Application Success Execute SQL task from the bottom of the control flow and paste it into the Parent.dtsx OnError event handler. When you do, change the name to Log Application Failure and the SQLStatement property tolog.LogApplicationFailure.

You are now ready to test, but you have no real way to test the application failure unless you modify a package—and that just seems tragic. You’re likely going to need to test errors after this, too. So why not build an ErrorTest.dtsx SSIS package and add it to the SSIS application? I like this plan!

Create a new SSIS package and rename it ErrorTest.dtsx. Add a Script task to the control flow and rename it Succeed or Fail? Change the ScriptLanguage property to Microsoft Visual Basic 2012. Open the editor and add the System::TaskName andSystem::PackageName variables to the ReadOnlyVariables property. Open the Script Editor and add the code shown in Listing A-13 to SubMain().

Listing A-13. Code to Succeed or Fail SSIS Package

Public Sub Main()

Dim sPackageName As String = Dts.Variables("PackageName").Value.ToString
Dim sTaskName As String = Dts.Variables("TaskName").Value.ToString
Dim sSubComponent As String = sPackageName & "." & sTaskName

Dim iResponse As Integer = MsgBox("Succeed Package?", MsgBoxStyle.YesNo, sSubComponent)
If iResponse = vbYes Then
Dts.TaskResult = ScriptResults.Success
Else
Dts.TaskResult = ScriptResults.Failure
End If

End Sub

Unit-test by executing ErrorTest.dtsx in the SSDT debugger, as shown in Figure A-12.

9781484200834_FigAppA-12.jpg

Figure A-12. Unit-testing the ErrorTest.dtsx SSIS package

To add this SSIS package to the SSISApp1 SSIS application, append the T-SQL script in Listing A-14 to the T-SQL script in Listing A-11.

Listing A-14. Append This T-SQL Script to Listing A-11 to Add the ErrorTest.dtsx SSIS Package to the SSISApp1 SSIS Application

/*ErrorTest.dtsx */

/* Variable Declaration */
declare @PackageFolder varchar(255) = 'F:\Andy\Projects\PublicFramework_PackageDeployment_2014\SSISConfig2014\'
declare @PackageName varchar(255) = 'Child1.dtsx'
declare @PackageID int
declare @ExecutionOrder int = 10

declare @ApplicationName varchar(255) = 'SSISApp1'
declare @ApplicationID int

/* Add the SSIS First Application */
If Not Exists(Select ApplicationName
From cfg.Applications
Where ApplicationName = @ApplicationName)
begin
print 'Adding ' + @ApplicationName
exec cfg.AddSSISApplication @ApplicationName, @ApplicationID output
print @ApplicationName + ' added.'
end
Else
begin
Select @ApplicationID = ApplicationID
From cfg.Applications
Where ApplicationName = @ApplicationName
print @ApplicationName + ' already exists in the Framework.'
end
print ''

set @PackageName = 'ErrorTest.dtsx'
set @ExecutionOrder = 30

If Not Exists(Select PackageFolder + PackageName
From cfg.Packages
Where PackageFolder = @PackageFolder
And PackageName = @PackageName)
begin
print 'Adding ' + @PackageFolder + @PackageName
exec cfg.AddSSISPackage @PackageName, @PackageFolder, @PackageID output
end
Else
begin
Select @PackageID = PackageID
From cfg.Packages
Where PackageFolder = @PackageFolder
And PackageName = @PackageName
print @PackageFolder + @PackageName + ' already exists in the Framework.'
end

If Not Exists(Select AppPackageID
From cfg.AppPackages
Where ApplicationID = @ApplicationID
And PackageID = @PackageID
And ExecutionOrder = @ExecutionOrder)
begin
print 'Adding ' + @ApplicationName + '.' + @PackageName
+ ' to Framework with ExecutionOrder ' + convert(varchar, @ExecutionOrder)
exec cfg.AddSSISApplicationPackage @ApplicationID, @PackageID, @ExecutionOrder
print @PackageName + ' added and wired to ' + @ApplicationName
end
Else
print @ApplicationName + '.' + @PackageName
+ ' already exists in the Framework with ExecutionOrder '
+ convert(varchar, @ExecutionOrder)

Now open Parent.dtsx and execute it in the SSDT debugger. Once prompted by the ErrorTest.dtsx message box, click the No button to cause the ErrorTest.dtsx to fail. This should cause the Parent.dtsx package OnError event handler to fire, as shown in Figure A-13.

9781484200834_FigAppA-13.jpg

Figure A-13. I have mixed emotions about successful OnError event handlers

A couple successful and failed executions later, the log.SSISAppInstance table contains the rows shown in Figure A-14

9781484200834_FigAppA-14.jpg

Figure A-14. Successes and failures of SSISApp1

That’s a wrap on application instance logging! Next, let’s build out child package instance logging.

Building Package Instance Logging

Package instance logging works like application instance logging, only on a different scale. An application instance consists of an application ID and an execution start time. A package instance consists of an application package ID, an application instance ID, and an execution start time.

Let’s start by creating the log.SSISPkgInstance table and stored procedures. Listing A-15 contains these database objects.

Listing A-15. Building the Package Instance Logging Table and Stored Procedures

/* log.SSISPkgInstance table */
If Not Exists(Select s.name + '.' + t.name
From sys.tables t
Join sys.schemas s
On s.schema_id = t.schema_id
Where s.name = 'log'
And t.name = 'SSISPkgInstance')
begin
print 'Creating log.SSISPkgInstance table'
Create Table [log].SSISPkgInstance
(
PkgInstanceID int identity(1,1)
Constraint PK_SSISPkgInstance Primary Key Clustered
,AppInstanceID int Not Null
Constraint FK_logSSISPkgInstance_logSSISAppInstance_AppInstanceID
Foreign Key References [log].SSISAppInstance(AppInstanceID)
,AppPackageID int Not Null
Constraint FK_logSSISPkgInstance_cfgAppPackages_AppPackageID
Foreign Key References cfg.AppPackages(AppPackageID)
,StartDateTime datetime Not Null
Constraint DF_cfgSSISPkgInstance_StartDateTime
Default(GetDate())
,EndDateTime datetime Null
,[Status] varchar(12) Null
)
print 'Log.SSISPkgInstance created'
end
Else
print 'Log.SSISPkgInstance table already exists.'
print ''

/* log.LogStartOfPackage stored procedure */
If Exists(Select s.name + '.' + p.name
From sys.procedures p
Join sys.schemas s
On s.schema_id = p.schema_id
Where s.name = 'log'
And p.name = 'LogStartOfPackage')
begin
print 'Dropping log.LogStartOfPackage stored procedure'
Drop Procedure [log].LogStartOfPackage
print 'Log.LogStartOfPackage stored procedure dropped'
end
print 'Creating log.LogStartOfPackage stored procedure'
go

Create Procedure [log].LogStartOfPackage
@AppInstanceID int
,@AppPackageID int
As

declare @ErrMsg varchar(255)

Insert Into log.SSISPkgInstance
(AppInstanceID, AppPackageID, StartDateTime, Status)
Output inserted.PkgInstanceID
Values
(@AppInstanceID, @AppPackageID, GetDate(), 'Running')
go
print 'Log.SSISPkgInstance stored procedure created.'
print ''

/* log.LogPackageSuccess stored procedure */
If Exists(Select s.name + '.' + p.name
From sys.procedures p
Join sys.schemas s
On s.schema_id = p.schema_id
Where s.name = 'log'
And p.name = 'LogPackageSuccess')
begin
print 'Dropping log.LogPackageSuccess stored procedure'
Drop Procedure [log].LogPackageSuccess
print 'Log.LogPackageSuccess stored procedure dropped'
end
print 'Creating log.LogPackageSuccess stored procedure'
go

Create Procedure [log].LogPackageSuccess
@PkgInstanceID int
As

update log.SSISPkgInstance
set EndDateTime = GetDate()
, Status = 'Success'
where PkgInstanceID = @PkgInstanceID
go
print 'Log.LogPackageSuccess stored procedure created.'
print ''

/* log.LogPackageFailure stored procedure */
If Exists(Select s.name + '.' + p.name
From sys.procedures p
Join sys.schemas s
On s.schema_id = p.schema_id
Where s.name = 'log'
And p.name = 'LogPackageFailure')
begin
print 'Dropping log.LogPackageFailure stored procedure'
Drop Procedure [log].LogPackageFailure
print 'Log.LogPackageFailure stored procedure dropped'
end
print 'Creating log.LogPackageFailure stored procedure'
go

Create Procedure [log].LogPackageFailure
@PkgInstanceID int
As

update log.SSISPkgInstance
set EndDateTime = GetDate()
, Status = 'Failed'
where PkgInstanceID = @PkgInstanceID
go
print 'Log.LogPackageFailure stored procedure created.'
print ''

The log.SSISPkgInstance table will hold the SSIS package instance data. Log.LogStartofPackage inserts a row into the SSISPkgInstance table; log.LogPackageSuccess updates the row with an EndDateTime and a 'Success' status, whilelog.LogPackageFailure updates the record with an EndDateTime and a 'Failed' status.

In Parent.dtsx, open the editor for the Foreach Child Package Foreach Loop container. Navigate to the Variable Mappings page and add a new variable. Configure the following settings in the Add Variable window:

· Container: Parent

· Name: AppPackageID

· Namespace: User

· Value Type: Int32

· Value: 0

Click the OK button to close the Add Variable window. The AppInstanceID—which exists in the dataset inside the User::Packages SSIS variable—is returned from executing the cfg.GetSSISApplication stored procedure. The AppPackageID column is returned as the fifth column. Therefore, the AppPackageID variable’s Index column on the Variable Mappings page of the Foreach Child Package Foreach Loop container should be set to 4 (the fifth value in a 0-based array). Click the OK button to close the Foreach Child Package Foreach Loop Container Editor.

Add an Execute SQL task to the Foreach Child Package Foreach Loop container. Rename the new Execute SQL task Log Start of Package. Open the editor and set the ResultSet property to Single Row. Set the ConnectionType property to ADO.Net and the Connection to theSSISConfig connection manager. Set the SQLStatement property to log.LogStartOfPackage and the IsQueryStoredProcedure property to True. Navigate to the Parameter Mapping page and add two new parameters:

· Variable Name: User::AppInstanceID

· Direction: Input

· Data Type: Int32

· Parameter Name: AppInstanceID

· Variable Name: User::AppPackageID

· Direction: Input

· Data Type: Int32

· Parameter Name: AppPackageID

On the Result Set page, add a new result named 0 and map it to a new Parent-level Int32 variable named PkgInstanceID, with a default value of 0. Close the Execute SQL Task Editor. Connect a precedence constraint from the Log Start of Package Execute SQL task to the Execute Child Package Execute Package task.

Add two more Execute SQL tasks to the Foreach Child Package Foreach Loop container. Rename the first Log Package Success, set the connection properties from the ADO.Net connection manager used to connect to the SSISConfig database, the SQLStatement property tolog.LogPackageSuccess, and the IsQueryStoredProcedure property to True. On the Parameter Mapping page, add a parameter and map the User::PkgInstanceID variable to the PkgInstanceID parameter for the log.LogStartofPackage stored procedure. Connect a precedence constraint (OnSuccess) from the Execute Child Package Execute Package task to the Log Package Success Execute SQL task.

Rename the second Log Package Failure, set the connection properties from the ADO.Net connection manager used to connect to the SSISConfig database, set the SQLStatement property to log.LogPackageFailure, and set the IsQueryStoredProcedure property to True. On the Parameter Mapping page, add a parameter and map the User::PkgInstanceID variable to the PkgInstanceID parameter for the log.LogStartofPackage stored procedure. Connect a precedence constraint (OnFailure) from the Execute Child Package Execute Package task to the Log Package Failure Execute SQL task.

Test the package instance logging by running a few test executions. Allow one to succeed and the other to fail. When you check the Application and Package Instance tables, the results should appear as shown in Figure A-15.

9781484200834_FigAppA-15.jpg

Figure A-15. Examining the application and package instance logs

You can tell by examining the Application Instance and Package Instance log tables that AppInstanceID8 started at 6:47:24 PM 29 Jun 2014. You can also see three SSIS packages—with PkgInstanceID’s 10, 11, and 12—were executed as part of the SSIS application. Each package succeeded, and the SSIS application succeeded as well. You also know AppInstanceID7 started at 6:14:15 PM 29 Jun 2014 and executed PkgInstanceID’s 7, 8, and 9. PkgInstanceID’s 7 and 8 succeeded, but PkgInstanceID 9 failed; failing the SSIS application.

Cool? Cool. Let’s move to error and event logging.

Building Error Logging

Instrumenting data integration processes to capture and preserve error and exception metadata is the most important and useful type of logging. Exceptions and errors are going to happen. SSIS provides a fairly robust model for capturing and reporting errors as long as you realize you can mostly ignore the error codes. The error descriptions, however, are mostly good. So it balances out.

Before I demonstrate how to capture error messages in SSIS, let’s discuss why. I used to manage a team of data integration developers. The team ranged in size from 28 to 40 developers, and I built very large ETL solutions for US state government interests. Part of my job was to figure out best practices. Having all SSIS packages log error data in the same format to the same location is a best practice. But how do you do this with 40 developers? Have you ever tried to get 40 developers to do the same thing the same way? It’s like herding cats. The problem was half of them thought they were smarter than me; and half of those were correct in thinking that. But this wasn’t the kind of problem that required deep thinking; this required strategy. So what’s the best strategy for getting every developer to build the exact same kind of log for every SSIS package every time? You guessed it: don’t let them. Take error logging completely out of their hands.

Soon after learning how to use the Execute Package task, I learned events “bubble” from child to parent packages. For the purposes of error logging, this means I can capture and record any error at the parent package. Even better, it means I can do this with no code in the child package. Problem solved.

Let’s take a look at how to implement this functionality into an SSIS framework. First, let’s add a table and a stored procedure to record and preserve errors, as shown in Listing A-16.

Listing A-16. Building the Error Logging Table and Stored Procedure

/* log.SSISErrors table */
If Not Exists(Select s.name + '.' + t.name
From sys.tables t
Join sys.schemas s
On s.schema_id = t.schema_id
Where s.name = 'log'
And t.name = 'SSISErrors')
begin
print 'Creating log.SSISErrors table'
Create Table [log].SSISErrors
(
ID int identity(1,1)
Constraint PK_SSISErrors Primary Key Clustered
,AppInstanceID int Not Null
Constraint FK_logSSISErrors_logSSISAppInstance_AppInstanceID
Foreign Key References [log].SSISAppInstance(AppInstanceID)
,PkgInstanceID int Not Null
Constraint FK_logSSISErrors_logPkgInstance_PkgInstanceID
Foreign Key References [log].SSISPkgInstance(PkgInstanceID)
,ErrorDateTime datetime Not Null
Constraint DF_logSSISErrors_ErrorDateTime
Default(GetDate())
,ErrorDescription varchar(max) Null
,SourceName varchar(255) Null
)
print 'Log.SSISErrors created'
end
Else
print 'Log.SSISErrors table already exists.'
print ''

/* log.LogError stored procedure */
If Exists(Select s.name + '.' + p.name
From sys.procedures p
Join sys.schemas s
On s.schema_id = p.schema_id
Where s.name = 'log'
And p.name = 'LogError')
begin
print 'Dropping log.LogError stored procedure'
Drop Procedure [log].LogError
print 'Log.LogError stored procedure dropped'
end
print 'Creating log.LogError stored procedure'
go

Create Procedure [log].LogError
@AppInstanceID int
,@PkgInstanceID int
,@SourceName varchar(255)
,@ErrorDescription varchar(max)
As

insert into log.SSISErrors
(AppInstanceID, PkgInstanceID, SourceName, ErrorDescription)
Values
(@AppInstanceID
,@PkgInstanceID
,@SourceName
,@ErrorDescription)
go
print 'Log.LogError stored procedure created.'
print ''

Each row in the log.SSISErrors table contains an AppInstanceID and PkgInstanceID for identification purposes. Why both? It is designed to capture and preserve errors that originate in both the parent and child packages. An error in the Parent.dtsx package will have aPkgInstanceID of 0. The remaining columns capture metadata about the error proper: the date and time the error occurred (ErrorDateTime), the error message (ErrorDescription), and the SSIS task from which the error originated (SourceName).

Image Caution Adding a row to the log.SSISErrors table with a PkgInstanceID of 0 will actually raise a foreign key constraint violation at this time, but I will address this matter later in the appendix.

It is important to note that error events are “raised” by SSIS tasks. When an error event is instantiated, its fields are populated with information such as the error description and source name (the name of the task raising the error). These data do not change as the event navigates, or bubbles, inside the SSIS package execution stack. When the event arrives at the Parent.dtsx package in the framework, it will contain the name of the task that originated the error (SourceName) and the description of the error from that task (ErrorDescription).

When the error bubbles to the Parent.dtsx package, you will call the log.LogError stored procedure to populate the log.SSISErrors table. In SSDT, return to the Parent.dtsx package’s OnError event handler that we configured earlier. Add an Execute SQL task and rename it Log Error. Open the editor and configure the ConnectionType and Connection properties to connect to the SSISConfig database via ADO.Net. Set the SQLStatement property to log.LogError and the IsQueryStoredProcedure property to True. Navigate to the Parameter Mapping page and add the following parameters:

· Variable Name: User::AppInstanceID

· Direction: Input

· Data Type: Int32

· Parameter Name: AppInstanceID

· Variable Name: User::PkgInstanceID

· Direction: Input

· Data Type: Int32

· Parameter Name: PkgInstanceID

· Variable Name: System::SourceName

· Direction: Input

· Data Type: String

· Parameter Name: SourceName

· Variable Name: System::ErrorDescription

· Direction: Input

· Data Type: String

· Parameter Name: ErrorDescription

We created the AppInstanceID and PkgInstanceID SSIS variables earlier in this appendix. You are now using the two variables from the System namespace—SourceName and ErrorDescription—which are two of the fields populated when an error event is first raised by the originating task.

Once these parameters are mapped, close the Execute SQL Task Editor and connect a precedence constraint from the Log Error Execute SQL task to the Log Application Failure Execute SQL task, as shown in Figure A-16.

9781484200834_FigAppA-16.jpg

Figure A-16. Adding the Log Error Execute SQL task to the Parent Package OnError event handler

Test the new error logging functionality by running Parent.dtsx in the SSDT debugger. When prompted from the ErrorTest.dtsx package, click the No button to generate an error. In SSMS, execute the following query to examine the error metadata:

Select * From log.SSISErrors

The results should appear similar to those shown in Figure A-17.

9781484200834_FigAppA-17.jpg

Figure A-17. Error metadata in the log.SSISErrors table

As you can see from the preceding image (and hopefully your own code if you are following along at home), error logging can make trouble shooting SSIS issues much simpler.

Event logging is very similar to error logging in SSIS. Part of the reason is that SSIS reuses the object model for the OnError event handler in the OnInformation event handler.

Let’s begin by adding another table and stored procedure to the SSISConfig database. The T-SQL script in Listing A-17 accomplishes this task.

Listing A-17. Building the Event Logging Table and Stored Procedure

/* log.SSISEvents table */
If Not Exists(Select s.name + '.' + t.name
From sys.tables t
Join sys.schemas s
On s.schema_id = t.schema_id
Where s.name = 'log'
And t.name = 'SSISEvents')
begin
print 'Creating log.SSISEvents table'
Create Table [log].SSISEvents
(
ID int identity(1,1)
Constraint PK_SSISEvents Primary Key Clustered
,AppInstanceID int Not Null
Constraint FK_logSSISEvents_logSSISAppInstance_AppInstanceID
Foreign Key References [log].SSISAppInstance(AppInstanceID)
,PkgInstanceID int Not Null
Constraint FK_logSSISEvents_logPkgInstance_PkgInstanceID
Foreign Key References [log].SSISPkgInstance(PkgInstanceID)
,EventDateTime datetime Not Null
Constraint DF_logSSISEvents_ErrorDateTime
Default(GetDate())
,EventDescription varchar(max) Null
,SourceName varchar(255) Null
)
print 'Log.SSISEvents created'
end
Else
print 'Log.SSISEvents table already exists.'
print ''

/* log.LogEvent stored procedure */
If Exists(Select s.name + '.' + p.name
From sys.procedures p
Join sys.schemas s
On s.schema_id = p.schema_id
Where s.name = 'log'
And p.name = 'LogEvent')
begin
print 'Dropping log.LogEvent stored procedure'
Drop Procedure [log].LogEvent
print 'Log.LogEvent stored procedure dropped'
end
print 'Creating log.LogEvent stored procedure'
go

Create Procedure [log].LogEvent
@AppInstanceID int
,@PkgInstanceID int
,@SourceName varchar(255)
,@EventDescription varchar(max)
As

insert into [log].SSISEvents
(AppInstanceID, PkgInstanceID, SourceName, EventDescription)
Values
(@AppInstanceID
,@PkgInstanceID
,@SourceName
,@EventDescription)
go
print 'Log.LogEvent stored procedure created.'
print ''

With the exception of the column names, the log.SSISEvents table is precisely the same design as the log.SSISErrors table. Return to SSDT and copy the Log Error Execute SQL task from the Parent.dtsx OnError event handler. Change the Event Handler drop-down from OnError to OnInformation and create the OnInformation event handler by clicking the link. Next, paste the contents of the clipboard onto the OnInformation event handler surface. Open the editor and change the name of the task to Log Event. Edit the SQLStatement property to readlog.LogEvent. On the Parameter Mapping page, change the ErrorDescription parameter name from ErrorDescription to EventDescription. Close the Execute SQL Task Editor and you are done.

But what about all that “Error” stuff in the parameter mapping? The OnInformation event handler message is conveyed via an SSIS variable named System::ErrorDescription. That is not a typo. You might expect it to be InformationDescription, but it’s not, which makes less work for me (and you).

If you execute Parent.dtsx now to test the new event logging functionality, then you won’t see any events logged. Bummer. How do you get events from SSIS? Several tasks provide information via OnInformation events. The Data Flow task, for example, provides lots of helpful metadata about rows read from sources and written to destinations, and lookup cache sizes, rows, and time to populate. You can also inject OnInformation events into the execution stream using a Script task.

I like to include Script tasks that summarize the information I have about SSIS applications and packages in SSIS framework parent packages. Let’s add those now.

Drag a Script task onto the Parent.dtsx package’s control flow and rename it Log Application Variables. Open the editor and change the ScriptLanguage to Microsoft Visual Basic 2012. Add the following variables to the ReadOnlyVariables property:

· System::TaskName

· System::PackageName

· User::AppInstanceID

· User::ApplicationName

Edit the script and place the code shown in Listing A-18 in Sub Main().

Listing A-18. Raising an Information Event from a Script Task

Public Sub Main()

Dim sPackageName As String = Dts.Variables("PackageName").Value.ToString
Dim sTaskName As String = Dts.Variables("TaskName").Value.ToString
Dim sSubComponent As String = sPackageName & "." & sTaskName
Dim sApplicationName As String = Dts.Variables("ApplicationName").Value.ToString
Dim iAppInstanceID As Integer = _
Convert.ToInt32(Dts.Variables("AppInstanceID").Value)

Dim sMsg As String = "ApplicationName: " & sApplicationName & vbCrLf & _
"AppInstanceID: " & iAppInstanceID.ToString
Dts.Events.FireInformation(1001, sSubComponent, sMsg, "", 0, True)

Dts.TaskResult = ScriptResults.Success
End Sub

The purpose of the script is the Dts.Events.FireInformation call near the end. The first argument for this function is the InformationCode. Depending on the nature and purpose of the SSIS framework, I may or may not enter a value (other than 0) here. TheSubComponent argument is next and I usually construct a string identifying the names of the package and task. The description argument follows, and this contains the message I want to inject into the log.SSISEvents table. The next two arguments are help-related—I usually blank and zero them, respectively. The last argument is FireAgain, and I am uncertain if it does anything (anymore); I always set it to True.

Close the script editor and the Script Task Editor. Connect a precedence constraint from the Log Start of Application Execute SQL task to the Log Application Variables Script task and another precedence constraint from the Log Application Variables Script task to the Get Package Metadata Execute SQL task.

Drag another Script task into the Foreach Child Package Foreach Loop container and rename it Log Package Variables. Open the editor and change the ScriptLanguage to Microsoft Visual Basic 2012. Add the following variables to the ReadOnlyVariables property:

· System::TaskName

· System::PackageName

· User::PkgInstanceID

· User::ChildPackagePath

· User::AppPackageID

Edit the script and place the code shown in Listing A-19 in Sub Main().

Listing A-19. Raising an Information Event from a Script Task

Public Sub Main()

Dim sPackageName As String = Dts.Variables("PackageName").Value.ToString
Dim sTaskName As String = Dts.Variables("TaskName").Value.ToString
Dim sSubComponent As String = sPackageName & "." & sTaskName
Dim sChildPackagePath As String = Dts.Variables("ChildPackagePath").Value.ToString
Dim iAppPackageID As Integer = Convert.ToInt32(Dts.Variables("AppPackageID").Value)
Dim iPkgInstanceID As Integer = _ Convert.ToInt32(Dts.Variables("PkgInstanceID").Value)

Dim sMsg As String = "ChildPackagePath: " & sChildPackagePath & vbCrLf & _
"AppPackageID: " & iAppPackageID.ToString & vbCrLf & _
"PkgInstanceID: " & iPkgInstanceID.ToString
Dts.Events.FireInformation(1001, sSubComponent, sMsg, "", 0, True)

Dts.TaskResult = ScriptResults.Success
End Sub

Close the script editor and the Script Task Editor. Connect a precedence constraint from the Log Start of Package Execute SQL task to the Log Start of Package Execute SQL task.

If you execute Parent.dtsx now, you may get a foreign key constraint error when you try to log the application variables. Why? PkgInstanceID is set to a default value, 0, and there is no 0 row in the log.SSISPkgInstance table. Let’s remedy that now with the following script shown in Listing A-20.

Listing A-20. Adding0 ID Rows to Selected Tables in the SSISConfig Database

/* Add "0" rows */
If Not Exists(Select ApplicationID
From cfg.Applications
Where ApplicationID = 0)
begin
print 'Adding 0 row for cfg.Applications'
Set Identity_Insert cfg.Applications ON
Insert Into cfg.Applications
(ApplicationID
,ApplicationName)
Values
(0
,'SSIS Framework')
Set Identity_Insert cfg.Applications OFF
print '0 row for cfg.Applications added'
end
Else
print '0 row already exists for cfg.Applications'
print ''

If Not Exists(Select PackageID
From cfg.Packages
Where PackageID = 0)
begin
print 'Adding 0 row for cfg.Packages'
Set Identity_Insert cfg.Packages ON
Insert Into cfg.Packages
(PackageID
,PackageFolder
,PackageName)
Values
(0
,'\'
,'parent.dtsx')
Set Identity_Insert cfg.Packages OFF
print '0 row for cfg.Packages added'
end
Else
print '0 row already exists for cfg.Packages'
print ''

If Not Exists(Select AppPackageID
From cfg.AppPackages
Where AppPackageID = 0)
begin
print 'Adding 0 row for cfg.Packages'
Set Identity_Insert cfg.AppPackages ON
Insert Into cfg.AppPackages
(AppPackageID
,ApplicationID
,PackageID
,ExecutionOrder)
Values
(0
,0
,0
,10)
Set Identity_Insert cfg.AppPackages OFF
print '0 row for cfg.AppPackages added'
end
Else
print '0 row already exists for cfg.AppPackages'
print ''

If Not Exists(Select AppInstanceID
From [log].SSISAppInstance
Where AppInstanceID = 0)
begin
print 'Adding 0 row for cfg.Packages'
Set Identity_Insert [log].SSISAppInstance ON
Insert Into [log].SSISAppInstance
(AppInstanceID
,ApplicationID
,StartDateTime
,EndDateTime
,[Status])
Values
(0
,0
,'1/1/1900'
,'1/1/1900'
,'Unknown')
Set Identity_Insert [log].SSISAppInstance OFF
print '0 row for log.SSISAppInstance added'
end
Else
print '0 row already exists for log.SSISAppInstance'
print ''

If Not Exists(Select PkgInstanceID
From [log].SSISPkgInstance
Where PkgInstanceID = 0)
begin
print 'Adding 0 row for cfg.Packages'
Set Identity_Insert [log].SSISPkgInstance ON
Insert Into [log].SSISPkgInstance
(PkgInstanceID
,AppInstanceID
,AppPackageID
,StartDateTime
,EndDateTime
,[Status])
Values
(0
,0
,0
,'1/1/1900'
,'1/1/1900'
,'Unknown')
Set Identity_Insert [log].SSISPkgInstance OFF print '0 row for log.SSISPkgInstance added'
end
Else
print '0 row already exists for log.SSISPkgInstance'
print ''

Now that these event-generating Script tasks are in place, test-execute the Parent.dtsx package and then observe the log.LogEvents table by executing the following T-SQL in SSMS:

Select * From [log].SSISEvents

My results appear as shown in Figure A-18.

9781484200834_FigAppA-18.jpg

Figure A-18. SSIS framework events!

Viewing the log.SSISEvents table in SSMS is disappointing. The data is accurate and SSMS is doing its job, but the user experience could be better for this type of data. Fortunately, SQL Server 2014 ships with SQL Server Reporting Services, which provides a better user experience! Let’s look at building reports to display this data.

Reporting Execution Metrics

SQL Server Reporting Services (SSRS) allows us to create reports that display SSIS framework metadata and metrics in a more user-friendly format. We can add visualizations to the reports that will assist in identifying the status of SSIS applications and SSIS packages.

Open a new instance of SSDT-BI and create a new Report Server project named PublicFrameworkReports_PackageDeployment_2014. In Solution Explorer, right-click Shared Data Source and click Add New Data Source. When the Shared Data Source Properties window displays, set the Name property to SSISConfig and click the Edit button to configure the connection to your instance of the SSISConfig database. When I configure the shared data source, it appears as shown in Figure A-19.

9781484200834_FigAppA-19.jpg

Figure A-19. Configuring the SSISConfig shared data source

You’re now ready to build reports! Let’s begin by creating a report to display application instance data.

Before we jump into report development, we will create supporting objects in the SSISConfig database. Listing A-21 contains the T-SQL script required to build the rpt schema and the rpt.ReturnAppInstanceHeader stored procedure.

Listing A-21. Creating the rpt Schema and rpt.ReturnAppInstanceHeader Stored Procedure

/* rpt schema */
If Not Exists(Select name
From sys.schemas
Where name = 'rpt')
begin
print 'Creating rpt schema'
declare @sql varchar(100) = 'Create Schema rpt'
exec(@sql)
print 'Rpt schema created'
end
Else
print 'Rpt schema already exists.'
print ''

/* rpt.ReturnAppInstanceHeader stored procedure */
If Exists(Select s.name + '.' + p.name
From sys.procedures p
Join sys.schemas s
On s.schema_id = p.schema_id
Where s.name = 'rpt'
And p.name = 'ReturnAppInstanceHeader')
begin
print 'Dropping rpt.ReturnAppInstanceHeader stored procedure'
Drop Procedure rpt.ReturnAppInstanceHeader
print 'Rpt.ReturnAppInstanceHeader stored procedure dropped'
end
print 'Creating rpt.ReturnAppInstanceHeader stored procedure'
go

Create Procedure rpt.ReturnAppInstanceHeader
@ApplicationName varchar(255) = NULL
As

Select a.ApplicationID
,ap.AppInstanceID
,a.ApplicationName
,ap.StartDateTime
,DateDiff(ss,ap.StartDateTime,Coalesce(ap.EndDateTime,GetDate())) As RunSeconds
,ap.Status
From log.SSISAppInstance ap
Join cfg.Applications a
On ap.ApplicationID = a.ApplicationID
Where a.ApplicationName = Coalesce(@ApplicationName,a.ApplicationName)
Order by AppInstanceID desc

go
print 'Rpt.ReturnAppInstanceHeader stored procedure created.'
print ''

Return to SSDT, right-click the Reports virtual folder in Solution Explorer, and click Add New Report. If the welcome screen displays, then click the Next button. On the Select the Data Source screen, select the shared data source named SSISConfig and click the Next button. The Design the Query window displays next; add rpt.ReturnAppInstanceHeader to the Query String textbox and click the Next button. Select Tabular on the Select the Report Type page and click the Next button. When the Design the Table page displays, multiselect all the columns listed in the Available Fields listbox and click the Details button. The Report Wizard will appear as shown in Figure A-20.

9781484200834_FigAppA-20.jpg

Figure A-20. Selecting all available fields as details

Click the Next button. Select a theme on the Choose the Table Style page and click the Next button. On the Completing the Wizard page, enter Application Instance in the Report Name property textbox and click the Finish button.

The SSRS Report Wizard will generate the report, but it doesn’t manage stored procedures effectively. You need to change this so you get the maximum performance out of the reports. Click View image Report Data to display the Report Data sidebar. Expand the Datasets virtual folder. Right-click DataSet1 and click Dataset Properties. When the Dataset Properties window displays, rename the dataset rpt_ReturnAppInstanceHeader (the Dataset Name property does not like periods). Copy rpt.ReturnAppInstanceHeader out of the Query property and click the Stored Procedure option underQuery Type. Paste rpt.ReturnAppInstanceHeader into the Select or Enter Stored Procedure Name drop-down. The Dataset Properties window should appear similar to what is shown in Figure A-21.

9781484200834_FigAppA-21.jpg

Figure A-21. Configuring the dataset to use the rpt.ReturnAppInstanceHeader stored procedure

Click the OK button to close the Dataset Properties window. If you click the Preview tab, the report will prompt you for an application name as shown in Figure A-22.

9781484200834_FigAppA-22.jpg

Figure A-22. Prompting for application name

Type SSISApp1 in the textbox and click the View Report button in the upper-right corner. We don’t want the user to supply an SSIS application each time they use the report, so let’s configure the report parameter named @ApplicationName. Click the Design tab and return to the Report Data sidebar. Expand the Parameters virtual folder. Double-click @ApplicationName to open the Report Parameter Properties window. On the General page, check the Allow Null Value checkbox and change the Select Parameter Visibility option to Hidden. On the Default Values page, select the Specify Values option and click the Add button. A (Null) row will be added to the Value grid, which is what we want. Click the OK button to close the Report Parameter Properties window.

Test the changes by clicking the Preview tab. The report should display all application instance rows stored in the database, as shown in Figure A-23.

9781484200834_FigAppA-23.jpg

Figure A-23. Displaying the application instance data

We do not want to see the 0 rows displayed in these reports. Modify the rpt.ReturnAppinstanceHeader stored procedure to eliminate these records from the returned results by executing the T-SQL shown in Listing A-22.

Listing A-22. Updating the rpt.ReturnAppInstanceHeader Stored Procedure

/* rpt.ReturnAppInstanceHeader stored procedure */
If Exists(Select s.name + '.' + p.name
From sys.procedures p
Join sys.schemas s
On s.schema_id = p.schema_id
Where s.name = 'rpt'
And p.name = 'ReturnAppInstanceHeader')
begin
print 'Dropping rpt.ReturnAppInstanceHeader stored procedure'
Drop Procedure rpt.ReturnAppInstanceHeader
print 'Rpt.ReturnAppInstanceHeader stored procedure dropped'
end
print 'Creating rpt.ReturnAppInstanceHeader stored procedure'
go

Create Procedure rpt.ReturnAppInstanceHeader
@ApplicationName varchar(255) = NULL
As

Select a.ApplicationID
,ap.AppInstanceID
,a.ApplicationName
,ap.StartDateTime
,DateDiff(ss,ap.StartDateTime,Coalesce(ap.EndDateTime,GetDate())) As RunSeconds
,ap.Status
From log.SSISAppInstance ap
Join cfg.Applications a
On ap.ApplicationID = a.ApplicationID
Where a.ApplicationName = Coalesce(@ApplicationName,a.ApplicationName)
And a.ApplicationID > 0
Order by AppInstanceID desc

go
print 'Rpt.ReturnAppInstanceHeader stored procedure created.'
print ''

Refresh the Application Instance report preview and it now appears as shown in Figure A-24.

9781484200834_FigAppA-24.jpg

Figure A-24. Refreshed Application Instance report, sans the 0 row

Color helps identify the state better than most visual cues. To add background color to the data rows, return to the Design tab and select the row that displays data values (the bottom row) in the table. Press the F4 key to display Properties and click on the BackgroundColor property. In the BackgroundColor property’s value drop-down, select Expression. When the Expression window opens, change the text in the Set Expression for: BackgroundColor textbox from No Color (the default) to the following expression:

=Switch(Fields!Status.Value="Success", "LightGreen"
, Fields!Status.Value="Failed", "LightCoral"
, Fields!Status.Value="Running", "Yellow")

When you clean up the report by removing ID columns (which mean little to the user), resetting font sizes, changing text alignment, and adjusting column widths, you can alter the report so it appears as shown in Figure A-25.

9781484200834_FigAppA-25.jpg

Figure A-25. Application Instance—in color!

I call this Operational Intelligence. An enterprise operations person can look at this report and glean lots of information about the current state of enterprise data integration processes.

The Package Instance report is remarkably similar. Let’s begin by adding the stored procedure to the database, as shown in Listing A-23.

Listing A-23. Adding the rpt.ReturnPkgInstanceHeader Stored Procedure

/* rpt.ReturnPkgInstanceHeader stored procedure */
If Exists(Select s.name + '.' + p.name
From sys.procedures p
Join sys.schemas s
On s.schema_id = p.schema_id
Where s.name = 'rpt'
And p.name = 'ReturnPkgInstanceHeader')
begin
print 'Dropping rpt.ReturnPkgInstanceHeader stored procedure'
Drop Procedure rpt.ReturnPkgInstanceHeader
print 'Rpt.ReturnPkgInstanceHeader stored procedure dropped'
end
print 'Creating rpt.ReturnPkgInstanceHeader stored procedure'
go

Create Procedure rpt.ReturnPkgInstanceHeader
@AppInstanceID int
As

SELECT a.ApplicationName
,p.PackageFolder + p.PackageName As PackagePath
,cp.StartDateTime
,DateDiff(ss,
cp.StartDateTime,
Coalesce(cp.EndDateTime,GetDate())) As RunSeconds
,cp.Status
,ai.AppInstanceID
,cp.PkgInstanceID
,p.PackageID
,p.PackageName
FROM log.SSISPkgInstance cp
Join cfg.AppPackages ap
on ap.AppPackageID = cp.AppPackageID
Join cfg.Packages p
on p.PackageID = ap.PackageID
Join log.SSISAppInstance ai
on ai.AppInstanceID = cp.AppInstanceID
Join cfg.Applications a
on a.ApplicationID = ap.ApplicationID
WHERE ai.AppInstanceID = Coalesce(@AppInstanceID,ai.AppInstanceID)
And a.ApplicationID > 0
Order By cp.PkgInstanceID desc
go
print 'Rpt.ReturnPkgInstanceHeader stored procedure created.'
print ''

In SSDT, add a new report named Package Instance just like you added the Application Instance report. Make sure you use the rpt.ReturnPkgInstanceHeader stored procedure. To get the Report Wizard to recognize a query that expects parameters, you need to add default parameter values on the Design the Query page. My Query String textbox reads as follows:

exec rpt.ReturnPkgInstanceHeader NULL

This allows the query builder to locate the columns list returned from the stored procedure (which is what the Report Wizard needs to continue). Once the report is built, remember to first update the dataset, then the report parameter, as you did for the Application Instance report. One cool thing about this particular design is that we can reuse the expression for BackgroundColor on the data rows. Once complete, the Package Instance report appears, as shown in Figure A-26.

9781484200834_FigAppA-26.jpg

Figure A-26. The Package Instance report

Package instances are “children” of application instances. To reflect that relationship, return to the Application Instance report and add a column to the table to contain Packages links. Enter Packages in the column header and as text in the data cell. Right-click the data cell and click Text Box Properties. On the Font page, change the font color to Blue and set the Effects property to Underline. On the Action page, select the Go to Report option for the Enable as an Action property and set the Specify a Report property to Package Instance. In the Use These Parameters to Run the Report grid, click the Add button and map the AppInstanceID parameter to the [AppinstanceID] value. Click the OK button to close the Text Box Properties Editor.

Click the Preview tab to display the Application Instance report. Select one of the Packages” links to navigate to the Package Instance report that will contain only the package instances related to that particular application instance. The Package Instance report should appear similar to the Package Instance report displayed in Figure A-27.

9781484200834_FigAppA-27.jpg

Figure A-27. Package instances for a single application instance

Building the reports in this fashion makes sense. The Application Instance report becomes a “gateway” for the Package Instance report—a “dashboard,” if you will. More in a bit …

Let’s turn our attention to the error log data. To retrieve it, use the T-SQL script shown in Listing A-24.

Listing A-24. Building the rpt.ReturnErrors Stored Procedure

/* rpt.ReturnErrors stored procedure */
If Exists(Select s.name + '.' + p.name
From sys.procedures p
Join sys.schemas s
On s.schema_id = p.schema_id
Where s.name = 'rpt'
And p.name = 'ReturnErrors')
begin
print 'Dropping rpt.ReturnErrors stored procedure'
Drop Procedure rpt.ReturnErrors
print 'Rpt.ReturnErrors stored procedure dropped'
end
print 'Creating rpt.ReturnErrors stored procedure'
go

Create Procedure rpt.ReturnErrors
@AppInstanceID int
,@PkgInstanceID int = NULL
As

Select
a.ApplicationName
,p.PackageName
,er.SourceName
,er.ErrorDateTime
,er.ErrorDescription
From log.SSISErrors er
Join log.SSISAppInstance ai
On ai.AppInstanceID = er.AppInstanceID
Join cfg.Applications a
On a.ApplicationID = ai.ApplicationID
Join log.SSISPkgInstance cp
On cp.PkgInstanceID = er.PkgInstanceID
And cp.AppInstanceID = er.AppInstanceID
Join cfg.AppPackages ap
On ap.AppPackageID = cp.AppPackageID
Join cfg.Packages p
On p.PackageID = ap.PackageID
Where er.AppInstanceID = Coalesce(@AppInstanceID, er.AppInstanceID)
And er.PkgInstanceID = Coalesce(@PkgInstanceID, er.PkgInstanceID)
Order By ErrorDateTime Desc
go
print 'Rpt.ReturnErrors stored procedure created.'
print ''

The T-SQL in Listing A-24 constructs the rpt.ReturnErrors stored procedure, which will supply data to a new report. Let’s build that report now in SSDT.

Add a new report named Errors to the SSISConfig2012Reports solution. Use the rpt.ReturnErrors stored procedure as the source. Remember to update the dataset and both report parameters: AppinstanceID and PkgInstanceID.

On the table’s data row, edit the BackgroundColor property, adding the following expression:

=Iif(RowNumber(Nothing) Mod 2 = 0,"White","WhiteSmoke")

We are not coloring the background of each cell here to reflect status; the report would be filled with LightCoral if we did so. But we do need to break up these rows visually, so let’s use subtle shading to help keep the eyes moving across the row at 2:15 AM some dark and dreary morning.

Open the Application Instance report. Right-click on the Status data field and click Text Box Properties. Navigate to the Font page and click the f(x) button beside the Color property drop-down. In the Set Expression for: Color textbox, enter the following expression:

=Iif(Fields!Status.Value="Failed", "Blue", "Black")

If the status is "Failed", then this expression will change the color of the Status text to Blue. Click the f(x) button beside the Effects property drop-down. In the Set Expression for: TextDecoration textbox, add the following expression:

=Iif(Fields!Status.Value="Failed", "Underline", "Default")

This expression will decorate a "Failed" status with an underline. This and the previous property combine to make "Failed" status appear as a hyperlink. Where does the hyperlink take us? Let’s configure that property now. Navigate to the Action page and select the Go to Report option for the Enable as an Action property. Click the f(x) button beside the Specify a Report drop-down and add the following expression to the Set Expression for: ReportName textbox:

=Iif(Fields!Status.Value="Failed", "Errors", Nothing)

Click the Add button and map the AppInstanceID parameter name to the [AppInstanceID] parameter value. Click the f(x) button in the Omit column of the parameter mapping and add the following expression to the Set Expression for: Omit textbox:

=Iif(Fields!Status.Value="Failed", False, True)

The two previous property settings configure the Action property of the Status value. If the Status is "Failed", clicking the word Failed, which will appear to be a hyperlink, will cause the Errors report to display. When it displays, it will only show those error rows associated with the application instance displayed in that row of data.

Let’s test it! When I run the Application Instance report, it now appears as shown in Figure A-28.

9781484200834_FigAppA-28.jpg

Figure A-28. The Application Instance report, including Status and Packages decoration

Clicking one of the Failed hyperlinks takes us to the Errors report for that application instance. The report should appear similar to that shown in Figure A-29.

9781484200834_FigAppA-29.jpg

Figure A-29. Displaying an error

Quickly isolating the source of an error in an SSIS package is one way to improve overall operational efficiency. These reports, working in tandem, facilitate efficient root cause analysis.

The Events report is very similar to the Errors report. The T-SQL script for creating the rpt.ReturnEvents" stored procedure is shown in Listing A-25.

Listing A-25. Building the rpt.ReturnEvents Stored Procedure

/* rpt.ReturnEvents stored procedure */
If Exists(Select s.name + '.' + p.name
From sys.procedures p
Join sys.schemas s
On s.schema_id = p.schema_id
Where s.name = 'rpt'
And p.name = 'ReturnEvents')
begin
print 'Dropping rpt.ReturnEvents stored procedure'
Drop Procedure rpt.ReturnEvents
print 'Rpt.ReturnEvents stored procedure dropped'
end
print 'Creating rpt.ReturnEvents stored procedure'
go

Create Procedure rpt.ReturnEvents
@AppInstanceID int
,@PkgInstanceID int = NULL
As

Select
a.ApplicationName
,p.PackageName
,ev.SourceName
,ev.EventDateTime
,ev.EventDescription
From log.SSISEvents ev
Join log.SSISAppInstance ai
On ai.AppInstanceID = ev.AppInstanceID
Join cfg.Applications a
On a.ApplicationID = ai.ApplicationID
Join log.SSISPkgInstance cp
On cp.PkgInstanceID = ev.PkgInstanceID
And cp.AppInstanceID = ev.AppInstanceID
Join cfg.AppPackages ap
On ap.AppPackageID = cp.AppPackageID
Join cfg.Packages p
On p.PackageID = ap.PackageID
Where ev.AppInstanceID = Coalesce(@AppInstanceID, ev.AppInstanceID)
And ev.PkgInstanceID = Coalesce(@PkgInstanceID, ev.PkgInstanceID)
Order By EventDateTime Desc
go
print 'Rpt.ReturnEvents stored procedure created.'
print ''

Add a new report named Events, use the rpt.ReturnEvents stored procedure, and remember to configure the dataset and report parameters. Add the alternating row shading I demonstrated in the Errors report. The same expression will work in the Events report:

=Iif(RowNumber(Nothing) Mod 2 = 0,"White","WhiteSmoke")

Return to the Application Instance report and add another column to the data table. Label it Events and set the data grid value to Events as well. Open the Text Box Properties window for the Events data field and navigate to the Font page. Change the Color property to Blue and the Effects property to Underline. On the Actions page, change the Enable as an Action property to “Go to Report and the Specify a Report drop-down to Events. Add a parameter mapping and map the AppInstanceID parameter name to the [AppinstanceID] parameter value. Click the OK button to close the Text Box Properties Editor. Let’s test it!

The Application Instance report now appears, as shown in Figure A-30.

9781484200834_FigAppA-30.jpg

Figure A-30. The new and improved Application Instance report

Clicking the Events hyperlink takes us to the Events report, which should be similar the report shown in Figure A-31.

9781484200834_FigAppA-31.jpg

Figure A-31. The Events report for an application instance

This latest round of reports and updates to the Application Instance report reinforce its status as the Operational Intelligence Dashboard. Similar changes can be made to the Package Instance report. Let’s add the Failed link functionality and the Events column now.

On the Package Instance report, open the Text Box Properties Editor for the Status data field. As I did for the Status data field in the Application Instance report, navigate to the Font page and click the f(x) button beside the Color property drop-down. In the Set Expression for: Color textbox, enter the following expression:

=Iif(Fields!Status.Value="Failed", "Blue", "Black")

This expression will change the color of the status text to blue if the status is "Failed". Click the f(x) button beside the Effects property drop-down. In the Set Expression for: TextDecoration textbox, add the following expression:

=Iif(Fields!Status. Value=“Failed”, “Underline”, “Default”)As with the Application Instance report, this expression will decorate a Failed status with an underline. This and the previous property combine to make the Failed status appear as a hyperlink. Where does the hyperlink take us? Let’s configure that property now. Navigate to the Action page and select the Go to Report option for the Enable as an Action property. Click the f(x) button beside the Specify a Report” drop-down and add the following expression to the Set Expression for: ReportName textbox:

=Iif(Fields!Status.Value="Failed", "Errors", Nothing)

Click the Add button and map the AppInstanceID parameter name to the [AppInstanceID] parameter value. Click the Add button again and map the PkgInstanceID parameter name to the [PkgInstanceID] parameter value. Click the f(x) button in the Omit column of each parameter mapping and add the following expression to each Set Expression for: Omit textbox:

=Iif(Fields!Status.Value="Failed", False, True)

As with the Application Instance report, the two previous property settings configure the Action property of the Status value. If the status is "Failed", clicking the word Failed, which will appear to be a hyperlink, will cause the Errors report to display. When it displays, it will only show those error rows associated with the application instance displayed in that row of data.

Let’s test it! When we run the Package Instance report, it now appears as shown in Figure A-32.

9781484200834_FigAppA-32.jpg

Figure A-32. “Failed hyperlinks” for the Package Instance report

Clicking a Failed link takes us to the Errors report for that package instance. Cool. Now let’s add the Events column to the Package Instance report. Add a column with the header and data field hard-coded Events. Open the Text Box Properties window for the Events data field and navigate to the Font page. Set the Color property to Blue and the Effects property to Underline. Navigate to the Action page and set the Enable as an Action property to Go to Report. Select the Events report from the Specify a Report drop-down and click the Add button twice to map two parameters. Map the AppInstanceID parameter name to the [AppInstanceID] parameter value and the PkgInstanceID parameter name to the [PkgInstanceID] parameter value. Close the Text Box Properties window and click the Preview tab to test. The Package Instance report should appear as shown in Figure A-33.

9781484200834_FigAppA-33.jpg

Figure A-33. The finished Package Instance report

Clicking the Events link will take me to the Events report and display only the events for the package instance on the referenced row.

To wrap it up, you can start at the Application Instance report; it is on the dashboard. Click the Packages link to view all the SSIS child packages that executed as part of the selected SSIS application instance. From there, drill into the Errors report and observe the errors that caused a package to fail, or view all of the events recorded by the OnInformation event handler for the selected package instance on the Events report. You can reach all errors and events for an SSIS application instance from the Application Instance report, as well.

Conclusion

This example isn’t an exhaustive example of an SSIS framework, but it demonstrates the utility of patterns-based data integration development using SSIS. The sample framework provides repeatable, metadata-driven SSIS execution without leaving the SSIS and SQL Server database domains. Monitoring is provided by a set of SQL Server Reporting Services reports driven by stored procedures that read metadata automatically captured by the framework’s Parent.dtsx SSIS package. Zero lines of code are required in child packages to capture error and event information, and this information is logged centrally in a consistent format, which makes it perfect for reporting.