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 we assure you, it is not. Execution control requires a working knowledge of the Integration Services Runtime API. An understanding of the subtleties of tight- and loose-coupling is not merely helpful; it can make or ruin your day (or data integration solution).
SSIS monitoring changed with the release of SSIS 2012. The SSIS Catalog, as discussed in Chapter 2, provided built-in support and instrumentation.
Instrumentation is a term used by engineers to describe devices—called “instruments”—placed into or near machinery or processes to measure pertinent indicators.
Why would anyone need an SSIS Framework if SSIS 2012 includes the SSIS Catalog? That is an excellent question. The SSIS 2012 Catalog utilizes the Project Deployment Model—the default for SSIS projects developed in SQL Server Data Tools (SSDT). But SSIS 2012 also includes the Package Deployment Model to support upgrading legacy SSIS projects to SSIS 2012. There are use cases for using the SSIS Catalog for execution and monitoring. There are also use cases for using a serial 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, we will walk you through designing and building a serial SSIS Execution and Monitoring Framework that will work with SSIS 2012’s Package Deployment Model, complete with a SQL Server Reporting Services solution. Building an SSIS Framework is an advanced task, but we will build it from the ground up, using some 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. Create a new SSIS Solution and Project named “SSISConfig2012.” 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 2010.” Click the ellipsis in the ReadOnlyVariables property value textbox and add the System::TaskName and System::PackageName variables. Open the script editor and add the following code in Sub Main().
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
Listing A-1. Sub Main From Who Am I? Script Task in Child1.dtsx Package
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. Copy and paste this script task into any SSIS package and it will perform the same way each time.
Close the editor and execute the Child1.dtsx package in the SSDT debugger. When we execute the package, we see a message box similar to the one shown in Figure A-1.
Figure A-1. Message Box from Child1.dtsx
Child.dtsx will be our first test package. We will use Child1.dtsx going forward to conduct tests of our SSIS Execution and Monitoring Framework.
Before we proceed, let’s 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 in Figure A-2.
Figure A-2. Converting the Project to Package Deployment Model
You will need to click the OK button on the dialog to acknowledge you understand that this will change the features available to use in SSIS. Once the conversion is complete, you will see a result pane informing you the project Child1.dtsx was converted to 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.
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 dropdown 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 your SSISConfig2012 project and select Child1.dtsx. Click the OK button to close the File Connection Manager editor and OK again to close the Execute Package Task editor. Note the File Connection Manager that was created during 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, then 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. We can improve upon the Parent-Child with a little metadata. How? We’re 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 dropdown, as shown in Figure A-4.
Figure A-4. The File Connection Manager Property Expressions Editor
Click the ellipsis in the Expression textbox beside the ConnectionString property. 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.
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. We 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.
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.
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
Listing A-2. Creating the SSISConfig Database
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, let’s build a table to hold SSIS package metadata. Listing A-3 contains T-SQL for such a 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 ''
Listing A-3. Building the Cfg Schema and Cfg.Packages Table
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 Programmers Interface, or DPI. Not an Applications Programmers Interface, or API because databases are not applications. Let’s begin building the SSISConfig DPI with a stored procedure to load data into the cfg.Packages table, as shown in Listing A-4.
/* 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 ''
Listing A-4. The Cfg.AddSSISPackages Stored Procedure
Note the cfg.AddSSISPackage stored procedure returns an integer value that represents the identity column—PackageID—from the cfg.Packages table. We will use this integer value later. Once this stored procedure is in place, we can use the T-SQL script in Listing A-5 to add the packages in our project.
/* Variable Declaration */
declare @PackageFolder varchar(255) = 'F:SSIS 2012 Design PatternsSSISConfig2012SSISConfig2012'
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
Listing A-5. Adding our Packagesto the Cfg.Packages Table
We now have enough to test the next step of our Execution and Monitoring SSIS Framework so let’s return to SSDT. Add an Execute SQL Task to the Control Flow and rename it Get Package Metadata. Open the editor and change the ResultSet property to “Single row.” Change the ConnectionType property to “ADO.Net.” Click the dropdown 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. Execute the Parent.dtsx package to test it. What happens? The 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 specified order. Let’s start by adding a couple tables and supporting stored procedures to the SSISConfig database.
First, create a table named cfg.Applications, and a stored procedure to add them, in SSISConfig using the T-SQL in Listing A-6.
/* 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 ''
Listing A-6. Building cfg.Applications and cfg.AddSSISApplication
Note the cfg.AddSSISApplication stored procedure returns an integer value that represents the identity column—ApplicationID—from the cfg.Applications table. We will use this integer value later. Let’s add an SSIS Application to the table using the following T-SQL in Listing A-7.
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 ''
Listing A-7. Addingan SSIS Application
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 easy to determine that the relationship between 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 for choosing patterns-based development: code reusability, specifically in reference to the SSIS Package code. Consider the Archive File Pattern from the end of the Flat File Design Patterns chapter. 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. From this, we gather that the relationship between SSIS Packages and SSIS Applications is also one-to-many. If you do the math, these relationships combine to create a many-to-many relationship between the Applications and Packages tables. This means we need a bridge or resolver table between them to create mappings between SSIS Applications and SSIS Packages.
We 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.
/* 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 '
Listing A-8. Creating Cfg.AppPackages and Cfg.AddSSISApplicationPackage
To create the mappings between SSIS Applications and SSIS Packages, we need the IDs of each. Executing the following queries returns the information we need:
Select * from cfg.Applications
Select * from cfg.Packages
We 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. We use the T-SQL script shown in Listing A-9 to accomplish the mapping.
declare @ExecutionOrder int = 10
declare @ApplicationID int = 1
declare @PackageID int = 1
declare @ApplicationName varchar(255) = 'SSISApp1'
declare @PackageFolder varchar(255) = 'F:SSIS 2012 Design PatternsSSISConfig2012SSISConfig2012'
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)
Listing A-9. Couplingthe “Child1” and “Child2” SSIS Packages to the “SSISApp1” SSIS Application
One note about the T-SQL script shown in Listing A-9. This is not the way we would load this metadata into Production (or even Test) environments. We would not “re-declare” the ApplicationName, PackageFolder, PackageName, ApplicationID, and PackageID variables; rather, we would reuse these values from the previous T-SQL scripts. We alluded to this earlier when we mentioned we will use the ApplicationID and PackageID values later. We 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? Let’s 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.
/* 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
/*
(c) 2011,2012 Linchpin People, LLC
*/
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 ''
Listing A-10. Creating the Cfg.GetSSISApplication Stored Procedure
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 returned are:
Also not the SSIS Packages are returned in the order specified by ExecutionOrder.
We can 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.
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 Packages metadata associated with the SSIS Application named “SSISApp1” in the SSISConfig database.
That was a lot of work! Fortunately, most of it will not need to be repeated. When we want to add SSIS Packages and associate them with SSIS Applications in the future, our script will look like the T-SQL shown in Listing A-11.
Use SSISConfig
go
/* Variable Declaration */
declare @PackageFolder varchar(255) = 'F:SSIS 2012 Design PatternsSSISConfig2012SSISConfig2012'
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)
Listing A-11. The Complete T-SQL Script for Adding “SSISApp1” and Associated SSIS Packages
Retrieving SSIS Applications in SSIS
Return to SQL Server Data Tools 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 dropdown 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 “String.” For the Value property, enter “SSISApp1” without the double-quotes. Your Add Variable window should appear as shown in Figure A-8.
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 from “User::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 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” dropdown, 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 dropdown 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:
- Execute the cfg.GetSSISApplications stored procedure in the SSISConfig database, passing it the value contained in the ApplicationName variable.
- Push the full result set returned by the stored procedure execution into an SSIS Object Variable named “Packages.”
- Configure a Foreach Loop to point at each row stored in the “Packages” variable in the order returned.
- 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 we do this, we get two message boxes. The first states “I am Child1” and the second appears as shown in Figure A-9.
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.
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, 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 to hold execution metrics about SSIS Application instances; and Log.SSISPkgInstance to hold 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. We 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. Execute the T-SQL script shown in Listing A-12 to build the 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 ''
Listing A-12. Building the Application Instance Tables and Stored Procedures
Return to SSDT and let’s 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 the 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::ApplicationName SSIS variable to the ApplicationName parameter for the log.LogStartOfApplication stored procedure. On the Result Set page, add a new Result named “0” and map it to a new Int32 variable named “AppInstanceID.” 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 SSISConfig connection 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 we just accomplish? We added SSIS Application Instance logging to the Control Flow of the Parent.dtsx SSIS Package. Execute Parent.dtsx in the SSDT debugger to test.
Once execution completes, execute the following query to observe the logged results:
Select * From [log].SSISAppInstance
When we execute this query, we get the results that are shown in Figure A-10.
Figure A-10. Observing the Results of Querying the Application Instance Log
What happens when an SSIS Application fails? We want to update the log.SSISAppInstance row with an EndDateTime and set the Status to “Failed.” For this, we will us 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 dropdown, select “Parent”; in the Event Handler dropdown, select “OnError” as shown in Figure A-11.
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. We could walk you through building another Execute SQL Task to log the SSIS Application failure; however, it’s easier and simpler to 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. Change the name to “Log Application Failure” and the SQLStatement property to log.LogApplicationFailure.
We are now ready to test, but we have no real way to test the application failure unless we modify a package—and that just seems tragic. We are likely going to need to test errors after this, too. So why not build an ErrorTest.dtsx SSIS package and add it to our SSIS Application? We like this plan. Let’s do it!
Create a new SSIS Package and rename it “ErrorTest.dtsx.” Add a Script Task to the Control Flow and rename it “Succeed or Fail?” Open the editor and add the “System::TaskName” and “System::PackageName” variables to the ReadOnlyVariables property. Open the Script Editor and add the code shown in Listing A-13 to Sub Main().
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
Listing A-13. Code to Succeed or Fail SSIS Package
Let’s unit-test by executing ErrorTest.dtsx in the SSDT debugger, as shown in Figure A-12.
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.
/*ErrorTest.dtsx */
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)
Listing A-14. Append this T-SQL Script to Listing A-11 to Add the ErrorTest.dtsx SSIS Package to the “SSISApp1” SSIS Application
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.
Figure A-13. I Have Mixed Emotions About Successful OnError Event Handlers.
A couple successful and failed executions later, and the log.SSISAppInstance table contains the rows shown in Figure A-14.
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.
/* 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 ''
Listing A-15. Building the Package Instance Logging Table and Stored Procedures
The log.SSISPkgInstance table will hold the SSIS Package Instance data. Log.LogStartofPackage inserts a row into the Package Instance table; log.LogPackageSuccess updates the row with an EndDateTime and a “Success” status, while log.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 the SSISConfig 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 Int32 variable named “PkgInstanceID.” 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 to “log.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, the SQLStatement property to “log.LogPackageFailure,” 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 (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 we check the Application and Package Instance tables, the results should appear as shown in Figure A-15.
Figure A-15. Examining the Application and Package Instance Logs
We can tell by examining the Application Instance and Package Instance log tables that AppInstanceID 5 started at 5:24:28 PM 25 Apr 2012. We can also see three SSIS packages—with PkgInstanceID’s 1, 2, and 3—were executed as part of the SSIS Application. Each package succeeded, and the SSIS Application succeeded as well. We also know AppInstanceID 6 started at 5:24:46 PM 25 Apr 2012 and executed PkgInstanceID’s 4, 5, and 6. PkgInstanceID’s 4 and 5 succeeded, but PkgInstanceID 6 failed; failing the SSIS Application.
Cool? Cool. Let’s move to Error and Event 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 we 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 we built very large ETL solutions for US 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 isn’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 we can capture and record any error at the parent package. Even better, it means we 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.
/* 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 ''
Listing A-16. Building the Error Logging Table and Stored Procedure
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 a PkgInstanceID 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).
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 we 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—“bubbles”—inside the SSIS package execution stack. When the event arrives at the Parent.dtsx package in our 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, we will call the log.LogError stored procedure to populate the log.SSISErrors table. In SSDT, return to the Parent.dtsx package’s On Error event handler 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. We are 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.
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
Your results should appear similar to those shown in Figure A-17.
Figure A-17. Error Metadata in the Log.SSISErrors Table
As you can see from the preceding image (and hopefully your own code at this point), error logging can make troubleshooting SSIS issues much simpler.
Event logging is very similar to error logging in SSIS. Part of the reason is 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.
/* 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 ''
Listing A-17. Building the Event Logging Table and Stored Procedure
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 dropdown 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 read “log.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 us.
If we execute Parent.dtsx now to test the new Event logging functionality, then we don’t see any events logged. Bummer. How do we 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, for example. You can also inject OnInformation events into the execution stream using a Script Task.
We like to include Script Tasks that summarize the information we have about a 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 2010.” 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().
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
Listing A-18. Raising an Information Event from a Script Task
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, we may or may not enter a value (other than 0) here. The SubComponent argument is next and we usually construct a string identifying the names of the package and task. The description argument follows and this contains the message we want to inject into the log.SSISEvents table. The next two arguments are help-related—we usually blank and zero them, respectively. The last argument is FireAgain, and we are uncertain if it does anything (anymore); we 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 2010.” 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().
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
Listing A-19. Raising an Information Event from a Script Task
If you execute Parent.dtsx now, you will 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.
/* 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 ''
Listing A-20. Adding “0” ID Rows to Selected Tables in the SSISConfig Database
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.
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 2012 ships with SQL Server Reporting Services, which provides a better user experience! Let’s look at building reports to display this data.
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.
To begin, open a new instance of SQL Server Data Tools (SSDT) and create a new Report Server project named “SSISConfig2012Reports.” 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 we configure the Shared Data Source, it appears as shown in Figure A-19.
Figure A-19. Configuring the SSISConfig Shared Data Source
We are now ready to build reports! Let’s begin by creating a report to display Application Instance data.
Before we jump into report development, let’s 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.
/* 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 ''
Listing A-21. Creating the Rpt Schema and Rpt.ReturnAppInstanceHeader Stored Procedure
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” (without the double-quotes) 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, multi-select all the columns listed in the Available Fields listbox and click the “Details” button. Your Report Wizard will appear as shown in Figure A-20.
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. We need to change this so we get the maximum performance out of the reports. Click View → 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 in the Query Type property. Paste “rpt.ReturnAppInstanceHeader” into the “Select or enter stored procedure name” dropdown. Your Dataset Properties window should appear similar to what is shown in Figure A-21.
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.
Figure A-22. Prompting for Application Name
Supply “SSISApp1” to the textbox (without the double-quotes) 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.” Return to the Report Data sidebar and 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.
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.
/* 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 ''
Listing A-22. Updating the Rpt.ReturnAppInstanceHeader Stored Procedure
Refresh the Application Instance report Preview and it now appears as shown in Figure A-24.
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 dropdown, 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")
By cleaning up the report by resetting font sizes, changing text alignment, and adjusting column widths, our report appears as shown in Figure A-25:
By cleaning up the report by removing ID columns (which mean little to the user), resetting font sizes, changing text alignment, and adjusting column widths, our report appears as shown in Figure A-25.
Figure A-25. Application Instance—in Color!
We 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.
/* 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.PackageID = cp.AppPackageID
Join cfg.Packages p
on p.PackageID = ap.AppPackageID
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 ''
Listing A-23. Adding the Rpt.ReturnPkgInstanceHeader Stored Procedure
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.
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. Your Package Instance report should appear similar to the Package Instance report displayed in Figure A-27.
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, let’s use the T-SQL script shown in Listing A-24.
/* 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 ''
Listing A-24. Building the Rpt.ReturnErrors Stored Procedure
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 we 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 dropdown. 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 blue. Click the f(x) button beside the Effects property dropdown. In the “Set expression for: TextDecoration” textbox, add the following expressiocv n:
=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” dropdown 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 we run the Application Instance report, it now appears as shown in Figure A-28.
Figure A-28. The Application Instance Report, Including Status, and Packages Decoration
Clicking one of the “Failed” hyperlinks takes me to the Errors report for that Application Instance. Your report should appear similar to that shown in Figure A-29.
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.
/* 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 ''
Listing A-25. Building the Rpt.ReturnEvents Stored Procedure
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 we 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 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” dropdown 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.
Figure A-30. The New and Improved Application Instance Report
Clicking the Events hyperlink takes us to the Events report, which should be to similar the report shown in Figure A-31.
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 for the “Status” data field. As we 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 dropdown. 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 blue if the Status is “Failed.” Click the f(x) button beside the Effects property dropdown. 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 “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” dropdown 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.
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 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” dropdown 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. Your Package Instance report should appear as shown in Figure A-33.
Figure A-33. The finished Package Instance Report
Clicking the “Events” link will take us 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. You can click the Packages “link” to view all the SSIS Child Packages that executed as part of the selected SSIS Application Instance. From there, you can drill into the Errors report and observe the errors that caused a Package to fail, or you can 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.
Summary
This isn’t an exhaustive example of an SSIS Framework, but it does demonstrate the utility of patterns-based data integration development using SSIS. This framework provides repeatable, metadata-driven SSIS execution without leaving the SSIS and SQL Server database realms. 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.