Adding an SSIS Application to SSIS Framework Community Edition

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:

  1. custom.Applications
  2. custom.Packages
  3. custom.ApplicationPackages

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

FWApp0

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

FwApp1

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:

FWApp2

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 10th. Or 20th. Or even more than once. Remember, it’s the same SSIS 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.

[sql]

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. [/sql]

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 ApplicationName
                      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 PackageName
                      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:

Schedule an SSIS Application with SSIS Framework Community Edition
Designing an SSIS Framework (recording)
Biml in the Enterprise Data Integration Lifecycle (recording)
From Zero to Biml – 19-22 Jun 2017, London 
IESSIS1: Immersion Event on Learning SQL Server Integration Services – Oct 2017, Chicago

Andy Leonard

andyleonard.blog

Christian, husband, dad, grandpa, Data Philosopher, Data Engineer, Azure Data Factory, SSIS guy, and farmer. I was cloud before cloud was cool. :{>

Comments

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.