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 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!
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 – author, blogger, 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?
I occasionally get questions about my companies (yes, plural) so I thought I’d write a post explaining them.
Enterprise Data & Analytics
Enterprise Data & Analytics is a boutique consulting firm. I own the company and I also deliver consulting services. We have a team of experienced engineers who deliver everything from cloud data migrations to data science to performance-tuning to tier-2 support for Azure, AWS, SQL Server, MySQL, SSIS, and other data-related platforms. If we don’t have the experience in-house, we can find someone who can help your team deliver.
Andy Leonard Consulting
Andy Leonard Consulting is the umbrella company for my other activities and includes:
My company with “consulting” in the name does everything except consulting. My company that doesn’t have consulting in the name does consulting. Confusing? Yep. So how’d I get here?
How I Got Here
When I decided to leave my last venture – actually, before I decided to leave, while I was having conversations about how to remain engaged there – I started Andy Leonard Training, Inc. DBA Andy Leonard Consulting. Shortly thereafter, I was having lunch with a friend who is way better at business stuff than I am and he said to me, “Andy, ‘Andy Leonard Consulting’ sounds like a one-person operation.” My response? “There’s a very good reason for that…”
My friend encouraged me to find a different name for my company. And then he found the name Enterprise Data & Analytics and even found the coolest URL: entdna.com.
That’s a good friend right there. And his suggestion was excellent; it wasn’t long before Enterprise Data & Analytics began to grow. Earlier this year, we (quietly) doubled in size.
Data Integration Lifecycle Management Suite
I kept Andy Leonard Consulting around because I wanted to keep software development activities partitioned from my consulting activities, in case my software development ideas failed. That may sound dumb today – three years after it started and with the success of DILM Suite and the book – but back then? I didn’t know if my ideas about DILM would ever come to fruition.
I’m pleased as punch to say now, three years later, that my ideas have produced fruit in the form of several utilities that make up the Data Integration Lifecycle Management Suite:
Catalog Reports – a free and open source collection of SSRS reports for the SSIS Catalog.
SSIS Catalog Compare – the flagship product of DILM Suite, Catalog Compare manages enterprise SSIS Catalogs on-premises and in the cloud! SSIS Catalog Compare can be used to lift and shift SSIS to the cloud, even.
Biml Academy was started to host videos from a week of one-hour free training sessions delivered in 2016. The idea was wildly popular and a team of Biml People delivered even more weeks of Biml Academy training. The free videos and content from those deliveries of Biml Academy remain on the site and remain free.
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
SSIS Catalog Environments may exist in any SSIS Catalog Folder.
SSIS Catalog References define a relationship between an SSIS Catalog Environment and an SSIS Project or Package.
The SSIS Catalog Environment is not required to reside in the same SSIS Catalog Folder as the SSIS Project or Package.
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:
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:
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:
Clicking the Script button after an Environment has been created and configured results in an empty SSMS script window.
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:
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.
A Catalog Reference script is automatically generated from several methods in SSIS Catalog Compare’s Catalog object named CatalogBase.
The script begins with a header documentation similar to that shown here:
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:
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.
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:
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:
The final portion of the script checks for the existence of the Environment Variables and responds accordingly. This is a three-step process:
Drop the Environment Variable if it exists.
Create the Environment Variable.
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:
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.
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
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.
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.
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
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
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:
Connections Properties Reset
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:
Connections Properties Literal Override
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:
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.
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!
I am looking forward to the PASS Summit 2018. I hope to see you there.
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.”
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.
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.
The cookie settings on this website are set to "allow cookies" to give you the best browsing experience possible. If you continue to use this website without changing your cookie settings or you click "Accept" below then you are consenting to this.