Adding an SSIS Application to SSIS Framework Community Edition

Note: This post was originally posted at SQLBlog.

I’m writing more about SSIS Framework Community Edition because lots of people are using this free, open-source utility to execute collections of SSIS packages – called SSIS Applications – in their enterprises. Earlier I wrote Schedule an SSIS Application with SSIS Framework Community Edition, a post that describes using SQL Server Agent to schedule the execution of an SSIS Application. In this post I’m going to cover adding SSIS Application metadata to SSIS Framework Community Edition. But first, I can hear some of you thinking, …

“What is an SSIS Application, Andy?”

I’m glad you asked! An SSIS Application is a collection of SSIS packages configured to execute in some order. Do you need an SSIS Framework to execute SSIS packages in some order? Nope. There are lots of ways to accomplish SSIS package execution management. Some of those ways, though, are the data integration equivalent of storing your enterprise data in Excel. ‘Nuff said.

The SSIS Framework uses three tables to organize SSIS Packages into SSIS Applications:

custom.Applications
custom.Packages
custom.ApplicationPackages

The design of the custom.Applications table is fairly straightforward with two columns, ApplicationID and ApplicationName:

The design of the custom.Packages table is also pretty straightforward with SSIS Catalog Package storage metadata columns:

A Cardinal Rule

Let’s consider cardinality for a moment. If an SSIS Application is a collection of SSIS Packages, then the cardinality between applications and packages is one-to-many. But… one of the reasons for using an SSIS Framework is the SSIS Execute Package Task requires SSIS Packages exist in the SSIS Project (for SSIS Catalog execution using Project Deployment Model). Are there ways around this? Sure (please see my earlier comment about storing enterprise data in Excel…).

Wouldn’t it be awesome if you could store SSIS packages anywhere in the SSIS Catalog and then re-use them as part of several SSIS Applications? Yes. Yes it would. This is one advantage of using an SSIS Framework; it facilitates code re-use. Imagine you have an SSIS Package named ArchiveFile.dtsx that, well, archives flat files once their data has been loaded. You could add that package to every SSIS Project. If you go that route, what happens if you want to update ArchiveFile.dtsx? Or, God forbid, what do you do if you find a bug in ArchiveFile.dtsx? I reckon you open all those SSIS Projects, make the change, update source control, and then redeploy ArchiveFile.dtsx to all those SSIS Catalog projects (… and remember, you’d have to redeploy all packages in the projects prior to SSIS 2016…). Right?

Yea… no. Instead, you find (or build) a better mousetrap: an SSIS Framework.

Considering cardinality in a framework that facilitates code re-use, you realize the possibility that some SSIS Packages can be part of many SSIS Applications. The cardinality between SSIS Applications and SSIS Packages is really many-to-many. A bridge, or resolver, table is needed to manage the many-to-many relationship and I call this table custom.ApplicationPackages:

Each record represents an SSIS Package that runs as part of an SSIS Application. ApplicationPackageID is an identity column that manages this distinction. ApplicationID identifies the SSIS Application, PackageID identifies the SSIS Package, and ExecutionOrder defines the relative execution order of the SSIS Package in the SSIS Application. FailApplicationOnPackageFailure is an execution attribute. Technically ExecutionOrder is also an execution attribute. Why store this metadata at the ApplicationPackage? Let’s return to ArchiveFile.dtsx for a moment.

What if my data integration process is made up of one SSIS Package that loads data from a CSV file? The first SSIS Package in my SSIS Application will be the loader. The second SSIS Package in my SSIS Application will be ArchiveFile.dtsx.

But what if my data integration process contains several SSIS Packages? In that case, ArchiveFile.dtsx may execute 10^th. Or 20^th. Or even more than once. Remember, it’s the sameSSIS Package – ArchiveFile.dtsx. It’s just being called many times.

Why attribute FailApplicationOnPackageFailure to the ApplicationPackage? I may desire to continue SSIS Application execution if ArchiveFile.dtsx fails in SSIS Application A. But I may equally desire to stop the execution of SSIS Application B if ArchiveFile.dtsx fails. I hope this is making sense. Execution attributes are important.

Adding Framework Metadata

Adding SSIS Application metadata is straightforward because the design of the custom.Applications table is straightforward. When I write T-SQL for metadata operations, I like for the code to tell me what it’s doing. It’s awesome to know there was an error (well, not awesome exactly) or that the command completed successfully. But I’d like more information. Please. So I write T-SQL that is re-executable (idempotent). The T-SQL not only manages state, it informs me of state.

Adding an SSIS Application can be accomplished using the following T-SQL statement:

/* Application: Load AdventureWorks2014 Stage */
print 'Application: Load AdventureWorks2014 Stage'
declare @appID int = (Select ApplicationName
From custom.Applications
Where ApplicationName = 'Load AdventureWorks2014 Stage')

If(@appID Is NULL)
begin
print ' - Creating Load AdventureWorks2014 Stage SSIS Application'
Insert Into custom.Applications
(ApplicationName)
Output inserted.ApplicationID As ApplicationID
Values
('Load AdventureWorks2014 Stage')
print ' - Load AdventureWorks2014 Stage SSIS Application created'
end
Else
begin
print ' - Load AdventureWorks2014 Stage SSIS Application already exists.'
Select @appID As ApplicationID
end

Why return the ApplicationID value? ApplicationID is one of the fields in ApplicationPackage.

Adding SSIS Package Framework metadata is similar:

/* Package: AdventureWorks2014_Stage_dbo_AWBuildVersion_TruncateAndLoad.dtsx */
print 'Package: AdventureWorks2014_Stage_dbo_AWBuildVersion_TruncateAndLoad.dtsx'
declare @pkgID int = (Select PackageName
From custom.Packages
Where PackageName = 'AdventureWorks2014_Stage_dbo_AWBuildVersion_TruncateAndLoad.dtsx'
And ProjectName = 'AdventureWorks2014_Stage_Loader'
And FolderName = 'Stage')

If(@pkgID Is NULL)
begin
print ' - Adding AdventureWorks2014_Stage_dbo_AWBuildVersion_TruncateAndLoad.dtsx SSIS Package'
Insert Into custom.Packages
(FolderName
,ProjectName
,PackageName)
Output inserted.PackageID As PackageID
Values
('Stage'
, 'AdventureWorks2014_Stage_Loader'
, 'AdventureWorks2014_Stage_dbo_AWBuildVersion_TruncateAndLoad.dtsx')
print ' - AdventureWorks2014_Stage_dbo_AWBuildVersion_TruncateAndLoad.dtsx SSIS Package added'
end
Else
begin
print ' - AdventureWorks2014_Stage_dbo_AWBuildVersion_TruncateAndLoad.dtsx SSIS Package already exists.'
Select @pkgID As PackageID
end

Again, PackageID is required for ApplicationPackage metadata, which is where we next turn our attention:

/* ApplicationPackage: AdventureWorks2014_Stage_dbo_AWBuildVersion_TruncateAndLoad.dtsx */
print 'ApplicationPackage: AdventureWorks2014_Stage_dbo_AWBuildVersion_TruncateAndLoad.dtsx'
declare @appPkgID int = (Select ApplicationPackageID
From custom.ApplicationPackages
Where ApplicationID = @appID
And PackageID = @pkgID
And ExecutionOrder = 10)

If(@appPkgID Is NULL)
begin
print ' - Adding AdventureWorks2014_Stage_dbo_AWBuildVersion_TruncateAndLoad.dtsx Application Package'
Insert Into custom.ApplicationPackages
(ApplicationID
,PackageID
,ExecutionOrder
,FailApplicationOnPackageFailure)
Output inserted.PackageID As PackageID
Values
(@appID
, @pkgID
, 10
, 1)
print ' - AdventureWorks2014_Stage_dbo_AWBuildVersion_TruncateAndLoad.dtsx Application Package added'
end

These scripts will add SSIS Framework metadata to create (or return existing values for) an SSIS Application, SSIS Package, and map the SSIS Package into the SSIS Application. I can hear you thinking, …

“Great Andy. But What if I Already Have a Bunch of SSIS Packages Deployed to an SSIS Catalog?”

The T-SQL above lends itself to automation so I automated it. The script provided below is not as robust as I’d like for Production purposes but it demonstrates the principles and, well, it works. I won’t go into detail describing each line of code. I’m sure this can be accomplished differently and I’m pretty sure it can be done better. Suffice it to say this T-SQL script loads SSIS Framework Community Edition metadata for an SSIS Project deployed to an SSIS Catalog. You set the name of the SSIS Application by setting the @frameworkApplicationName parameter. Supply the name of the SSIS Catalog Folder (@catalogFolderName) and Project (@catalogProjectName). Unless the order of execution doesn’t matter, you will likely have to update the ExecutionOrder values once the script completes.

Please remember this T-SQL starts a transaction but does not commit it. Commit and Rollback statements are at the end of the script, commented out so you can determine which to execute based on the results of the test query.

DISCLAIMER: This script will add SSIS Packages metadata to the SSIS Framework Community Edition in a non-deterministic order. Before executing the SSIS Application, please review the results returned by the final SELECT query in this script and update the ExecutionOrder values as needed to support your enterprise data integration requirements.

Use SSISDB
go

declare @frameworkApplicationName varchar(255) = N'Load AdventureWorks2014 Stage'
declare @catalogFolderName nvarchar(128) = N'Stage'
declare @catalogProjectName nvarchar(128) = N'AdventureWorks2014_Stage_Loader'
declare @packageName nvarchar(260)
declare @sql nvarchar(4000)
declare @CrLf char(2) = Char(13) + Char(10)
declare @ApplicationID int
declare @PackageID int
declare @ExecutionOrder int = 10
declare @ExecFlag bit = 1

declare curPackages Cursor For
Select p.[name] As PackageName
From [catalog].packages p
Join [catalog].projects pr
On pr.project_id = p.project_id
Join [catalog].folders f
On f.folder_id = pr.folder_id
Where pr.[name] = @catalogProjectName
And f.[name] = @catalogFolderName
And p.[name] Not Like '99_%'

-- start a transaction
begin tran

-- build application
Set @sql = '/* Application: ' + @frameworkApplicationName + ' */' + @CrLf
Set @sql = @sql + 'declare @appID int = (Select ApplicationID
From custom.Applications
Where ApplicationName = ''' + @frameworkApplicationName + ''')' + @CrLf + @CrLf
Set @sql = @sql + 'If(@appID Is NULL)
begin
Insert Into custom.Applications
(ApplicationName)
Output inserted.ApplicationID As ApplicationID
Values
(''' + @frameworkApplicationName + ''')
end' + @CrLf
Set @sql = @sql + 'Else
begin
Select @appID As ApplicationID
end' + @CrLf + @CrLf

print @sql

If(@ExecFlag = 1)
exec(@sql)

Set @ApplicationID = (Select ApplicationID
From custom.Applications
Where ApplicationName = @frameworkApplicationName)

open curPackages

fetch next from curPackages into @packageName

while(@@fetch_status = 0)
begin
Set @sql = '/* Package: ' + @packageName + ' */' + @CrLf
Set @sql = @sql + 'declare @pkgID int = (Select PackageID
From custom.Packages
Where PackageName = ''' + @packageName + '''
And ProjectName = ''' + @catalogProjectName + '''
And FolderName = ''' + @catalogFolderName + ''')' + @CrLf + @CrLf
Set @sql = @sql + 'If(@pkgID Is NULL)
begin
Insert Into custom.Packages
(FolderName
,ProjectName
,PackageName)
Output inserted.PackageID As PackageID
Values
(''' + @catalogFolderName + '''
, ''' + @catalogProjectName + '''
, ''' + @packageName + ''')
end' + @CrLf
Set @sql = @sql + 'Else
begin
Select @pkgID As PackageID
end' + @CrLf + @CrLf

print @sql

If(@ExecFlag = 1)
exec(@sql)

Set @PackageID = (Select PackageID
From custom.Packages
Where PackageName = @packageName
And ProjectName = @catalogProjectName
And FolderName = @catalogFolderName)
If(@PackageID Is Not NULL)
begin
Set @sql = '/* ApplicationPackage: ' + @packageName + ' */'
Set @sql = @sql + 'declare @appPkgID int = (Select ApplicationPackageID
From custom.ApplicationPackages
Where ApplicationID = ' + Convert(varchar(12), @ApplicationID) + '
And PackageID = ' + Convert(varchar(12), @PackageID) + '
And ExecutionOrder = ' + Convert(varchar(12), @ExecutionOrder) + ')' + @CrLf + @CrLf
Set @sql = @sql + 'If(@appPkgID Is NULL)
begin
Insert Into custom.ApplicationPackages
(ApplicationID
,PackageID
,ExecutionOrder
,FailApplicationOnPackageFailure)
Output inserted.PackageID As PackageID
Values
(' + Convert(varchar(12), @ApplicationID) + '
, ' + Convert(varchar(12), @PackageID) + '
, ' + Convert(varchar(12), @ExecutionOrder) + '
, 1)
end' + @CrLf

print @sql

If(@ExecFlag = 1)
exec(@sql)

   end

  Set @ExecutionOrder = @ExecutionOrder + 10
fetch next from curPackages into @packageName
end

close curPackages
deallocate curPackages

Select a.ApplicationName
, p.FolderName
, p.ProjectName
, p.PackageName
, ap.ExecutionOrder
, ap.FailApplicationOnPackageFailure
From custom.ApplicationPackages ap
Join custom.Applications a
On a.ApplicationID = ap.ApplicationID
Join custom.Packages p
On p.PackageID = ap.PackageID
Where a.ApplicationName = @frameworkApplicationName
Order By ap.ExecutionOrder

-- commit
-- rollback

Please consider this a beta version of this T-SQL script for loading metadata for deployed SSIS Projects into SSIS Framework Community Edition. As always, I welcome your feedback.

Enjoy!

:{>

—

You might like working with Enterprise Data & Analytics because we grok SSIS Frameworks.

—

Learn More:

Expert SSIS Training Online with Brent Ozar Unlimited!
IESSIS1: Immersion Event on Learning SQL Server Integration Services – 2-6 Oct 2017, Chicago
What is the DILM Suite? 2. SSIS Catalog Compare
What is the DILM Suite? 1. SSIS Framework Community Edition
Adding an SSIS Application to SSIS Framework Community Edition
Schedule an SSIS Application with SSIS Framework Community Edition
The Heart of SSIS Framework Community Edition-Parent.dtsx
SSIS Framework Community Edition Updates
My Latest Book – Building Custom Tasks for SQL Server Integration Services – Is Now Available!