Azure DevOps, SSIS, and Git Part 0 – Getting Started

“My name is Andy and I am a huge fan of source control.” That said, I confess I have struggled with git for a couple years now. I recently had some “play time” – I love play time – and decided to play with git.

In this post I will demonstrate how to create an Azure DevOps account, configure it, create a new Azure DevOps Project, connect my Azure DevOps project to SQL Server Data Tools (SSDT), clone the Azure DevOps project to a local repository, create an SSIS project in the local repository, perform an initial checkin, update the local SSIS project, and check in the changes so they are stored in our Azure DevOps remote repository.

That’s a lot and I know you’re itching to get started walking through this demo. But first I’d like to share why I’m writing about Azure DevOps, Git, and SSIS – and recommend a book while I’m at it.

Why?

I have been listening to Can’t Hurt Me, an audio book by David Goggins and Adam Skolnick based on Goggins’ life. The audio book (and hard copy) contains profanity. I am not a fan of gratuitous profanity – profanity for the sake of profanity. In my opinion, that’s not what I’m hearing when I listen to Can’t Hurt Me.

I’m hearing a man communicate.

Goggins offers solid advice for, well… life. The audio book is better than the hard copy because the co-authors chat in between (and during) chapters about the experiences recorded in the book. (Shameless plug: You can get a free audio book from Audible and help out the Data Driven Podcast if you sign up using this link.)

One piece of advice Goggins offers is: face your challenges and fears. Overcome them. With a tip o’ the cap to Mr. Goggins, let’s “get after this.”

Git is a distributed version control system (VCS) that is free and open source. I can hear some of you thinking, “What does that have to do with SQL Server Integration Services (SSIS), Andy?” I’m glad you asked:

SSIS is software development.

Andy, circa 2006

Learning By Example

There are a few moving parts to using SSIS with Azure DevOps. In this post I’m going to share how to configure the parts and then get them moving. Cool?

Setup an Azure DevOps Account

First, you need Azure DevOps. Browse to dev.azure.com and set up an account:

Before I dive into something like this, I prefer to count the cost. At the time of this writing Azure DevOps may be used for free:

See the Azure DevOps Pricing page for details (note: this may have changed since I wrote this post).

Once you have Azure DevOps up and running, create a project:

Clicking the Create project button opens the Create New Project window. Configure the project by selecting a version control engine and a work item process :

Please note TFS is an option for version control engine. Are you using TFS on-premises but thinking of migrating to the cloud? Here you go.

Click the Create button to proceed. In a minute or so, the Overview>Summary page for the project displays:

Connect to the Azure DevOps Project

The next step is to connect SSDT to the Azure DevOps project. Begin by clicking Team>Manage Connections:

Note: You may need to disconnect a current connection first:

You may need to add an account to Visual Studio’s connections:

If so, you are prompted for credentials from Azure:

Once connected to Azure DevOps via Azure security, you may select an account:

After connecting, you may select the project you created earlier:

Git works in a local repository which exists on the developer’s machine. The Clone button surfaces three options:

  1. Connect
  2. Clone
  3. Clone with Submodules

The default option for the highlighted button is “Clone.” That’s a clue, cloning is our next step but for our introductory demonstration, we select Connect:

Note we cannot continue unless we clone the repository (that’s why it was shown by default!):

This reminds me of my younger son, Riley. Whenever I tell him something he doesn’t want to hear, Riley responds, “GOSH, Dad!” When I first saw this dialog, I said, “GOSH, Azure DevOps!” True story…
What Just Happened?

When we cloned the SSIS Test Azure DevOps git repository, we created a local, linked copy of the SSIS Test Azure DevOps git repository in the specified folder – in my case, the local folder was C:\Users\A. Ray Leonard\source\repos\SSIS Test:


We will get to the other folder and files in this directory later. Promise…

The next step is to “Create a new project or solution” in this new local repository (it still has that new repository smell!):

Create an SSIS Project

Click the”Create a new project or solution” link in Team Explorer to create a project in our new local repository:

View Solution Explorer once the new project has been created. Note the small green “+” sign decorating the solution name:

The “+” sign indicates the solution has been added to source control, which – again – we may view the solution in our local repository using Windows Explorer:

But when we check our Azure DevOps SSIS Test project, we see a message indicating the remote repository is empty:

“Dude! Where’s my code?”

Committing Code (Locally)

Click View>Team Explorer to open the Team Explorer dialog:

Once Team Explorer opens, click the Changes button:

“Ch-ch-ch-changes”

Enter a Commit Message:

Q: “How committed are you to this relationship?”

Click the Commit All button to commit the changes locally:

A: “Locally committed. You are locally committed to this relationship.”

What does locally committed mean? Your local repository has been updated but no changes have been transmitted to your remote repository…yet.

As the message above reads, “Sync to share your changes with the server.” Click the “Sync” link (hey, that rhymes!):

Syncing

Clicking the Sync link triggers a git “push”


Team Explorer lets you know once the sync and push operations complete:

Now you can see files in your remote repository – up in Azure DevOps, finally!

There be files here. WOO HOO!

You can learn a lot more about interacting with your local and remote repository from the Pro Git book, available online for free:

Update, Commit, Sync (Push)

We now have a copy of our mostly-empty SSIS project stored in the cloud. w00t! Let’s make some changes to the project and push them to Azure DevOps.

I opt to rename the package:

I opt to add a Script Task for ETL Instrumentation as recently posted, SSIS Design Pattern: Use Script Tasks for ETL Instrumentation:

I Confess, This (Still) Confuses Me…

After a save, I add a message and commit the changes locally:

This is not the confusing part…

Once the changes are committed locally, I click the Sync button as before:

Still not the confusing part…

After clicking Sync, I need to Push the changes to the Azure DevOps remote repository:

THIS is the confusing part.

This confuses me. Why doesn’t Sync issue a git push – like with the initial sync?
I’d be ok with the initial git sync not pushing, even; I crave consistency.

Regardless, my updates now reside in the Azure DevOps remote repository:

Conclusion

In this post we configured Azure DevOps, added an Azure DevOps Project, connected the Azure DevOps project to SQL Server Data Tools (SSDT), cloned the Azure DevOps project locally, added an SSIS project to the local repository, performed an initial checkin, updated the local SSIS project, and checked in the changes.

Mission accomplished.

:{>

SSIS Design Pattern: Use Script Tasks for ETL Instrumentation

I consider scripting in SQL Server Integration Services (SSIS) my data integration “get out of jail free” card. Why? If I cannot find a way to accomplish some requirement using SSIS’s built-in objects – tasks, containers, Data Flow components, etc. – I can usually write .Net code to fulfill the requirement.

As stated in the SSIS Academy short course titled Troubleshooting SSIS, I almost always add to the Control Flow an SSIS Script Task that logs the initial state of parameters and variables when package execution starts. I find this information invaluable when troubleshooting failed SSIS executions.

Configuring a Script Task for ETL Instrumentation

Begin by adding an SSIS Script Task to the Control Flow:

Open the editor.
Click inside the ReadOnlyVariables property value textbox. An ellipsis is available:

Click the ellipsis to open the Select Variables window. Select the System::TaskName and System::PackageName variables:

Click the OK button to close the Select Variables window. Note the two variables we selected now appear in the ReadOnlyVariables value textbox:

Select the variables and copy them to the clipboard:

.Net Scripting

Click the Edit Script button to open the Visual Studio Tools for Applications (VSTA) .Net code editor. Scroll to public void Main() and select the text “TODO: Add your code here”:

(click to enlarge)

Paste the contents of the clipboard:

I do this because mistyping variable names results in an error that’s not very intuitive. Also, .Net design-time validation has no way to test variable names for accuracy. It’s better to copy and paste than risk an error, in my opinion.

Declaring a .Net Variable

Begin a .Net variable declaration by typing “string “. Select the System::PackageName variable name from the comment and copy it to the clipboard:

Paste the clipboard contents after “string ” as shown here:

The .Net string variable declaration appears as shown:

Now, System::PackageName is not a valid .Net variable name.
System::PackageName is a valid SSIS variable name where “System” is the namespace and “PackageName” is the variable name.

Edit the .Net variable so it is named “packageName” as shown:

Initializing a .Net Variable

Our goal is to read the value of the SSIS variable System::PackageName into the .Net string variable named packageName. Assigning an initial value to a variable is known as initializing the variable.

To begin initializing the .Net string variable named packageName, add the “=” sign after the variable name (packageName). In C# (and similar languages), a single equal sign is an assignment operator.

Type “Dts.” – as shown next – and note IntelliSense presents a list of available methods and objects. Scroll to the object named “Variables” as shown:

You can append “Variables” to “Dts.” by double-clicking Variables or by pressing the Tab key when Variables is selected (as shown above). Add a beginning-bracket – [ – followed by a double-quote – “. Note the complementary closing bracket – ] – and double-quote – ” – are added automagically. Plus the cursor is in perfect position to paste the contents of clipboard (again):

Move to the end of this line of .Net code and type “.v”. IntelliSense kicks in – although my screen-capture software makes the context menu opaque – and the Value property is selected as shown:

This is important.

Next type “.t”. Notice the Value property is completed by IntelliSense – again, automagically – and IntelliSense also displays the only option available that begins with the letter “t” – ToString:

Complete the statement by typing “();”:

Rinse, Repeat

Use the same .Net variable declaration and initialization to declare and initialize the taskName .Net string variable:

Next, declare a .Net string variable named subComponent and use the packageName and taskName .Net string variables to initialize the value of subComponent:

Exercise your .Net variable declaration and initialization skills even more by declaring two more variables:

  1. An int variable named informationCode and initialized to 1001
  2. A bool variable named fireAgain initialized to true:

Finally, declare a .Net string variable named description and initialize it with the string

“I am ” + packageName

ETL Instrumentation via Information Event Message

Configure and raise an Information event using the following .Net statement:

Dts.Events.FireInformation(informationCode, subComponent, description, “”, 0, ref fireAgain);

Click the screenshot below to enlarge the image. Note the Dts.Events.FireInformation method takes six arguments:

  1. informationCode [int]
  2. subComponent [string]
  3. description [string]
  4. helpFile [string]
  5. helpContext [int]
  6. fireAgain [bool]
(click to enlarge)

We use the .Net variables informationCode, subComponent, description, and fireAgain to supply four of the arguments. We supply literal values – “” and 0, respectively – to the helpFile and helpContext arguments.

When complete, our Main() method contains the following .Net code:

public void Main()
{
// System::TaskName,System::PackageName
string packageName = Dts.Variables[“System::PackageName”].Value.ToString();
string taskName = Dts.Variables[“System::TaskName”].Value.ToString();
string subComponent = packageName + “.” + taskName;
int informationCode = 1001;
bool fireAgain = true;

string description = “I am ” + packageName;
Dts.Events.FireInformation(informationCode, subComponent, description, “”, 0, ref fireAgain);

Dts.TaskResult = (int)ScriptResults.Success;
}

Test It!

Close the VstaProjects code editor by closing the window, then close the Script Task Editor by clicking the OK button.

Execute the SSIS package in the SQL Server Data Tools (SSDT) debugger by pressing the F5 key. If all goes as planned, you should see a successful debug execution:

Click the Progress tab to view the Information message:

(click to enlarge)

Conclusion

ETL Instrumentation is an important part of data integration development with SSIS. It increases development time (slightly) to add ETL Instrumentation such as this but it’s worth it. When troubleshooting an SSIS package at 2:00 AM some months after deployment, ETL Instrumentation will save time.

The additional time spent during development is the opposite of technical debt, it’s a technical investment.

Learn more!

Get live, online training – from me! Check out the Enterprise Data & Analytics Training page.

Check out Introduction to Troubleshooting SSIS – a free sample of SSIS Self-Help at SSIS Academy!

The Recording of Faster SSIS is Available

The recording for the free webinar titled Faster SSIS is available and may be viewed above. Thanks to everyone who attended!

Regarding “Bad” BLOBTempStoragePath Settings

Regarding the BLObs.dtsx demo SSIS package, Doug asked a really good question:

“Does the package abort if the [BLOBTempStoragePath] folder path is invalid or does it create it for you?”

The answer is: The package fails. The path is not created for us.

(click to enlarge)

The errors are:

Error:
Error:
Error:
[OLE DB Source [46]] Error: Failed to retrieve long data for column “Demographics”.
[OLE DB Source [46]] Error: There was an error with OLE DB Source.Outputs[OLE DB Source Output] on OLE DB Source. The column status returned was: “DBSTATUS_UNAVAILABLE”.
[OLE DB Source [46]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The “OLE DB Source.Outputs[OLE DB Source Output]” failed because error code 0xC0209071 occurred, and the error row disposition on “OLE DB Source” specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.
[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on OLE DB Source returned error code 0xC0209029. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.

(click to enlarge)

Enjoy the video!

:{>

SSIS, Docker, and Windows Containers, Part 1 – Why?

In my first post in this series, SSIS, Docker, and Windows Containers, Part 0 – Getting Started, I covered:

  • Installing docker
  • Choosing a Container OS
  • Pulling a test image
  • Pulling a Windows Container with SQL Server installed
  • interacting with with your instantiated container

In this post I reveal my ultimate goal and then travel down two rabbit holes to explain:
1. How I got here; and
2. One way to get answers to questions no one wants to hear.

My Ultimate Goal

My ultimate goal is to use one or more Windows containers running SQL Server to test SSIS. I can hear some of you thinking,..

“Why, Andy?”

My response? “Cuz.”
And, “I do what I want!”

Seriously, since I hopped the fence from developer to data I’ve dreamed of the day when I could practice lifecycle management with data-stuff like I used to practice lifecycle management with software development.

I recognize the obstacles. The greatest obstacle (in my humble opinion) is software is mostly stateless these days (these days started with Object-Oriented Programming and include its descendants). Stateless development solves lots of engineering problems in lifecycle management, and by “solves a lot of engineering problems” I mean some engineering problems simply don’t exist so lifecycle management for stateless stuff can simply ignore “a lot of engineering problems.”

A database, on the other hand, is all about that state. When it comes to managing lifecycle for a stateful platform – like a database – ACID gets dumped on many lifecycle management tools and solutions (see what I did there?).

Is it possible to manage a data-related lifecycle using stateless tools? Yes. But here there be obstacles. Let’s look at on use case:

Use Case 0

Your team releases an update that adds one column to one table. The column receives data. Someone realizes there’s a bug and determines a rollback is the solution. The web and middle-tier teams successfully rollback the release using functionality built into the release-management / DevOps enterprise tool.

What happens to the column in the table?

  • Is it dropped?
  • Is the data deleted?
  • Since this isn’t a post about data lifecycle management, let’s not even consider what happens if the data is considered personal-identifying-information or some tuple otherwise subject to privacy regulations.

You see the problem? The answer is always going to be, “It depends.”

This is the problem I am trying to solve, specifically for data integration.

I want to be able to easily deploy and rollback data integration deployments. But to accomplish this, I need more metadata (data about the data) and a bunch more automation. Why? My stuff has state – by definition.

“Not Supported”

If you have not yet figured this out, Microsoft is a software development shop. They’re people trying to use software to solve business problems for customers – just like you and me. Just like you and me, they have schedules and deadlines and competition and a need to generate a profit. in other words, Microsoft is a business.

When some redneck from Farmville Virginia starts asking all sorts of out-of-scope questions, he sometimes hears, “That’s not supported, Andy,” in response. Ask me how I know.

So it helps to be stubborn persistent. Persistence sometimes takes the form of thinking of another approach. When I began probing for ways to test SSIS in containers, my ultimate goal was to have the tests simulate Production as closely as possible. In 2019, Production SSIS means executing SSIS in the SSIS Catalog.

Is it possible to execute Production SSIS from the file system or the MSDB database in 2019? It is. I’ve not found a good use case for executing SSIS outside the Catalog, though – not in 2019. I am not trying to hurt anyone’s feelings, I promise. I’m just saying that the SSIS Catalog has been out for north of six years at the time of this writing. Plus: The only way to execute Azure-SSIS packages is via an SSIS Catalog in the cloud (again, at the time of this writing).

At Enterprise Data & Analytics we help clients migrate from file- / MSDB-based SSIS execution to the SSIS Catalog (both on-premises and Azure-SSIS). Some clients realize the benefits of SSIS Catalog-based execution and call us for help with training and consulting. Others ask for help performance-tuning SSIS, and we almost always recommend using the SSIS Catalog because SSIS packages almost always execute faster when executed from the Catalog. When do we not recommend using the SSIS Catalog? Some enterprises have designed their data environments in such a way that using the SSIS Catalog would require a major shift. The technical portion of the shift would be relatively straightforward, but – often – the cultural shift is more disruptive. Not all enterprises are willing to make such a cultural shift; to endure the disruption.

Getting Around “Not Supported”

When I started asking questions like, “Is it possible to run the SSIS Catalog in a Windows container?” the response was, “That’s not supported.”

Fine. What is supported? Running the SSIS Catalog on SQL Server installed on Windows Server Core. I was trying to solve many of the same problems in a container that would need to be solved running an SSIS Catalog on Core. It turned out many of these problems had indeed already been solved.

More Information

If you can make it to SQL Saturday #813 – Boston BI Edition on 30 Mar 2019, Liz Baron and Sreeja Pullagura are delivering an information-packed session titled SSIS DevOps with Azure Containers on this very topic. I’ve had the privilege and honor to work with Liz, Sreeja, and the awesome data engineering team at Teach For America – a worthy non-profit.

I predict Sreeja’s and Liz’s presentation is going to be a session that moves the needle for Data Integration Lifecycle Management (DILM).

My next several blog posts on this topic – sharing some of my efforts and what I’ve learned about executing SSIS using containers – will begin appearing 31 Mar 2019.

Shameless plug: I am delivering Intelligent Data Integration with SSIS – a full-day pre-conference training session – Friday, 29 Mar 2019.
Register here!
I hope to see you there!

:{>

Free Webinar – Faster SSIS

SQL Server Integration Services (SSIS) is a powerful enterprise data integration tool that ships free with Microsoft SQL Server. Join me as I demonstrates practical SSIS performance design patterns.

Register today to learn:

  • Load BLObs faster
  • An Incremental Load design pattern using HashBytes
  • Using a Script Component Destination

This webinar is free!

Register today!

Lift and Shift SSIS to Azure

Enterprise Data & Analytics‘ data engineers are experts at lifting and shifting SSIS to Azure Data Factory SSIS Integration Runtime. 

Our state-of-the-art DILM Suite tools in the capable hands of our experienced data engineers combine to drastically reduce the amount of time to manually migrate and apply SSIS Catalog configuration artifacts – Literals, Catalog Environments and Catalog Environment Variables, References, and Reference Mappings – while simultaneously improving the quality of the migration effort.

Check out our Lift and Shift page to learn more!

:{>

Andy Answers: Format a Flat File Name

My Twitter DMs are open @AndyLeonard. Ask me a question about SSIS, ADF, or Biml. I may answer it with a blog post, a video, or both.

Today’s Andy Answer is an answer to an SSIS question received via @AndyLeonard direct message:

Using SSDT, I have 3 variables: one for the file path, one for date string, and one for file name. I’m trying to create the flat file destination with a variable so the date will be correct each day the file is generated. I have the right expressions for each variable, however, when the file is created it only shows the file name variable value. I configured the flat file connection manager’s ConnectionString property with an expression that concatenates these three variables. Can you help?

[edited]

Yes. Yes I can help. I Am Here To Help.™

An Example to Demonstrate

Create a demo project:

Rename the SSIS package, if you’d like:

Add new SSIS Variables to the SSIS Package. 

I added four variables to start:

  • FlatFilePath [String] – defaulted to a path on my server.
  • FlatFileName [String] – a hard-coded base name for my file.
  • FlatFileExtension [String] – to hold my file extension (csv in this case).
  • FlatFileDateString [String] – the date formatted as I desire:

Formatting the FlatFileDateString SSIS variable is tricksy. I used the following SSIS Expression:

(DT_WSTR,4)YEAR(@[System::StartTime]) + (DT_WSTR,2)((MONTH(@[System::StartTime]) < 10) ? “0” + (DT_WSTR,2)MONTH(@[System::StartTime]) : (DT_WSTR,2)MONTH(@[System::StartTime])) + (DT_WSTR,2)((DAY(@[System::StartTime]) < 10) ? “0” + (DT_WSTR,2)DAY(@[System::StartTime]) : (DT_WSTR,2)DAY(@[System::StartTime]))

Add a new SSIS Variable to contain the full path to the flat file. My variable is named FlatFileFullPath and the SSIS Expression used to build it is show here:

My SSIS Variables, configured:

Note: the initial value for the FlatFileFullPath Expression is:

@[User::FlatFilePath] + “\” + @[User::FlatFileName] + “_” + @[User::FlatFileDateString] + “.” + @[User::FlatFileExtension]

Add a Data Flow Task. In the Data Flow Task, add an OLE DB Source adapter. Configure the source adapter to read some data from some sample database:

Create a test sample file in the directory you specified in the FlatFilePath SSIS Variable Value:

Populate the sample file with column names and one sample data row. Enclose the column names and data values in double-quotes:

Create a Flat File Connection Manager. Configure the Text Qualifier as double-quotes and make sure the “Column names in the first data row” checkbox is checked:

Verify Flat File Connection Manager configuration by viewing the Columns page:

Add a Flat File Destination adapter to the Data Flow Task and configure it to use the Flat File Connection Manager. Ensure the “Overwrite data in the file” checkbox is checked to make the SSIS package re-executable (within the same day, at least):

Select the Flat File Connection Manager and view its Properties. Click the ellipsis for the Expressions property to open the Property Expressions Editor for the Flat File Connection Manager. From the Property dropdown, select ConnectionString:

Drag the FlatFileFullPath variable to the Expression textbox. Click the Evaluate Expression button to examine and verify the value of the FlatFileFullPath SSIS variable: 

When you close the Expression Builder, the Property Expressions Editor should appear similar to:

Once the ConnectionString Expression override is configured, set the DelayValidation property for the Flat File Connection Manager to True. This setting configures the connection manager to skip validation, which is helpful if the file does not (yet) exist:

A test execution in the SSIS debugger proves we’ve accomplished out goal:

One Change

I recommend (at least) one change to this package: Move the FlatFilePath SSIS Variable to a Package Parameter. Why? One may desire to change the target directory of the output file at some point and a parameter value is easy to override at runtime:

After converting the SSIS variable to a package parameter, update the Expression for the FlatFileFullPath SSIS variable to reflect the change:

The updated Expression is:

@[$Package::FlatFilePath] + “\” + @[User::FlatFileName] + “_” + @[User::FlatFileDateString] + “.” + @[User::FlatFileExtension]

A second test run lets me know we’re all good.

Conclusion

This is one answer for this question posed to me on Twitter.

Do you have an SSIS question? Ask! Andy Answers.

:{>

Mean Time to Identify Failure

While managing a team of 40 ETL developers, I wanted to track lots of metrics. Some of the things I wanted to track were technical, like SSIS package execution times. Some metrics were people-centric. 

Andy’s First Rule of Statistics states:

You can use statistics for anything about people, except people.

Andy – circa 2005

It was important to me to track how long it took the on-call person to identify the problem. I didn’t use the information to beat on-call people over the head. I used the information to measure the results of several experiments for displaying metadata about the failure.

Reports For The Win

You may be as shocked by this as I was; reports helped a lot more than I anticipated. Before I deployed the reports the Mean Time to Identify Failure was tracking just under 30 minutes. After deploying the reports, the mean time to identify failure fell to 5 minutes.

As I said, I was shocked. There were mitigating circumstances. The on-call team members were getting more familiar with the information SSIS produces when it logs an error. They were gaining experience, seeing similar errors more than once.

I accounted for growing familiarity by narrowing the time window I examined. The least-impressive metrics put the reduction at 18 minutes to 5 minutes.

Pictures…

(click to enlarge)

Before I built and deployed the dashboard for SSIS Application Instances (like the one pictured at the top of this post), on-call people would query custom-logging tables we built to monitor enterprise data integration. The queries to return Application Instance log data were stored where everyone could reach them. In fact, I used the same queries as sources for this report.

A funny thing happened when I deployed the reports. Each week, one or more on-call people would ping me and tell me how much they liked the reports. Even though the data was the same, the presentation was different. A picture with a little color goes a long way.

The image at the beginning of this section – the SSIS Framework Task Instance Report – is displayed when a user clicks the Failed link shown in the initial screenshot. This design received he most comment by the on-call team members. The most common comment was, “I click the Failed link and it takes me to details about the failure.” The reports were passing The 2:00 AM Test.

SSIS Framework Applications

If you’ve read this far and wondered, “Andy, what’s an SSIS Application?” An SSIS Application is a construct I came up with to describe a collection of SSIS Packages configured to execute in a specific order. An application is a way to group SSIS packages for execution. You can get a sense of how our frameworks work – especially the application execution functionality – by checking out the SSIS Framework Community Edition at DILM Suite (DILM == Data Integration Lifecycle Management).

(click to enlarge)

An Application Instance is an instance of execution of an SSIS Application. An Application Instance is made up of Package Instances. the relationship between applications and packages appears straightforward: an application is a collection of packages; parent-child; one-to-many. But it’s not quite that simple. Our SSIS Frameworks facilitate patterns that execute the same package multiple times, sometimes in parallel! We can also create packages that perform utility functions – such as ArchiveFile.dtsx – and call it from multiple applications. When you do the Boolean algebra, the relationship between applications and packages is many-to-many. 

Our SSIS Frameworks are SSIS Catalog-integrated. They even work with the SSIS Integration Runtime that’s part of Azure Data Factory, Azure-SSIS. 

Dashboards… Evolved

While the Reporting Services dashboard was neat when it was released back in the day, the cool kids now play with Power BI. At DILM Suite you will also find a free – albeit basic – Power BI dashboard that surfaces many of the same metrics using even better reporting technology. The Basic SSIS Catalog Dashboard in Power BI is free at DILM Suite.

I’ve not yet collected Mean Time to Identify Failure metrics using the Basic SSIS Catalog Dashboard in Power BI dashboard. Perhaps you can be the first.

Enjoy!