HowTo: Install SSIS Framework Community Edition

SSIS Framework Comunity Edition is available from DILM Suite (Data Integration Lifecycle Management Suite). SSIS Framework CE is not only free, it is open source.

To install SSIS Framework Community Edition, visit DILM Suite and click SSIS Framework Community Edition:

When the SSIS Framework Community Edition page opens, click the first link:

This link will take you to GitHub where you may download the code:

Unzip the file and open the PDF titled SSIS Framework Community Edition Documentation and User Guide:

The documentation includes instructions for creating an SSIS Catalog on-premises – or an instance of Azure-SSIS in Azure Data Factory:

The Documentation and User Guide next walks you through the process of upgrading the SSIS packages to the latest version of SSIS, followed by instructions for deploying the Framework project:

The next step is to execute T-SQL scripts that configure the project you just deployed and also create the SSIS Framework objects:

The remainder of the document walks you through testing and customizing the SSIS Framework for your own needs. A customizing primer is included, in which I demonstrate how to extend Framework functionality.

Learn More

Click here to learn about enterprise SSIS Framework editions.
Click here to learn about SSIS Framework Browser, which is free.

The Recording for Loading Medical Data with SSIS is Available

Kent Bradshaw and I had a blast delivering Loading Medical Data with SSIS earlier today! If you missed the webinar and, perhaps more importanly, coupon codes to save on upcoming Enterprise Data & Analytics Training.

Enjoy the video!

We demonstrated a handful of (free!) DILM Suite (Data Integration Lifecycle Management) utilities:

Join us next week for another free webinar: Enterprise SSIS Execution!

Regarding SSIS Frameworks, Part 1 – Execution

Why Use a Framework?

The first answer is, “Maybe you don’t need or want to use a framework.” If your enterprise data integration consists of a small number of SSIS packages, a framework could be an extra layer of hassle metadata management for you that, frankly, you can live without. We will unpack this in a minute…

“You Don’t Need a Framework”

I know some really smart SSIS developers and consultants for whom this is the answer.
I know some less-experienced SSIS developers and consultants for whom this is the answer.
Some smart and less-experienced SSIS developers and consultants may change their minds once they gain at-scale experience and encounter some of the problems a framework solves.
Some will not.

If that last paragraph rubbed you the wrong way, I ask you to read the next one before closing this post:

One thing to consider: If you work with other data integration platforms – such as DataStage or Informatica – you will note these platforms include framework functionality built-in. Did the developers of these platforms include a bunch of unnecessary overhead in their products? No. They built in framework functionality because framework functionality is a solution for common data integration issues encountered at enterprise scale.

If your data integration consultant tells you that you do not need a framework, one of two things is true:
1. They are correct, you do not need a framework; or
2. They have not yet encountered the problems a framework solves, issues that only arise when one architects a data integration solution at scale.

– Andy, circa 2018

Data Integration Framework: Defined

A data integration framework manages three things:

  1. Execution
  2. Configuration
  3. Logging

This post focuses on…

Execution

If you read the paragraph above and thought, “I don’t need a framework for SSIS. I have a small number of SSIS packages in my enterprise,” I promised we would unpack that thought. You may have a small number of packages because you built one or more monolith(s). A monolith is one large package containing all the logic required to perform a data integration operation – such as staging from sources.

(click to enlarge)

The monolith shown above is from a (free!) webinar Kent Bradshaw and I deliver 17 Apr 2019. It’s called Loading Medical Data with SSIS. We refactor this monolith into four smaller packages – one for each Sequence Container – and add a (Batch) Controller package to execute them in order. I can hear some of you thinking…

“Why Refactor, Andy?”

I’m glad you asked! Despite the fact that its name contains the name of a popular relational database engine (SQL Server), SQL Server Integration Services is a software development platform. If you search for software development best practices, you will find something called Separation of Concerns near the top of everyone’s list.

One component of separation of concerns is decoupling chunks of code into smaller modules of encapsulated functionality. Applied to SSIS, this means Monoliths must die:

A slide from the Expert SSIS training…

If your SSIS package has a dozen Data Flow Tasks and one fails, you have to dig through the logs – a little, not a lot; but it’s at 2:00 AM – to figure out what failed and why. You can cut down the “what failed” part by building SSIS packages that contain a single Data Flow Task per package.

If you took that advice, you are now the proud owner of a bunch of SSIS packages. How do you manage execution?

Solutions

There are a number of solutions. You could:

  1. Daisy-chain package execution by using an Execute Package Task at the end of each SSIS package Control Flow that starts the next SSIS package.
  2. Create a Batch Controller SSIS package that uses Execute Package Tasks to execute each package in the desired order and degree of parallelism.
  3. Delegate execution management to a scheduling solution (SQL Agent, etc.).
  4. Use an SSIS Framework.
  5. Some combination of the above.
  6. None of the above (there are other options…).

Dasiy-Chain

Daisy-chaining package execution has some benefits:

  • Easy to interject a new SSIS package into the workflow, simply add the new package and update the preceding package’s Execute Package Task.

Daisy-chaining package execution has some drawbacks:

  • Adding a new package to daisy-chained solutions almost always requires deployment of two SSIS packages – the package before the new SSIS package (with a reconfigured Execute Package Task – or an update to the ) along with the new SSIS package. The exception is a new first package. A new last package would also require the “old last package” be updated.

Batch Controller

Using a Batch Controller package has some benefits:

  • Relatively easy to interject a new SSIS package into the workflow. As with daisy-chain, add the new package and modify the Controller package by adding a new Execute Package Task to kick off the new package when desired.

Batch-controlling package execution has some drawbacks:

  • Adding a new package to a batch-controlled solutions always requires deployment of two SSIS packages – the new SSIS package and the updated Controller SSIS package.

Scheduler Delegation

Depending on the scheduling utility in use, adding a package to the workflow can be really simple or horribly complex. I’ve seen both and I’ve also seen methods of automation that mitigate horribly-complex schedulers.

Use a Framework

I like metadata-driven SSIS frameworks because they’re metadata-driven. Why’s metadata-driven so important to me? To the production DBA or Operations people monitoring the systems in the middle of the night, SSIS package execution is just another batch process using server resources. Some DBAs and operations people comprehend SSIS really well, some do not. We can make life easier for both by surfacing as much metadata and operational logging – ETL instrumentation – as possible.

Well architected metadata-driven frameworks reduce enterprise innovation friction by:

  • Reducing maintenance overhead
  • Batched execution, discrete IDs
  • Packages may live anywhere
Less Overhead

Adding an SSIS package to a metadata-driven framework is a relatively simple two-step process:

  1. Deploy the SSIS package (or project).
  2. Just add metadata.

A nice bonus? Metadata stored in tables can be readily available to both production DBAs and Operations personnel… or anyone, really, with permission to view said data.

Batched Execution with Discrete IDs

An SSIS Catalog-integrated framework can overcome one of my pet peeves with using Batch Controllers. If you call packages using the Parent-Child design pattern implemented with the Execute Package Task, each child execution shares the same Execution / Operation ID with the parent package. While it’s mostly not a big deal, I feel the “All Executions” report is… misleading.

Using a Catalog-integrated framework gives me an Execution / Operation ID for each package executed – the parent and each child.

“Dude, Where’s My Package?”

Ever try to configure an Execute Package Task to execute a package in another SSIS project? or Catalog folder? You cannot.* By default, the Execute Package Task in a Project Deployment Model SSIS project (also the default) cannot “see” SSIS packages that reside in other SSIS projects or which are deployed to other SSIS Catalog Folders.

“Why do I care, Andy?”

Excellent question. Another benefit of separation of concerns is it promotes code reuse. Imagine I have a package named ArchiveFile.dtsx that, you know, archives flat files once I’m done loading them. Suppose I want to use that highly-parameterized SSIS package in several orchestrations? Sure, I can Add-Existing-Package my way right out of this corner. Until…

What happens when I want to modify the packages? Or find a bug? This is way messier than simply being able to modify / fix the package, test it, and deploy it to a single location in Production where a bajillion workflows access it. Isn’t it?

It is.

Messy stinks. Code reuse is awesome. A metadata-driven framework can access SSIS packages that are deployed to any SSIS project in any SSIS Catalog folder on an instance. Again, it’s just metadata.

*I know a couple ways to “jack up” an Execute Package Task and make it call SSIS Packages that reside in other SSIS Projects or in other SSIS Catalog Folders. I think this is such a bad idea for so many reasons, I’m not even going to share how to do it. If you are here…

… Just use a framework.

SSIS Framework Community Edition

At DILM Suite, Kent Bradshaw and I give away an SSIS Framework that manages execution – for free! I kid you not. SSIS Framework Community Edition is not only free, it’s also open source.

The Next Mastering the SSIS Catalog Course Delivery is 11 Jun 2019

I’m excited to announce the next delivery of Mastering the SSIS Catalog will be the morning of 11 Jun 2019! This half-day course brings you up to speed on the SSIS Catalog quickly and efficiently, and we have great fun along the way.


About Mastering the SSIS Catalog

Data integration is the foundation of data science, machine learning, artificial intelligence, business intelligence, and enterprise data warehousing. This instructor-led training class is specifically designed for SQL Server Integration Services (SSIS) professionals and DBAs responsible for deploying, configuring, and managing data integration at enterprise-scale who want to learn more about the SSIS Catalog.

You will learn:
– Creating an SSIS Catalog
– Deploying SSIS projects to the SSIS Catalog
– SSIS Catalog Execution options
– Configuring connection strings and parameter values.

Agenda

1. Introducing the SSIS Catalog
2. Deploying SSIS projects.
3. SSIS execution options.
4. Configuring parameters and connection managers.

Register today!

I hope to see you there.

:{>

SSIS, Docker, and Windows Containers, Part 6 – Executing Packages in an SSIS Catalog in a Container

In the previous six posts I:

  • examined how to install Docker for Windows, pull a test image, search for a SQL Server on Windows image, install and interact with it (Part 0)
  • shared why I want to do all this (Part 1)
  • shared one way to execute SSIS on a container in Part 2
  • shared a failed attempt to add an SSIS Catalog to a SQL Server-on-Windows container in Part 3
  • shared a successful attempt to create an SSIS Catalog in a container (Part 4)
  • shared how to deploy SSIS Projects to an SSIS Catalog in a container (Part 5)

View in SSMS

I can view the deployment in SQL Server Management Studio (SSMS) after refreshing the SSISDB node in SSMS Object Explorer’s Integration Services Catalogs node:

On (Attempting to) Execute SSIS Packages Using SSMS

An aside regarding attempting SSIS package execution from SSMS connected to an instance of SQL Server in a container (using the runas /netonly trick shared earlier: It appears to work, but doesn’t. The package execution is created but “hangs” in Pending Execution status:

(click to enlarge)

Execute SSIS Packages Using PowerShell (Kinda)

I write “kinda” here because I’m only using PowerShell as, well, the shell. I’m back to where started, executing SSIS packages using the dtexec command:

dtexec /Server “localhost” /ISServer “\SSISDB\Test\TestSSISProject\ReportAndSucceed.dtsx”

There is one big difference, however. This package is being executed from within the SSIS Catalog on the container and not from the file system.

Are We There Yet?

No. There’s more I’ve learned but haven’t (yet) shared.

I wrote this series over the past several weeks, completing the editing for this post 18 Mar 2019. In less than two weeks I get to hang out with Liz Baron and Sreeja Pullagura from Teach For America at
SQL Saturday #813 – Boston BI Edition on 30 Mar 2019 and learn what new and exciting things they’ve discovered since we last hung out. The TFA team has done amazing work. Liz and Sreeja are delivering an information-packed session titled SSIS DevOps with Azure Containers on this very topic. I can hardly wait.

SSIS, Docker, and Windows Containers, Part 5 – Deploying to the SSIS Catalog

In the previous five posts I:

  • examined how to install Docker for Windows, pull a test image, search for a SQL Server on Windows image, install and interact with it (Part 0)
  • shared why I want to do all this (Part 1)
  • shared one way to execute SSIS on a container in Part 2
  • shared a failed attempt to add an SSIS Catalog to a SQL Server-on-Windows container in Part 3
  • shared a successful attempt to create an SSIS Catalog (Part 4)

I Have Not Yet Figured Out…

  1. I do not (yet) know how to deploy to a container using the Integration Services Deployment Wizard. This bugs me and I want to figure out some way to do it because deployment testing is critical for SSIS.
  2. I have not figured out how to execute SSIS packages in an SSIS Catalog in a container using the SSMS Object Explorer’s Integration Services Catalogs node.

What I Have Figured Out

  1. I know how to deploy SSIS packages to an SSIS Catalog (the topic of this post); and
  2. I know how to execute them from the SSIS Catalog (the topic of the next post) – both using PowerShell.

Deploying an SSIS Project to the SSIS Catalog

Returning to Matt Masson’s PowerShell script – combined with the docker volume added earlier – I have a means to deploy an SSIS Project to the SSIS Catalog in the container.

I copied an ISPAC file into my D:\docker\install directory:

I can access this ISPAC file from within the container:

From Matt’s post, I execute the following slightly-modified PowerShell script:

# from https://www.mattmasson.com/2012/06/publish-to-ssis-catalog-using-powershell/
$ProjectFilePath = “C:\install\TestSSISProject.ispac”
$ProjectName = “TestSSISProject”
$FolderName = “Test”
[System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.SqlServer.Management.IntegrationServices”) | Out-Null;
$ISNamespace = “Microsoft.SqlServer.Management.IntegrationServices”
$sqlConnectionString = “Data Source=localhost;Initial Catalog=master;Integrated Security=SSPI;”
$sqlConnection = New-Object System.Data.SqlClient.SqlConnection $sqlConnectionString
$integrationServices = New-Object $ISNamespace”.IntegrationServices” $sqlConnection
$SSISCatalog = “SSISDB”
$catalog = $integrationServices.Catalogs[$SSISCatalog]
$folder = New-Object $ISNamespace”.CatalogFolder” ($catalog, $FolderName, “Folder description”)
$folder.Create()
[byte[]] $projectFile = [System.IO.File]::ReadAllBytes($ProjectFilePath)
$folder.DeployProject($ProjectName, $projectFile)

When I execute this script, my results appear as shown here:

(click to enlarge)

View in SSMS

If successful, I can view the deployment in SQL Server Management Studio (SSMS) after refreshing the SSISDB node in SSMS Object Explorer’s Integration Services Catalogs node:

Conclusion

The best way I know to deploy SSIS projects to an SSIS Catalog in a container is using PowerShell.

Next we tackle executing SSIS packages deployed to an SSIS Catalog in a container.

SSIS, Docker, and Windows Containers, Part 3 – Adding the SSIS Catalog, Attempt 1

In my previous three posts I’ve examined how to install Docker for Windows, pull a test image, search for a SQL Server on Windows image, install and interact with it (Part 0). I’ve shared why I want to do all this (Part 1). And I’ve shared one way to execute SSIS on a container in Part 2.

In this post, I show my next (attempted) step: adding an SSIS Catalog to a container. Spoilers:
1. It’s more work than you think;
2. This is merely one way to do attempt it;
3. This is my first attempt to get one step closer to my ultimate goal; and
4. This attempt fails.

One Very Important Note

This attempt at creating an SSIS Catalog on a container does not succeed.
The next post in this series shows how I succeeded in this portion of the task. If you want to skip this post and go directly to SSIS, Docker, and Windows Containers, Part 4 – Adding an SSIS Catalog, Attempt 2, we can still be friends. Promise.

I can hear some of you thinking,…

“So, Why Write This Post, Andy?”

I have been working on getting an SSIS Catalog running in a container for a couple years.
I share this post not to discourage you.
I share it to let you know one way I failed.
I thought I had succeeded when the PowerShell in this post worked. The PowerShell works, by the way – just not in a container configured thus.
This is but one failure.
I failed more than once, I promise.
Why’d I keep going? Love. I love this field and I love helping others and I love figuring out new stuff – especially when I’ve been told, “It cannot be done.” When I hear that, I’m all “Challenge accepted!”
Did I become discouraged? Yep.
Did I want to give up? Yep.
Did I give up? Nope. I made the-problem-I-was-trying-to-solve give up first.

The most important reason I wrote this, though? Someone out there probably knows how to make this work. They will read the post and think, “Andy, you idiot! This is what you need to do to make this work.” Hopefully they will then share this in the comments (you can skip the “idiot” part, though. I already know that…).

“You are welcome.”

Get New

If you followed along in Part 0, you should have a fairly recent image of SQL Server Developer Edition running in a Windows container. But images change from time to time, so you may want to double-check by searching for – and pulling – a fresh image using these commands:

docker search mssql
docker pull microsoft/mssql-server-windows-developer

Before We Begin

Remember from Part 2, an SSIS Catalog-aware version of dtexec is already installed on the container. But:

  • There is no SSIS Catalog installed on the container
  • The Integration Services (IS) components are not installed on the container
  • The SQL Server installation media – which we could use to add IS components – is not available on the container

How then can we create an SSIS Catalog? It turns out there are a few ways to accomplish this task. PowerShell is one of the ways.

You can find lots of good explanations and walk-throughs about how to provision an SSIS Catalog online. I narrow my search considerably by adding the required (in my opinion) search term “mattm” – short for Matt Masson – who was on the SSIS Developer Team that built the SSIS Catalog and who also blogged about the SSIS Catalog. Matt has since moved to another team inside Microsoft.
Durnit.

You may find one of Matt’s examples here. This example works if you’re connecting to a physical server. But this script breaks at line 20 when executed against this Windows container in its current state:

# Line 20
$integrationServices = New-Object $ISNamespace”.IntegrationServices” $sqlConnection

That is not Matt’s fault. Matt’s a genius. Trust me.

The problem is not all SSIS Catalog-required objects are present on this image. I can hear you thinking, …

“Andy, How Do We Get All the SSIS Catalog-Required Objects Installed On This Image?”

I’m glad you asked.
The answer is “We modify our container.”

Disclaimer: I’m about 100% certain there’s another way to do this and about 99% sure there’s a better way. I’m going to show you what I did. Cool?
Cool.

Create a Local Host Directory

On my laptop I created D:\docker\install as shown here.

In the directory I placed the installation media for SQL Server 2017 Developer Edition. Note: If you browse to that link you will need to scroll to see the Download Now button for Developer Edition:

I blogged about one way to install SQL Server 2017 Developer Edition if you’d like to learn a little more. But I warn you, this post doesn’t describe many configuration options and should not be used to configure a production SQL Server 2017 instance.

Reference a Host Directory When Starting a Container

Here I choose to reuse mySqlServerContainer2. To accomplish that, though, I need to tear down the previous version. I do so using the following PowerShell commands:

docker stop mySqlServerContainer2
docker rm mySqlServerContainer2

This is one reason I like containers: I can burn them down and rebuild them easily. Try that with a virtual machine…

You can configure a Docker volume when you run a container by adding the -v switch and referencing a directory on the host. Our modified docker run command appears as show here:

docker run -d -p 1433:1433 -e sa_password=$up3r$3cr3t -e ACCEPT_EULA=Y –name mySqlServerContainer2 -m 2048M -v D:\docker\install:c:\install microsoft/mssql-server-windows-developer

Docker run creates a container for us that has 2G RAM and its C:\install directory is mapped into our D:\docker\install directory on the host. I have to tell you, this functionality beats the dickens out of transferring files into the container using PowerShell (as demonstrated in Part 2)!

Run:

docker start mySqlServerContainer2
docker exec -it mySqlServerContainer2 powershell

This starts the container and drops you into PowerShell inside the container. Run the “ls” command to list files:

Change to the install directory and view its contents by executing the following commands:

cd install
ls

As you can see, if we want to add files that are accessible from within our container, we merely add them to our host directory.

Add Integration Services to the Container

To add the IS (Integration Services) features to our default instance of SQL Server 2017 on the container, we execute the SQL Server 2017 Developer Edition installation application – setup.exe – from the command line. Note: I ran the container with 2G RAM to provide enough RAM for this operation to complete. The command is:

C:\install\SQLServer2017Developer\setup.exe /Q /ACTION=Install /FEATURES=IS /INSTANCENAME=MSSQLServer /IACCEPTSQLSERVERLICENSETERMS /INDICATEPROGRESS

In response to this command, the container spins for a few minutes. When the installation completes, the container appears as shown here:

(click to enlarge)

(Attempt to) Create the SSIS Catalog

Now that IS is installed on the container, let’s enable SQLCLR. CLR is required to run the SSIS Catalog because several Catalog operations are managed via the .Net Framework. To enable CLR, please execute the following PowerShell script – with a tip o’ the hat to Rob Sewell (blog | @sqldbawithbeard) and his post titled Enable CLR with PowerShell:

# adapted from https://sqldbawithabeard.com/2014/05/05/enable-clr-with-powershell/
$srv=New-Object Microsoft.SQLServer.Management.SMO.Server
$Config=$srv.Configuration
$CLR=$srv.Configuration.IsSqlClrEnabled
$CLR
$CLR.ConfigValue=1
$Config.Alter()
$CLR

My results appear as shown here:

Returning to Matt Masson’s (awesome) PowerShell script, we can execute the following PowerShell script to create an SSIS Catalog adapted from Matt’s post titled Publish to SSIS Catalog using PowerShell:

# adapted from https://www.mattmasson.com/2012/06/publish-to-ssis-catalog-using-powershell/
[System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.SqlServer.Management.IntegrationServices”) | Out-Null;
$ISNamespace = “Microsoft.SqlServer.Management.IntegrationServices”
$sqlConnectionString = “Data Source=localhost;Initial Catalog=master;Integrated Security=SSPI;”
$sqlConnection = New-Object System.Data.SqlClient.SqlConnection $sqlConnectionString
$integrationServices = New-Object $ISNamespace”.IntegrationServices” $sqlConnection
if ($integrationServices.Catalogs.Count -gt 0) { $integrationServices.Catalogs[“SSISDB”].Drop() }
$catalog = New-Object $ISNamespace”.Catalog” ($integrationServices, “SSISDB”, “SUPER#secret1”)
$catalog.Create()

Once executed, my results are:

(click to enlarge)

If you skipped the Enable CLR step (which I simulate by resetting IsSqlClrEnabled to 0) you will get an error when you attempt to execute the $catalog.Create() command:

Exception calling “Create” with “0” argument(s): “Integration Services uses CLR stored procedures. Enable “clr enabled” configuration option.”
At line:1 char:1
$catalog.Create()
+ ~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [], MethodInvocationException
+ FullyQualifiedErrorId : IntegrationServicesException

Conclusion

It appears we have an SSIS Catalog on our container but looks can be deceiving. As mentioned near the top of this post, we have not, in fact, created an SSIS Catalog in our container. As ever, we need to test and see.

Next, we test and find we do not have an SSIS Catalog running in our container, so we try again – and this time succeed:

SSIS, Docker, and Windows Containers, Part 4 – Adding an SSIS Catalog, Attempt 2