Two (or More) Kinds of Developers

I’ve made statements about “two kinds of developers” for years. These statements are false inasmuch as all generalizations are false. The statements are not designed to be truisms. They are designed to make people think.

Last week – while presenting a full-day pre-conference session at the PASS Summit 2018 and again when delivering a session about Faster SSIS – I repeated the sentiment shown at the top of this post:

There are two kinds of developers:
1) Those who use source control; and
2) Those who will.

I follow up with: “Because if you do not use source control, you will lose code one day and it will break your heart.” Audience members laugh and the point is made.

More Than Two

There are myriad types of developers. And that’s a good thing. Why? Because there are myriad problems developers face in the wild and those problems need to be solved.

There are no one-size-fits-all solutions. If you attended the PASS Summit last week you likely saw some really cool demos. I know I did. And you may have thought – or even been told – that this, this right here is the answer for which you’ve searched your entire career.

There’s a possibility that the people selling you on whatever-this-is are absolutely correct.
There’s a greater possibility that they are less than absolutely correct.

I write this not to disparage anyone’s solution (or my own solutions). Promise.
I write this to dissuade the disparaging of anyone else’s solution (because that also happens).

The Bottom Line

Goldilocks. The bottom line is we all want the Goldilocks solution. We want the just-right solution that maximizes efficiency and minimizes complexity.

That’s just hard.

I can hear you thinking, “Why is maximizing efficiency and minimizing complexity hard, Andy?” I’m glad you asked. Solutions are a moving target, part art and part science, and the only way to learn where and when to draw the art-science line is experience.

A Moving Target

Maximizing efficiency and minimizing complexity is hard because it’s not at all the bottom line; it’s a line in the middle – a balancing of mutually-exclusive demands on your time, expertise, and energy.

Plus, it shifts.

Everything scales. Things scale either up and / or out or they scale down and / or in. In general (generality warning!), down and in is “bad” and up and out is “good.”

Experience Matters

Experienced architects understand subtle nuances; the art part of the art / science of enterprise software. When you speak with an experienced architect, you may hear her say, “It depends,” often. Good architects will finish the sentence and share at least some of the things upon which “it depends.” Less-experienced architects will present naked scalars and polarized advice.

Naked Scalars

Naked scalars are numeric values in a vacuum. They are unsupported because most are unsupportable. In other words, they are lies. Now, “lies” is a pretty harsh word. I prefer an engineering definition for the word “truth” that sounds an awful lot like the oath witnesses are asked to swear in US courts:

“Do you promise to tell the truth, the whole truth, and nothing but the truth, so help you God?”

This oath covers falsehoods that are shared, yes; but it also covers omissions of the truth.

Examples of naked scalars:

  • “97% of engineers believe ____.”
  • “10% of the people I know have practiced ____ successfully.”

Polarized Advice

Polarized advice can be a special case of naked scalars, advice focused on 0% and 100%. Polarized advice may or may not include scalars (naked or otherwise).

Examples of polarized advice:

  • “I’ve never seen a good use case for ____.”
  • “You should always ____.”

Are naked scalars and polarized advice always bad and wrong? Nope. That would be a generality (and we covered generalities already, did we not?).

Managing the Risk of Inexperience

What exactly is a consultant communicating when they engage naked scalars or polarized advice?
They are signalling a lack of experience.
They are, in effect, stating, “I do not have experience with ____.”

How do you manage the risk of inexperience?
You hire people – architects, especially – who understand there are good reasons systems are designed as they are. They will say things like, “I’m not sure why this was designed this way,” and mean it. It’s not a criticism; it’s an admission of curiosity. Trust me on this: You want curious consultants. They are more likely to identify a solution that solves the problem you are trying to solve in a way that doesn’t create new problems. Returning to the good reasons systems are designed as they are…

  1. Are (or were) the good reasons, well, good? Sometimes.
  2. Do the good reasons scale? Sometimes.
  3. Do the good reasons stand the test of time? Sometimes.

Good architects discern the baby from the bath water. Their experience separates good architects from the crowd. Not-as-good architects are less flexible, less willing to learn, and loathe to admit mistakes.

Let’s face facts, though: All architects and developers know what they know and don’t know what they don’t know. Better architects recognize these uncomfortable truths and mitigate them.

One way to mitigate inexperience – the best way, in my opinion, is to work with others.

The Story Of Us

At Enterprise Data & Analytics, our consultants and architects work together as a team. Our diverse group is a strength, bringing perspective to bear on the problems you are trying to solve. Our experience levels vary, the software and tools with which we work vary, and our demographics vary. As owner, I am honored to lead a team from diverse cultural – as well as diverse technical – backgrounds.

I didn’t set out to build a cultural- / age- / gender-diverse team. I set out to find the best people – to do what Jim Collins describes as “getting the right people on the bus.”

I found, though, that focusing on getting the right people on the bus had the side-effect of building a cultural- / age- / gender-diverse team.

As an added bonus, people of different genders approach problem-solving differently. People of different ethnicity pick up on stuff – especially cultural stuff, including enterprise culture – that people of other cultures miss.

EDNA‘s diversity is a strength that emerged unintentionally, but emerged nonetheless. As Chief Data Engineer, it’s very cool to watch our less-experienced consultants growing into more-experienced consultants and architects, while at the same time watching our people interact and perform as a team – each member catching stuff and contributing ideas because of their unique perspectives.

Cost Value

I can hear some of you thinking, “We’re on a budget here. Don’t good architects cost more than less-than-good architects, Andy?” I feel you. The answer is, “No. Good architects cost less than less-than-good architects.”

I can prove it. Because math. (Read that post for more information…)

It’s often accurate that good architects cost more per hour than less-than-good architects. Do you know why good architects charge more per hour?

Because they are worth more per hour.

(Generality!)

But consider this: “Time will tell” is a tried and true statement. Like good wine, the likelihood a generality is accurate improves with age. If enterprises continue to hire an organization – like Enterprise Data & Analytics or any other firm – to help them solve the problems they are trying to solve, then the folks shouting them down may be doing so in an effort to compete. Competition is fine, but I never hire anyone who talks bad about other clients or the competition. Why? They’ve demonstrated the capacity to talk bad about me at some later date.

Conclusion

I love our team!
I love our expertise!
I love our diversity!
I love that we always deliver value!

Contact me to learn more.

:{>

Want a Free #DILM Book? See Me at the #PASSsummit 2018!

I’ll be at the PASS Summit 2018 next week. I’m delivering a full-day precon Monday, presenting Faster SSIS and participating in the BI & Data Visualization Panel Wednesday, and Enterprise Data & Analytics is exhibiting Wednesday through Friday.

“You Mentioned a Free Book…”

Oh. Yeah. That.

I will be giving away free copies of my latest book: Data Integration Life Cycle Management with SSIS: A Short Introduction by Example starting Wednesday! Would you like to score a free copy? You’ll have to catch me carrying them in the Washington State Convention Center.

They are free while supplies last.

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 for Wednesday, 7 Nov 2018 at 1:30 PM PT in room 612. 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. I’ve added some Azure Data factory to the precon in place of these three demos!
  2. I am participating in a panel discussion titled BI & Data Visualization Panel Wednesday, 7 Nov 2018 at 3:15 PM PT in room TCC Yakima 1.

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!

If you are attending the PASS Summit 2018, please catch me for a free book while supplies last. If I’m out of physical copies, come see me anyway… we will work something out. Promise!

:{>

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.

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.

Anatomy of a Catalog Reference Script in SSIS Catalog Compare

A Catalog Reference script is automatically generated from several methods in SSIS Catalog Compare’s Catalog object named CatalogBase.

Header Documentation

The script begins with a header documentation similar to that shown here:

(click to enlarge)

When executed, the Project Reference script header documentation portion displays in the Messages output window of SQL Server management Studio (SSMS):

The statements returned in the Messages tab of SQL Server Management Studio (SSMS) are designed to be copied and stored. The authors recommend enterprises use a ticketing system to manage and track the deployment of enterprise scripts. Before closing a ticket to create a Catalog Reference, the deploying agent is advised to copy the contents of the Messages tab and paste them into the Notes section of the ticket for auditing purposes.

Status and Conditions Checks

The next portion of the Catalog Reference script tests for the existence of prerequisite artifacts:

Prerequisites for a Project Reference include:
• Catalog Folder
• SSIS Project
• Catalog Environment

When executed, this portion of the script returns status messages for prerequisites similar to those shown below:

Create the Reference

The next portion of the Reference Script creates the Reference which is a relationship between an SSIS Catalog Environment and an SSIS Project (or Package). An example of this portion of the script is shown here:

Once this portion of the script is executed, a message similar to that shown in the figure above is returned if the reference is created:

If the script detects the reference already exists, a message similar to that shown below is returned:

Clear the Parameter Value

The next portion of the Reference script clears the parameter value:

The messages generated by this portion of the References script appear similar to those show below:

Set the Parameter Value

The final portion of the script builds the Reference Mapping – the relationship between a Catalog Environment Variable and a Parameter that the Environment Variable value will override at execution-time:

When executed, this portion of the script generates a message similar to that shown below:

After executing the Project Reference script in the target instance, click the Refresh button in SSIS Catalog Compare to observe the updated SSIS Catalog state of the target SSIS Catalog instance as shown here:

Conclusion

As you may glean from this analysis of one script generated for SSIS Catalog References management, the Transact-SQL for scripting SSIS Catalog artifacts in SSIS Catalog Compare is rigorous and includes several existence and error-condition checks prior to performing any updates. The script is designed to be idempotent, as well, meaning the script will succeed and the results will be repeatable and predictable each time the script is executed – and that the script itself is re-executable.

Learn more about SSIS Catalog Compare!

Anatomy of an Environment Script in SSIS Catalog Compare

An SSIS Catalog Environment script is automatically generated from several methods in SSIS Catalog Compare’s Catalog object named CatalogBase.

Declarations and Header Documentation

The script begins with a declaration of Transact-SQL parameters that support SSIS Catalog Environment Variables contained within an SSIS Catalog Environment. These parameters are placed at the top of the script for easy access by release management personnel, DBAs, DevOps, or other specialists responsible for deployment and deployment testing:

(click to enlarge)

Script documentation follows and is recorded as both Transact-SQL documentation and then printed so it will be part of the output found in the Messages window:

When executed, this portion of the script outputs messages suitable for copying and pasting into the Notes field of a ticket used by enterprise DevOps teams:

The last piece of the script header is the deployment output message, for which the script is shown here:

When executed, this portion of the script produces output similar to that shown here:

Status and Conditions Checks

The next section of the artifact script checks for the existence of the Catalog Folder which is the only prerequisite for a Catalog Environment. An example:

When executed, this portion of the script produces a message similar to that shown:

Catalog Environment Check / Creation

The next portion of the script checks for the existence of the Catalog Environment and creates it if it does not exist:

If the script creates the Environment, the output appears similar to this:

If the environment does not exist, the script informs the individual executing the script:

Environment Variables

The final portion of the script checks for the existence of the Environment Variables and responds accordingly. This is a three-step process:

  1. Drop the Environment Variable if it exists.
  2. Create the Environment Variable.
  3. Set the Environment Variable value.

If the Environment Variable exists the script drops it. Why? SSIS Catalog Compare wants to be sure the environment variable is created with the proper data type and initial values.

The next step is creation of the Environment Variable.

Finally, the Environment Variable value is set. This is somewhat redundant as the value of the Environment Variable is initialized when the Environment Variable is created in the previous step.

An example of the Transact-SQL for this portion of the script is shown here:

After executing this portion of the script, messages similar to those shown below are displayed in the Messages output:

If the SSIS Catalog Environment Variable exists when the script is executed, it is first dropped and the output messages reflect this:

Catalog Environments, Post-Script-Execution

After executing the Catalog Environment script in the target instance, click the Refresh button in SSIS Catalog Compare to observe the updated SSIS Catalog state of the target SSIS Catalog instance:

Conclusion

As you may glean from this analysis of one script generated for SSIS Catalog Environments management, the Transact-SQL for scripting SSIS Catalog artifacts in SSIS Catalog Compare is rigorous, containing several existence and error-condition checks prior to performing any updates. The script is designed to be idempotent, as well, meaning the script will succeed and the results will be repeatable and predictable each time the script is executed – and that the script itself is re-executable.

Learn more about SSIS Catalog Compare!

Anatomy of an SSIS Catalog Connection Literals Script in SSIS Catalog Compare

An SSIS Catalog Connection Literals script is automatically generated using several methods in SSIS Catalog Compare’s Catalog object named CatalogBase.

Declarations and Header Documentation

(click to enlarge)

The script begins with a declaration of Transact-SQL parameters that will be used to provide literal overrides. These parameters are placed at the top of the script for easy access by release management personnel, DBAs, DevOps, or other specialists responsible for deployment and deployment testing.

(click to enlarge)

The next section provides feedback for the professional deploying the script. The feedback includes the same information contained in the head documentation, followed by deployment feedback.

(click to enlarge)

When executed, this documentation section returns messages similar to that shown here:

These messages are intended to be copied and stored in the Notes field of a ticketing system in a DevOps enterprise. Note the detail contained herein:

  • Script Name – the path to the file used to perform the operation.
  • Generated From – the SQL Server instance of the SSIS Catalog host from which the script was generated.
  • Catalog Name – redundant at present because all SSIS Catalogs are named “SSISDB.”
  • Folder Name – the name of the SSIS Catalog Folder that contains the scripted artifact.
  • Project Name – the name of the SSIS Project that contains the scripted artifact.
  • Project Connection Name – the name of the SSIS Project Connection.
  • Generated By – the name of the enterprise account used to generate the artifact’s script.
    • Note: SSIS Catalog Compare respects the security model of the SSIS Catalog. Windows Authentication is required to perform many SSIS Catalog operations.
  • Generated Date – the date and time the script was generated.
  • Generated From – the version of CatalogBase used in the generation of the artifact script.
    • Executing On – the name of the machine on which CatalogBase was running.
  • Deployed to Instance – the SQL Server instance hosting the target SSIS Catalog.
  • Deploy Date – the date and time the deployment script was executed.
  • Deploy By – the enterprise account used to deploy the artifact script.

Script Support Declarations

(click to enlarge)

The next section of the artifact script is the declaration of parameters used to support the remained of the script’s operations. An example is shown here:

Status and Conditions Checks

(click to enlarge)

The next section of the artifact script checks for the existence of required artifacts like Catalog Folders and Projects. An example of checks for the existence of a Catalog Folder and an SSIS Project.

An example of messages generated by this portion of the script are shown below:

If required preceding artifacts do not exist in the target SSIS Catalog, an error message is generated – similar to that seen here:

(click to enlarge)

Connections Properties Reset

 

(click to enlarge)

Unlike other scripts generated by SSIS Catalog Compare, Connections Literals scripts reset all related properties (parameters) for a connection manager that are not overridden via Reference Mapping. The portion of the script that manages clearing connection property parameter values is shown here. The results of the execution of this portion of the artifact script are shown below:

(click to enlarge)

Connections Properties Literal Override

(click to enlarge)

The final section of the Connection Property Literal script contains the literal override. This section of the script is laden with existence checks and conditionals.

The results of executing this section of the script are shown below:

(click to enlarge)

Conclusion

As you may glean from this analysis of one script generated for Project Connection Literals management, the Transact-SQL for scripting SSIS Catalog artifacts in SSIS Catalog Compare is rigorous, containing several existence and error-condition checks prior to performing any updates. The script is designed to be idempotent, as well, meaning the script will succeed and the results will be repeatable and predictable each time the script is executed – and that the script itself is re-executable.

Learn more about SSIS Catalog Compare!

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.

:{>

Lift and Shift an SSIS Catalog…

… to a different instance on-premises.
… to Azure Data Factory SSIS Integration Runtime.

This screenshot is from the SSIS Catalog Compare documentation. You can script an entire SSIS Catalog using SSIS Catalog Compare!

This awesome functionality can save you hours (days, weeks, even!) when starting a new project or updating an existing project.

This awesome functionality supports DevOps with SSIS.

Learn more about SSIS Catalog Compare.

View the recording of Use SSIS Catalog Compare to Lift and Shift SSIS to Azure Data Factory (registration required).

I am a Product Manager (No, I’m Not… But I Can Explain)

I was about to click the Publish button for this post about my new role as a product manager when my friend and brother, Scott, called. I mentioned it and Scott said, “You’re not just a product manager. You’re a problem-solver.”

Scott is correct. Hence the parentheses in the title of this post.

“Is This The Best Use of Your Time?”

A friend used to ask me this question all the time. It’s a fantastic question. The question made me pause and think. Pausing and thinking is mostly good most of the time. When is pausing and thinking not good?

When it delays action you desperately need to take; action you are uniquely positioned to take.

Evolution of a Response

For a couple years my response to this question was to answer, “No,” and then I would feel silly for considering the thing I had wanted to do. But then a funny thing began to happen: I increasingly felt cornered by “the best use of my time.”

<TimeOut>

Let’s pause right here and consider a different perspective. I was being selfish. I wanted to do what I wanted to do, the consequences be damned.

</TimeOut>

This is an accurate description right up until the point where I didn’t do what I wanted to do – what I felt desperately needed to be done – to help people attempting DevOps with SSIS. Instead I stuffed the ideas back down inside, put my head down, and went back to doing what was a good use of my time.

Was I in any position to make this determination?
Was I qualified to make this call?

Yes. Yes I was.

Coming Out of the Corner

Over time, my response evolved. I stopped feeling silly about wanting to solve DevOps for SSIS and started feeling silly for placing myself into a position which offered more obstacles than opportunities.

The short version of a long story is: I extricated myself from that corner.

Before I did anything else – I am not making this up, I can produce witnesses – I started writing SSIS Catalog Compare. I started coding within minutes of announcing my decision.

I did not know what I was doing.
I am still learning.
I feel like I only recently worked my way up to being a n00b C# developer.
I didn’t know anything about designing a software product. I know more now but (still) not enough.
I didn’t know anything about marketing a software product.
I didn’t know anything about managing a software product.

I continue to learn. Here’s the latest thing I’ve learned:

I am not afraid.

I am not afraid of not knowing. Frank Herbert got it right (in Dune): Fear is the mind-killer. The only way to learn is by using my mind – the same mind battling fear of not knowing.

Was the question about the bet use of my time well-intentioned? Absolutely. My friend was watching out for me, he had my back. I learned from the experience and walked away more mature and with broader perspective. I learned. I grew. I would not be where I am now had I not.

I am a Problem-Solver

Scott reminded me I am a problem-solver. I always have been a problem-solver. Lord willing, I will continue to be a problem-solver.

Becoming a software product manager was required in order to solve a problem.

Next.