The recording of my free webinar titled Using SSIS Framework Community Edition is now available (registration required). You can obtain the free and open source SSIS Framework Community Edition at DILM Suite.
I haven’t advertised this in the past and… I’m not sure why: I donate licenses for SSIS Catalog Compare and (non-free) SSIS Framework Editions – and subscriptions to Biml Academy and SSIS Academy – to individuals who work for charities and non-profit organizations. I am honored to announce our 2019 Scholarships.
Do you work for a charity or non-profit organization? Submit your application today.
In addition to donating free licenses to our software and online training sites, Enterprise Data & Analytics offers a discounted rate to charities and non-profits for consulting services.
We are here to help.™ How may we serve you? Contact us today and let us know!
Join me 20 Sep 2018 at noon ET for a free webinar titled Using SSIS Framework Community Edition!
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!
I was honored to write an article titled Introducing Azure Data Factory Design Patterns featured in this month’s PASS Insights newsletter!
The article covers a couple execution patterns:
I demonstrate a cool SSIS Catalog Browser feature that helps ADF developers configure the Execute SSIS Package activity.
To see it in action, download SSIS Catalog Browser – it’s one of the free utilities available at DILM Suite. Connect to the instance of Azure SQL DB that hosts an Azure Data Factory SSIS Integration Runtime Catalog, select the SSIS Package you desire to execute using the Execute SSIS Package activity, and then copy the Catalog Path from the Catalog Browser status message:
Paste that value into the Package Path property of the Execute SSIS Package activity:
You can rinse and repeat – Catalog Browser surfaces Environment paths as well:
Enjoy the article!
If you have any questions about Azure Data Factory – or need help getting started – please reach out!
Attend my full-day pre-conference session titled Intelligent Data Integration at the PASS Summit 2018 on 5 Nov 2018.
Check out this 1-day course on Fundamentals of Azure Data Factory delivered in cooperation with Brent Ozar Unlimited 10 Dec 2018!
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.
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:
To know if you’re ready for this class, look for “yes” answers to these questions:
I hope to see you there.
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 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?
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.
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:
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.
Along with the free material there is structured training for folks interested in learning Business Intelligence Markup Language (Biml) or more about Biml.
SSIS Academy launched in the summer of 2018. Similar to Biml Academy, SSIS Academy hosts training for folks interested in learning SQL Server Integration Services (SSIS) or learning more about SSIS.
I suppose you could say I am a serial entrepreneur. I enjoy consulting, training, and building software!
I’m excited to announce the availability of SSIS Catalog Compare Documentation!
And now, it’s free!
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.
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:
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:
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:
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.
Learn more about SSIS Catalog Compare!
An SSIS Catalog Environment script is automatically generated from several methods in SSIS Catalog Compare’s Catalog object named CatalogBase.
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:
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:
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:
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:
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.
Learn more about SSIS Catalog Compare!
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:
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:
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:
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:
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.
Learn more about SSIS Catalog Compare!