Catalog Browser v0.7.8.0

I’ve been making smaller, more incremental changes to SSIS Catalog Browser – a free utility from the Data Integration Lifecycle Management suite (DILM Suite).

You can use SSIS Catalog Browser to view SSIS Catalog contents on a unified surface. Catalog Browser works with SSIS Catalogs on-premises and Azure Data Factory SSIS Integration Runtime, or Azure SSIS. It’s pretty cool and the price ($0 USD) is right!

The latest change is a version check that offers to send you to the page to download an update. You will find this change starting with version 0.7.7.0.  Version 0.7.8.0 includes a slightly better-formatted version-check message. As I said, smaller, more incremental changes.

Enjoy!

Honored to Present Lift and Shift SSIS to ADF at #Azure DataFest Reston

I am honored to deliver Lift and Shift SSIS to ADF at the Azure DataFest in Reston Virginia 11 Oct 2018!

Abstract

Your enterprise wants to use the latest cool Azure Data Analytics tools but there’s one issue: All your data are belong to the servers on-premises. How do you get your enterprise data into the cloud?

In this session, SSIS author and trainer Andy Leonard discusses and demonstrates migrating SSIS to Azure Data Factory Integration Runtime.

Register today!

:{>

Viewing SSIS Configurations Metadata in SSIS Catalog Browser

SSIS Catalog Browser is a pretty neat product. “How neat is it, Andy?” I’m glad you asked.

It’s free. That makes it difficult to beat the cost.

SSIS Catalog Browser is designed to surface all SSIS Catalog artifacts and properties in a single view. “What exactly does that mean, Andy?” You’re sharp. Let’s talk about why the surface-single-view is important.

Before I go on, you may read what I’m about to write here and in the companion post and think, “Andy doesn’t like the Integration Services Catalogs node in SSMS.” That is not accurate. I do like the Integration Services Catalogs node in SSMS. It surfaces enough information for the primary target user of SSMS – the Database Administrator – to see what they need to see to do their job, without “cluttering up” their interface with stuff that they rarely need to see and even more-rarely change.

In the companion post I shared this image of the windows (and pages) you need to open in SSMS to view the configured execution-time value of a parameter that is mapped via reference:

(click to enlarge)

That’s a lot of open windows.

So how does one view the same configuration metadata in SSIS Catalog Browser?

Under the Project node (LiftAndShift), we find a virtual folder that holds Project Parameters.

In Project parameters, we find a reference mapping – indicated by underlined text decoration and describing the reference mapping as between the parameter (ProjectParameter) and the SSIS Catalog Environment Variable (StringParameter).

Expanding the reference mapping node surfaces References. There are two references named env1 and env2. Since references can reference SSIS Catalog Environments in other Catalog folders, the fully-qualified path to each SSIS Catalog environment is shown in folder/environment format.

Expanding each reference node surfaces the value of the SSIS Catalog Environment Variable in each SSIS Catalog Environment.

I call this feature Values Everywhere, and I like it. A lot.

Values Everywhere From the Project Reference Perspective

Values Everywhere is perspective-aware. Whereas from the perspective of an SSIS Project Parameter, Values Everywhere surfaces the reference mapping in the format parameter–>environment variable, in the Project References virtual folder, Values Everywhere surfaces the same relationship as environment variable–>parameter:

Values Everywhere perspectives follow intuition when surfacing reference mapping relationships. (Did I mention I like this feature? A lot?)

Conclusion

SSIS Catalog Browser provides a clean interface for enterprise Release Management and Configuration teams. And it’s free.

I can hear you thinking, “Why is Catalog Browser free, Andy?” I give away Catalog Browser to demonstrate the surfacing capabilities of SSIS Catalog Compare.

SSIS Catalog Compare

SSIS Catalog Compare not only surfaces two SSIS Catalogs side by side, you can compare the contents of the Catalogs:

You can also script an entire SSIS Catalog which produces T-SQL script and ISPAC files for every artifact in the SSIS Catalog (organized by instance and folder):

You can also deploy all artifacts contained in an SSIS Catalog Folder from one SSIS Catalog to another:

This functionality is an efficient method for Data Integration Lifecycle Management – or DevOps – with SSIS.

SSIS Catalog Compare even works with Azure Data Factory SSIS Integration Runtime, so you can use SSIS Catalog Compare to lift and shift SSIS from on-premises Catalogs to the cloud.

Viewing SSIS Configurations Metadata in SSMS

Let’s take a look at an SSIS Project in SSMS:

Demo is the SSIS Catalog folder. Demo contains three SSIS projects named 0-Monolith, EmptySSISProject, and LiftAndShift.

If we expand the LiftAndShift SSIS project we see it contains a single SSIS package named Load Customer.dtsx.

The Demo folder contains two Catalog environments named env1 and env2.

These are the artifacts surfaced via the Integration Services Catalogs node in SSMS.

Before I go on, you may read what I’m about to write here and in the companion post and think, “Andy doesn’t like the Integration Services Catalogs node in SSMS.” That is not accurate. I do like the Integration Services Catalogs node in SSMS. It surfaces enough information for the primary target user of SSMS – the Database Administrator – to see what they need to see to do their job, without “cluttering up” their interface with stuff that they rarely need to see and even more-rarely change.

But Wait, There’s More

There are configurations that are not readily surfaced in the Integration Services Catalogs node in SSMS. To view the configurations metadata for the LiftAndShift SSIS project, right-click the project and click Configure:

The Configure window displays:

From this window I can see examples of the three sources of values for SSIS project and package parameters:

  1. Design-time defaults
  2. Literals
  3. Reference mappings

Design-Time Defaults

Design-time defaults are the default values entered by the SSIS developer when building the SSIS project. These values remain with the SSIS project as it is deployed. They are not changed unless and until another version of the SSIS project is deployed, one which contains different default values for parameters.

Design-time defaults are denoted by no text decoration.

Literals

There are actually two kinds of literal values:

  1. Configuration literals
  2. Execution literals

Configuration Literals

Configuration literals are stored in the SSIS Catalog. The values are “hard-coded” to override design-time defaults when the SSIS package is executed.

Configuration literals are denoted by bold text decoration.

When the SSIS project was first deployed to the SSIS Catalog, the value of ProjectLiteralParameter was the design-time default. We can see the design-time default value of ProjectLiteralParameter if we open the Configure window and click the ellipsis beside the Value cell for the parameter:

The design-time default value for ProjectLiteralParameter is “Project Default Value” shown circled in green above. The Configuration Literal value is “Project Literal Value” and is shown inside red and blue boxes in both the Set Parameter Value dialog and the Configure project window.

Execution Literals

Execution literals may be supplied in the Execute Package window. The Execute Package window is displayed when a user right-clicks an SSIS package and then clicks Execute:

Please note the Configuration literals – the values shown in the previous Configuration window with bold text decoration – are shown with no text decoration in the Execute Package window:

(click to enlarge)

Why are the Configuration literals not identified as such in the Execute Package window? The reason is that these values can be overridden as Execution literals.

If I click the ellipsis beside the value cell for the parameter named ProjectLiteralParameter, I can type in a value – an execution literal value – that will override the value of the ProjectLiteralParameter when the SSIS package is executed. The text decoration – in the Execute Package window – for an Execution Literal is bold:

This is a little complex so please bear with me as we examine what just happened.

When the SSIS project was first deployed to the SSIS Catalog, the value of ProjectLiteralParameter was the design-time default. We saw this value in the image above, repeated here:

The design-time default value for ProjectLiteralParameter is “Project Default Value” shown circled in green above. The Configuration Literal value is “Project Literal Value” and is shown inside red and blue boxes in both the Set Parameter Value dialog and the Configure project window.

When we open the Execute window, the Configuration literal value – “Project Literal Value” – is shown with no text decoration (as shown earlier):

(click to enlarge)

We can override the configured override for this execution of the SSIS package and this execution only by clicking the ellipsis to the right of the Value cell for ProjectLiteralParameter. The ellipsis opens the Edit Literal Value for Execution dialog into which we can enter an Execution literal value:

Please note: Execution literal values are not persisted in the SSIS Catalog. They are applied for the current execution – the execution triggered when the user clicks the OK button on the Execute Package window – and that execution only. If the use clicks the Cancel button and then re-opens the Execute Package window, all values revert to their Configured state.

Reference Mappings

Reference mappings are a mechanism for externalizing configurations in the SSIS Catalog. References are an elegant solution for configurations and release management. As with all flexible solutions, they are complex.

Reference mappings begin with References, and references begin with SSIS Catalog Environments (did I mention this was complex?).

Not discussed: Execution literals may also be used to override parameters that are reference-mapped. More on reference mappings in a bit…

SSIS Catalog Environments

An SSIS Catalog Environment is a container that holds SSIS Catalog Environment Variables. An SSIS Catalog Environment has the following configurable properties:

  • Name
  • Description (optional)

An SSIS Catalog Environment is a collection of SSIS Catalog Environment Variables. An SSIS Catalog Environment Variable has the following configurable properties:

  • Name
  • Type
  • Description (optional)
  • Value
  • Sensitive

References

A reference is a relationship between an SSIS Catalog Environment and an SSIS project (or SSIS package) that is deployed to an SSIS Catalog:

References are configured in the Configuration window on the References page:

Reference Mappings

A reference mapping applies (“maps”) the value of an SSIS Catalog Environment Variable – accessed via a reference – to an SSIS Project (or SSIS package) parameter.

Reference mappings are denoted by underlined text decoration:

In the image above, StringParameter is the name of the SSIS Catalog Environment Variable mapped to the parameter named ProjectParameter. This means the value contained in the SSIS Catalog Environment Variable named StringParameter will be used to override the value of the parameter named ProjectParameter at execution time.

There are several components of a Reference Mapping configuration. If we start our description at the parameter, feel free to sing along to our own version of The Skeleton Song substituting the following:

  • The parameter is part of the SSIS project
  • The project references the SSIS Catalog Environment
  • The SSIS Catalog Environment contains the SSIS Catalog Environment Variable
  • SSIS Catalog Environment Variable value property overrides the parameter value at execution time.

Not discussed: Multiple references and reference selection at execution time.

Viewing the Execution-Time Value

You can find the value that will be used at execution-time using SSMS.

View Design-Time Default and Configuration Literal Values

Design-time default and configuration literal values are available from the Configuration window:

Reference Mapping Values

You have to open a few windows – and a couple pages on the same window (Configure) to surface the execution-time value of a parameter that is mapped via reference:

(click to enlarge)

It’s… complicated. And it’s even more complex if we include multiple references to different SSIS Catalog Environments.

This last image is why I wrote SSIS Catalog Browser.

Catalog Browser is free and I write about how to see these same SSIS Catalog artifacts in the companion post titled Viewing SSIS Configurations Metadata in SSIS Catalog Browser.

It is Possible to Execute SSIS in a Container

As the title of this post suggests, it is possible to execute SSIS in a container. How? I’ll share more details later – promise. One short version is:

  1. Run a container with the microsoft/mssql-server-windows-developer image (assign lots of RAM).
  2. Add the SQL Server installation files to the container.
  3. Execute SQL Server setup from a command line configured to add Integration Services (this is why you need the RAM).
  4. Add SSIS.
  5. Execute from the command line (dtexec).

The results?

SSIS Catalog in a Container?

I can hear you thinking, “Yeah? But what about using the SSIS Catalog in a container, Andy? Huh? HUH?”

Calm down, will ya? I’m working on it.

I’ve benefited from some great advice and help from Ned Otter (nedotter.com | @NedOtter). I caught Ned’s presentation on containers at SQL Saturday Boston 22 Sep 2018. I’d seen other presentations on containers and learned a lot. Ned’s session put me over the top.

I can hear you thinking, ” Yeah-yeah. Have you made any progress, Andy?”

Well, some:

Above is a screenshot of an SSIS 2017 Catalog hanging out in a container. In this state I cannot do much with it because I am connected using a SQL Server login (even though said login is sa).

I was able to “create” a Catalog folder in this Catalog:

I write “create” in quotes because I “created” the folder in an unconventional manner (executing pieces of SSISDB stored procedures).

I may be able to deploy an SSIS project – again, via some creative combinations of pieces of SSISDB stored procedures. If so, I doubt I will be able to execute an SSIS package in this Catalog.

The only way I can think to accomplish this is to bypass some Catalog security checks which means there is a distinct possibility that important steps will be missed or skipped.

My gut tells me this would be bad.

The best path forward is to find some way to connect to SQL Server in the container using Windows Authentication. I’ve been communicating with my brother and friend, Brian Kelley (truthsolutions.wordpress.com | @kbriankelley) about this but have yet to solve it.

I plan to publish the image once I figure out how.

Conclusion

I am excited about executing SSIS in a container from the command line, though. Very excited.

More to come…

:{>

Using SSIS Framework Community Edition Webinar 20 Sep

Join me 20 Sep 2018 at noon ET for a free webinar titled Using SSIS Framework Community Edition!

Abstract

SSIS Framework Community Edition is free and open source. You may know can use SSIS Framework Community Edition to execute a collection of SSIS packages using a call to a single stored procedure passing a single parameter. But did you know you can also use it to execute a collection of SSIS packages in Azure Data Factory SSIS Integration Runtime? You can!

In this free webinar, Andy discusses and demonstrates SSIS Framework Community Edition – on-premises and in the cloud.

Join SSIS author, BimlHero, consultant, trainer, and blogger Andy Leonard at noon EDT Thursday 20 Sep 2018 as he demonstrates using Biml to make an on-premises copy of an Azure SQL DB.

I hope to see you there!

Register today.

:{>

Announcing the Fundamentals of Azure Data Factory Course!

I am excited to announce a brand new course (it still has that new course smell) from Brent Ozar Unlimited and honored to deliver it! This one-day, live, online course is titled Fundamentals of Azure Data Factory and it’s designed to introduce you to Azure Data Factory (ADF).

There will be demos.
Live demos.
Lots of live demos!

Abstract

Azure Data Factory, or ADF, is an Azure PaaS (Platform-as-a-Service) that provides hybrid data integration at global scale. Use ADF to build fully managed ETL in the cloud – including SSIS. Join Andy Leonard – authorblogger, and Chief Data Engineer at Enterprise Data & Analytics – as he demonstrates practical Azure Data Factory use cases.

In this course, you’ll learn:

  • The essentials of Azure Data Factory (ADF)
  • Developing, testing, scheduling, monitoring, and managing ADF pipelines
  • Lifting and shifting SSIS to ADF SSIS Integration Runtime (Azure-SSIS)
  • ADF design patterns
  • Data Integration Lifecycle Management (DILM) for the cloud and hybrid data integration scenarios

To know if you’re ready for this class, look for “yes” answers to these questions:

  • Do you want to learn more about cloud data integration in Azure Data Factory?
  • Is your enterprise planning to migrate its data, databases, data warehouse(s), or some of them, to the cloud?
  • Do you currently use SSIS?

The next delivery is scheduled for 10 Dec 2018. Register today!

I hope to see you there.

:{>

Delete SSIS Catalog Folders With SSIS Catalog Compare

To delete a Catalog folder using SSIS Catalog Compare, right-click the folder and click Delete Folder:

You are prompted to confirm folder deletion:

If the folder contains additional Catalog artifacts, the user is prompted again to confirm the deletion of the folder and all contents:

Let’s take a short break right here and consider some important facts about SSIS Catalog References and Environments.

Some Important Facts About SSIS Catalog References and Environments

  1. SSIS Catalog Environments may exist in any SSIS Catalog Folder.
  2. SSIS Catalog References define a relationship between an SSIS Catalog Environment and an SSIS Project or Package.
  3. The SSIS Catalog Environment is not required to reside in the same SSIS Catalog Folder as the SSIS Project or Package.
  4. The SSIS Catalog permits the deletion of Catalog Environments that participate in references.

Deleting the SSIS Catalog Folder Using SSMS

You cannot delete the SSIS Catalog Folder using SQL Server Management Studio – SSMS – unless the the folder is empty. Attempting to drop a non-empty SSIS Catalog Folder produces an error:

(click to enlarge)

However, SSMS allows the user to delete a Catalog Environment that is referenced with no warning or notification to the user:

Simply right-click the Environment and click Delete:

The Delete Object window, containing the SSIS Catalog Environment, displays:

Click OK and the Catalog Environment is deleted:

No warning, no message that env1 Catalog Environment is referenced by either a project in its own folder, Demo:

… or in a project in another folder (the ParamtersTest SSIS Project in the Test folder, in this case):

I can hear you thinking, “Why is this a problem, Andy?” I’m glad you asked – that’s an excellent question. Let’s try and execute an SSIS Package from the Test folder – from the SSIS Project named ParametersTest whose Configuration shown in the second screenshot above:

(click to enlarge)

Please note that I have checked the Environment checkbox and selected the referenced Catalog Environment. Why doesn’t this clear the error?

Because the Demo\env1 Catalog Environment no longer exists!

The error reads:

Parameter “NonSensitiveProjectParameter” is configured to receive a value from an environment variable named “Int32 Parameter”, but there is no environment variable named “Int32 Parameter” in environment “Demo\env1”. Select a different environment, or use a literal value for the parameter.

The error message is misleading – there is no Demo\env1 Catalog Environment. I just deleted it.

This is a broken reference.
And this is just how easy it is to accidentally create a broken reference when using SSMS to manage the SSIS Catalog.

But You Can Fix This, Right?

Well, yes and no. Mostly no.

“Can’t you connect to a different SSIS Catalog – one that contains another version of env1 – and then script env1 by pressing the Script button?”

Please watch this animated gif of me doing just that:

(click to enlarge)

Clicking the Script button after an Environment has been created and configured results in an empty SSMS script window.

Sad trombone…

You have options, but at this point something has gone very, very wrong with your plans for the day.

How SSIS Catalog Compare Helps

SSIS Catalog Compare informs the user if they are about to delete an SSIS Catalog Environment that participates in a reference.

If the folder contents include SSIS Catalog Environments that participate in references, the user must confirm the deletion of these Catalog Environments:

The message reads:

The environment Demo/env1 references the following folder(s)/project(s) in vDemo\QA:
Demo/LiftAndShift
Test/ParametersTest

The environment Demo/env2 references the following folder(s)/project(s) in vDemo\QA:
Demo/LiftAndShift

Are you sure you want to delete the environment(s)
Demo/env1
Demo/env2?

Note a message is created for each SSIS Catalog Environment that is about to be deleted, and each SSIS Project which consumes these Environments is also listed. Some Projects exists in other SSIS Catalog Folders, which is by design.

If the Reference is Broken…

SSIS Catalog Compare identifies references for which the Catalog Environment does not exist as Broken References. An example of how SSIS Catalog Compare surfaces a broken reference is shown here:

But Wait There’s More!

You can use SSIS Catalog Compare to connect to a different SSIS Catalog instance and script the SSIS Catalog Environment, as shown in this animated gif:

(click to enlarge)

Conclusion

You can still delete SSIS Catalog Environments using SSIS Catalog Compare, but not without being warned (if they participate in References). Why? The SSIS Catalog supports this functionality; so does SSIS Catalog Compare. But if a user accidentally deletes an SSIS Catalog Environment that is needed, SSIS Catalog Compare facilitates scripting SSIS Catalog artifacts, including SSIS Catalog Environments.

As you may glean from this analysis of one potential scenario using SSIS Catalog Environments management, managing SSIS Catalog artifacts in SSIS Catalog Compare can save your bacon.

Learn more about SSIS Catalog Compare!