Packaging SSIS Catalog Deployments

I love the SSIS Catalog. It’s an elegant piece of data integration engineering and I cannot say enough positive things about it. Packaging SSIS Catalog deployments can be tricky, though.

The SSIS Catalog is a framework. Frameworks manage execution, configuration, and logging; and the SSIS Catalog handles each task with grace. Like I said, I love it!

But…

(You knew there was a “but…” coming, didn’t you?)

A Tale of Two Audiences

There are two audiences for the SSIS Catalog, two groups of consumers:

  1. Administrators
  2. Developers
  3. Release Managers

I listed three. Because…

Administrators

(click to enlarge)

SSIS is often administered by database administrators, or DBAs. I admire DBAs. It’s often a thankless job – more like a collection of jobs all rolled into one (and subsequently paid as if its one job…).

I believe the SSIS Catalog interface presented to DBAs in SQL Server Management Studio is sufficient.

My complaint is the SSIS administrator has to expand a handful of nodes in Object Explorer and then right-click to open the SSIS project configuration window and then double-click each referenced SSIS Catalog environment to determine which value is configured for use when an SSIS package is executed.

Click the screenshot above to see what I mean. Configuring SSIS Catalog deployments in SSMS is challenging. I find it… clunky. Once I understood all the windows, what they meant and how to configure an SSIS package and project deployed to the SSIS Catalog, this made sense. But – in my opinion – this interface works against comprehension.

Does this interface work, though? It certainly does. When I teach people how to use the SSIS Catalog, I show them how to use the Object Explorer interface provided in SSMS.

(click to enlarge)

I don’t stop there, however, because I built one solution to the problem. I call my solution SSIS Catalog Browser. If you click to enlarge this image you will note I am viewing the configuration of the same parameter displayed in the SSMS image above. I find this interface cleaner.

Do administrators still need to understand how to configure SSIS Catalog deployments and SSIS packages and projects deployed to the SSIS Catalog? You bet. There is no substitute for understanding. SSIS Catalog Browser surfaces the same metadata displayed in the SSMS Object Explorer. The only difference is Catalog Browser is easier to navigate – in my opinion.

Best of all, Catalog Browser is free.

Developers and Release Managers

SSIS developers and release managers (DevOps release teams) need more functionality. As I wrote in DILM Tiers for the SSIS Enterprise, an enterprise should have a minimum of four Data Integration Lifecycle Management (DILM) tiers to manage enterprise data integration with SSIS. Those tiers need to be:

  1. Development – an environment where SSIS developers build SSIS packages and projects. SSIS developers need permission / rights / roles to utterly destroy the database instances in Dev. If the SSIS developers lack this ability, you have “an environment named Development but not a Development environment.” There is a difference.
  2. Test or Integration – an environment where SSIS developers have permission / rights / roles to deploy, configure, execute, and view logs related to SSIS packages and projects.
  3. UAT or QA (User Acceptance Testing or Quality Assurance or any environment other than Production, Test, or Development) – an environment that mimics Production in security, permission / rights / roles. Developers may (or may not) have read access to logs, source, and destination data. SSIS administrators (DBAs or DevOps / Release teams) own this environment. The person performing the deployment to Production should perform the deployment to UAT / QA / Whatever because I do not want the Production deployment to be the very first time this person deploys and configures this SSIS project.
  4. Production.

I architect data integration environments in this manner to support DILM (Data Integration Lifecycle Management) with SSIS, as I wrote in Data Integration Lifecycle Management with SSIS.

Viewing the contents of an SSIS Catalog is not enough functionality to manage releases. Why, then, do I include developers? Because…

SSIS developers create the initial SSIS Catalog deployments in the DILM DevOps cycle.

I cannot overemphasize this point. Developers need an environment where they are free to fail to build SSIS. They aren’t free to succeed, in fact, unless and until they are free to fail.

Have you ever heard a developer state, “It works on my machine.”? Do you know why it works on their machine? Defaults. They coded it up using default values. The defaults have to work or the developer will not pass the code along to the next tier.

How does an SSIS developer know they’ve forgotten to parameterize values?
How do they figure this out?

It’s impossible to test for missing parameters in the Development environment.

The answer is: SSIS developers must deploy the SSIS project to another environment – an environment separate and distinct from the Development environment – to test for missing parameterization.

To review: SSIS developers need a Development environment (not merely an environment named Dev) and they need a different environment to which they can deploy, configure, execute, and monitor logs.

Error Elimination

Having the SSIS developers build and script the SSIS Catalog deployments eliminates 80% of deployment configuration errors (according to Pareto…).

Having SSIS administrators practice deployment to UAT / QA / Whatever eliminates 80% of the remaining errors.

Math tells me an enterprise practicing DILM in this manner will experience a 4% deployment error rate. (Want to knock that down to 0.8%? Add another tier.)

Packaging SSIS Deployment

I will not go into functionality missing from the SSMS Object Explorer Integration Services Catalogs node (nor the underlying .Net Framework assemblies). I will simply state that some functionality that I believe should be there is not there.

(click to enlarge)

I don’t stop there, however, because I built one solution to the problem. I call my solution SSIS Catalog Compare. If you click the image to enlarge it, you will see a treeview that surfaces SSIS Catalog artifacts in the same way as SSIS Catalog Browser (they share a codebase). You will also see the results of a comparison operation, and the user initiating the packaging of an SSIS folder deployment by scripting the folder and its contents.

The result is a file system folder that contains T-SQL script files and ISPAC files for each SSIS Catalog configuration artifact:

  • Folder
  • SSIS Project
  • Environment
  • Reference
  • Configured Literals

You can use SSIS Catalog Compare to lift and shift SSIS Catalog objects from any environment to any other environment – or from any DILM tier to any other DILM tier – provided you have proper access to said environments and tiers.

This includes lifting and shifting SSIS to the Azure Data Factory SSIS Integration Runtime, also know as Azure-SSIS.

Zip up the contents of this file system folder, attach it to a ticket, and let your enterprise DevOps process work for data integration.

Conclusion

The SSIS Catalog is a framework, and a pretty elegant framework at that. Some pieces are clunky and other pieces are missing.

DILM Suite helps.

DILM Tiers for the SSIS Enterprise

“How many Data Integration Lifecycle Management tiers (DILM tiers) do I need to deploy SSIS, Andy?” If I had a nickel for every time I am asked this question, I would have a lot of nickels. My answer is, “Four.”

Why Four?

I’m glad you asked. Before I answer, I have a question for you: Have you ever been told by a developer, “It works on my machine.”? Do you know why it works on the developer’s machine?

Defaults.

Developers build software that contains configurations information. In the first iteration of building the software, most developers do no externalize – or parameterize – all configuration values. Instead, they hard-code these values into the application, usually as variable defaults.

I can hear you thinking…

“But This Is SSIS, Andy”

And? Oh, you ask because SSIS stands for “SQL Server Integration Services.” I get that. It’s important you understand this:

SSIS development is software development.

SQL Server Integration Services suffers somewhat from its name which contains the name of a popular relational database engine (SQL Server). But SSIS is not a database, and SSIS development is not database development (and most definitely not database administration).

SSIS development is software development.

Software Best Practices Apply

Because SSIS development is software development, software development best practices apply. For example, SSIS developers should source-control and test their SSIS code.

Perhaps testing is that thing you do in Production right after deployment. I have this pithy saying:

While I freely admit this statement is pithy, it is also true. The remainder of this quote is even pithier: “Some software is tested by your soon-to-be-former largest customer.”

I don’t want you to lose customers – especially your largest customer. That’s why I advocate you test your software by developing it on one Data Integration Lifecycle Management – or DILM – tier and then promoting the code to at least two other DILM tiers before deploying to the Production tier.

Tiers. Or Tears. Your Call.

So which tiers do I recommend? I’m glad you asked! i recommend:

  1. Development
  2. Test
  3. QA
  4. Production

Development

You must build the software somewhere. Wherever you build the software is your development environment.

Now let’s take a step back and describe the development environment for an SSIS developer. An SSIS development DILM tier shares some characteristics. In Dev, SSIS developers can:

  • Deploy SSIS projects and packages
  • Overwrite existing SSIS projects and packages
  • Delete existing SSIS projects and packages
  • Execute SSIS packages
  • Manage (deploy, overwrite, delete) SSIS configurations
  • Manage (deploy, overwrite, delete) SSIS metadata
  • Create databases, tables, and files

The most important part of the SSIS Development DILM tier? SSIS developers can perform each action listed above without help from anyone else in the enterprise.

I can hear you thinking, “What if the SSIS developers blow something up, Andy? Huh? HUH?!?” Let me ask another question: What happens when things go awry or amiss in Production? Isn’t the answer to that question, “I fix it.”? If that’s true in Production…

Then you fix it.
In Dev.

In fact (and I know this is a crazy thought) you could actually use a more-open Development tier to test and – perhaps, even – improve break-fix procedures for Production. After all, developers are notoriously excellent testers (I write this as a developer: I can break things like nobody’s business!).

What if the Dev tier becomes a place for you to test your recovery procedures for Production? If your HA/DR procedures work in Development where developers have too much access, then they should work in Production where the environment has been locked down. Am I correct?

Over the years I have found myself making this statement to enterprises: “You have an environment named Development but it is not a Development environment.” How do you know it’s a Development environment? If the developers can destroy it, it’s Dev.

One last thing before I kick this soapbox back under my desk. Consider that developers will be inconvenienced by the destruction of their Dev environment. It may slow them down. Heck, it may cost them a deadline. If that happens, you can take the opportunity to educate them, sharing what happened and why you believe it happened. Maybe they won’t do the dumb thing that burned down Dev again… Maybe.

Would having developers that better understand databases make your job a little easier in the long run?

Test

Once the software works on the SSIS developer’s machine, they need another environment to which they can deploy the SSIS project. Why? Remember those defaults? SSIS packages will always execute in Dev because default values are aimed at local resources for which the SSIS developer has authorization and access.

Which resources? Required access (CRUD – create, read, update, delete) to files and directories, databases, servers, etc. No SSIS developer would claim an SSIS package is ready unless and until she or he successfully executes the package in the SQL Server Data Tools (SSDT) debugger. But a successful SSDT debugger execution does not a completed SSIS package make.

To suss out missing configurations (externalization, parameterization), one needs to move the software to another lifecycle management tier and execute it there. Only then can the developer be sure they’ve externalized required defaults.

In Test, SSIS developers can:

  • Deploy SSIS projects and packages
  • Overwrite existing SSIS projects and packages
  • Delete existing SSIS projects and packages
  • Execute SSIS packages
  • Manage (deploy, overwrite, delete) SSIS configurations
  • Manage (deploy, overwrite, delete) SSIS metadata
  • Create databases, tables, and files

I can hear you thinking, “But Andy, this is the same list of permissions as Dev!”
Yes.

This part of the work must be completed. As professionals, we get to decide when and where the work gets done. Remember, it can always be done in Production. And it will be done in Production unless we are intentional about doing this work on another DILM tier.

Someone needs to do this part of the work. Do you want to do it? Or would you rather have the person most familiar with the code – the SSIS developer – do it? They are not going to catch everything that needs to be externalized, either; not on this pass. But the Pareto Principle informs us that they will catch 80% of the missing externalization.

Deployment to the DILM Test tier serves as the first deployment test. I want the SSIS developers bundling up SSIS deployments. Why? It’s good practice and practice makes perfect.

On Estimation…

One last point regarding software testing: When estimating a software project I begin by estimating how much time it will take to build the software. After this step the calculation for how much time is required to test the software is really easy: It’s the same amount of time required to develop the software.

Andy, are you telling me it takes just as long to test software as to develop it?

Yep.

“Cutting Testing to Save Time”

You can change this relationship by “cutting testing to save time.” But you can never shorten testing, only lengthen it. Decades of software development experience inform me that the costs of testing software increase by an order of magnitude for each tier of testing. The most expensive environment in which to test is Production.

Cutting testing never saves time.

Or cutting testing does save time; the time it takes you to deliver broken software. I can hear some of you thinking, “But can’t SSIS developers just develop better SSIS packages with fewer bugs?” The answer is the same as the answer to the physics question: “Can’t light just be either a wave or a particle, and not both?” Physics says no, light is both a wave and a particle. Andy says the same physics apply to software development along with the Theory of Constraints which tells us losses accumulate, gains do not.

As my friend Aaron Lowe used to say (before he passed away earlier in 2018 – man, I miss him, and others… we lost too many SQL Family members this year…), “Math is hard.”

You must include the cost of lost customers in the calculation.

Remember, all software is tested…

QA

The QA (or UAT or whatever you call the environment sitting just this side of Production) environment should be locked down tighter than a tick. The security in place in this pre-Production tier should be identical to the security in the Production tier. Optimally, all hardware capability and storage capacity in Production will match QA.

If you understand the dynamics of SSIS Data Flow Task internals, it’s possible to test performance with less data on sub-optimal hardware. If you ask me to do it, I’m going to let you know there’s a possibility we will miss something. I’ll be able to tell you for sure after we run some performance test executions in Production (and we will need to do that to be sure, and that will cost both time and money, so how much money are you saving when you save money by buying sub-optimal hardware and storage for QA?).

Deployment to QA should be performed by the same team – optimally by the same individual – that will be performing the deployment to Production. Why? We do not want deployment to Production to be their first experience with deploying this SSIS project. The deployment to QA is another deployment test, this time for the Production release management person. Perhaps this is a DBA. Perhaps this is a Release Management team member. Regardless, this step is their opportunity to practice the deployment to Production.

I can hear you thinking, “What’s the big deal, Andy?” I’m glad you asked. The big deal is: No matter how you execute SSIS in your enterprise, there’s more to the solution than merely the SSIS packages. If you’re running from the file system (and most enterprises execute SSIS from the file system), there are configurations stored in dtsConfig files or database tables that also need to be promoted. Some of the values – such as connection string components – need to be edited in these dtsConfig files at each DILM tier.

The same holds for deployments to the SSIS Catalog. And… if you are deploying (or planning to deploy) SSIS to the Azure Data Factory (ADF) SSIS Integration Runtime, your only option is deployment to an SSIS Catalog. Externalization is accomplished via Catalog Environments, References, and Literals in the SSIS Catalog. Same stuff, different location. But both dtsConfig files and Catalog Environments must be managed.

Since editing will need to occur when it’s time to deploy to Production, I prefer the deploy-er practice the editing along with the deployment. I ask the developer to send the SSIS and scripts or files for managing external configurations metadata to the deploy-er, and I request these configuration artifacts be set up for the Test DILM tier.

Production

If you’ve taken my advice and marched SSIS development through this process or a similar process, the Pareto Principle says you’ve identified at least 96% of the bugs prior to deployment to Production. My experience bears this out; about 4% of my Production deployments fail because some parameter slipped through the cracks walking through Dev, Test, and QA.

I can hear you thinking, “How does that happen, Andy?” Excellent question (and I am glad you asked!). parameters slip through the cracks because Dev and Test may be sharing data sources and/or destinations. It may be possible – desirable, even – to access Development data from the Test tier and vice versa.

Isolation

Remember: I want QA to be identical to Prod, which means I want it equally isolated. Is Production behind a firewall? Is it impossible (without poking a hole in the firewall) to write from Test to Prod? If so, then it should also be impossible to write from Test to QA and from QA to Prod. If Prod has a firewall, QA should have a firewall.

If your enterprise sports this architecture you can beat Pareto’s 4% prediction by 3.2% (an additional 80% of bugs will be caught) and experience a deployment-to-Production success rate of 99.2% (a failure rate of 0.8%).

To quote Foghorn Leghorn, “figures don’t lie.”

Conclusion

Is this the only way to properly manage SSIS in an enterprise? Goodness, no.

You can add more tiers to your Data Integration Lifecycle as needed. You can technically get by with three tiers (Dev, Test, Prod) but I don’t recommend it unless you’re the person wearing all the hats. Even then, the more times you test deployment, the more bugs you will catch prior to Production, and the fewer bugs you will deploy to Production.

It’s always easier to deploy more bugs but it is never less-expensive and it never saves time. Plus it may cost you your soon-to-be former largest customer.

Honored to Present Faster SSIS at Triad SQL PASS BI 30 Oct!

I am honored to join my friends at the Triad SQL PASS BI Group in Greensboro North Carolina 30 Oct 2018, where I will present Faster SSIS!

Abstract

Ever wonder why SSIS runs so slowly? Watch SSIS author Andy Leonard as he runs test loads using sample and real-world data, and shows you how to tune SQL Server 2016 Integration Services (SSIS 2016) packages. We’ll start by experimenting with SSIS design patterns to improve performance loading AdventureWorks data. We will implement different change detection patterns and compare execution performance for each. Then, we’ll explain a Data Flow Task’s bottleneck when loading binary large objects – or Blobs. Finally, we’ll demonstrate a design pattern that uses a Script Component in a Data Flow to boost load performance to MySql, whether on-premises or in the cloud. Prerequisites: Familiarity with SQL Server Integration Services (SSIS).

Register today! I hope to see you there.

:{>

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.

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.

:{>

PASS Summit 2018 Starts in 10 Weeks!

Can you believe the PASS Summit 2018 begins only 10 weeks from today (27 Aug 2018)? I confess, this is sneaking up on me fast!

I will be there. Will you?

Where Can You Find Andy at the PASS Summit 2018?

Precon!

Monday 5 Nov 2018, I’m delivering a full-day pre-conference session titled Intelligent Data Integration with SSIS. I’m going to cover  everything listed at that link but there is an update about my precon content:

There will be Azure Data Factory content and demos!

Why this addition? Two reasons:

  1. My presentation titled Faster SSIS was selected. I usually include the three Faster SSIS demos in my precon. This time, you can just view the Faster SSIS session to see those demos.
  2. may have something cool and new to share about Azure Data Factory that is currently under NDA! Stay tuned…

Enterprise Data & Analytics is Exhibiting!

That’s right, you can find me in the Exhibition Hall! Enterprise Data & Analytics is exhibiting at the PASS Summit 2018!

Have an SSIS or Biml or ADF question? Stop by our booth!
Want to grab a selfie with me or Nick? Stop by our booth!
Want me to autograph your book? Stop by our booth!
Need some consulting or training help? Stop by our booth!

I’m so excited about this – I can hardly wait. We’ll have more information about specific dates and times when I will be manning the booth in coming weeks.

Presenting Faster SSIS

At the time of this writing, the session schedule has not yet been published. PASS has published a general schedule. Keep checking for details!

Conclusion

I am looking forward to the PASS Summit 2018. I hope to see you there.

:{>

What’s the Best Book for Learning SSIS?

Best Book for Learning SSISI am often asked the question, “Andy, what’s the Best Book for Learning SSIS?” There are some really good books out there that will help you learn SSIS (and I am co-author of several).

The book from which I learned the most about SSIS is Extending SSIS 2005 with Script by Donald Farmer.

Why do I consider this book the best?

Donald Farmer (TreeHive Strategy | Interview on the Data Driven Podcast) is a data engineering industry expert. He led the team that developed Microsoft SQL Server Integration Services. He’s technical, a great communicator (speaking and writing), and fantastic at solving business problems.

In Extending SSIS 2005 with Script, Donald discusses and shares demo code that informs he reader of the problems SSIS is designed to solve, and how SSIS is designed to solve those problems. Best of all, you learn this from one of the minds that designed the product.

I consider the Data Flow task to be the heart of SSIS – and the SSIS Script Component of the Data Flow Task is arguably the most difficult and most flexible component in the SSIS Data Flow. If you get your mind around the Script Component, you have a pretty good handle on how the SSIS Data Flow works, in my humble opinion.

I am aware of SQL Server Integration Services (SSIS) courses that are based on the material contained in this book. It’s that good.

“This book is over 10 years old, Andy…”

Yep. The information remains cogent. If I have to choose one book, Extending SSIS 2005 with Script remains the best book for learning SSIS in my opinion.

The Best Book for Learning SSIS

How serious am I in this recommendation? I just ordered a copy for one of the independent consultants at Enterprise Data & Analytics who is learning more about SSIS. I want her to become a senior SSIS developer in 2019 and I consider this book vital to accomplishing that goal.

Full disclosure: The links to the book above pass through the Amazon Affiliate Program. If that bugs you, please click here for a clean link to the book.

Enjoy.

:{>

Free Webinar – Designing a Custom ADF SSIS Execution Framework

Folks, I’m not going to even pretend to be cool and collected about this webinar about designing an ADF SSIS Execution Framework. I’ve been waiting for this one for a loooooooong time! The next (free!) webinar in the series I’m calling the Summer-O’-ADF is Designing a Custom ADF SSIS Execution Framework.

It’s at noon EDT Thursday 12 Jul 2018.

Why Am I So Excited?

I’ve been designing data engineering frameworks since the days of DTS (Data Transformation Services). Enterprise Data & Analytics (EDNA) implements SSIS Frameworks for enterprises. We even give away a free version of a framework at DILM Suite (DILM == Data Integration Lifecycle Management). It’s called SSIS Framework Community Edition and it’s not only free, it’s open source.

I wrote about SSIS Framework Community Edition and Data Integration Lifecycle Management in my latest book: Data Integration Lifecycle Management with SSIS.

I’m excited about this webinar because it combines a long-held passion – DevOps for SSIS – with a new passion – Azure Data Factory (ADF)! I cannot wait to show you how!

If you haven’t seen my webinars titled The Azure Data Factory Controller Design Pattern and ADF Controller Design Pattern with the SSIS Integration Runtime (also part of the Summer-O’-ADF webinar series), you may want to check them out first (registration required).

Register today!

:{>