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

SSIS, Docker, and Windows Containers, Part 2 – Executing SSIS, v0.1

In my last couple 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).

In this post, I show my next step: executing an SSIS package in a container. Spoilers:
1. It’s more work than you think;
2. This is merely one way to do it; and
3. This is not my ultimate goal.

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

Just Add Memory

Start a new container in docker by opening a command prompt and entering the follow docker command:

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

Note we are standing up a new container named mySqlServerContainer2. We are also adding a switch to boost the memory in this container to 2G – the -m switch does this for us.

“Start Your Engines”

Start the container and then connect to PowerShell inside the container using the following commands:

docker start mySqlServerContainer2
docker exec -it mySqlServerContainer2 powershell

One Quick Test

Try something. Type “dtexec” without the double-quotes and note the result. My result looks like this:

This tells me Integration Services is installed on this container. Not only does it tell me IS is here, but it tells me a Catalog-aware version of the executable is present. How do I know? Look at the error message:

At least one of the DTS, SQL, ISServer or File options must be specified.

The “ISServer” option appears only in SSIS Catalog-aware versions of dtexec.
Groovy.
Almost.

There is no SSIS Catalog created on this version of the container.
Bummer. But, we can execute an SSIS package from the file system.

Let’s copy an SSIS package to the container and execute it!

First, let’s create a directory on our container’s C: drive for SSIS packages by executing the following PowerShell command:

md ssis

Copying an SSIS Package to the Container

I remind you: This is not the only way to access an SSIS package on a container. I share a better way in the next post in this series, in fact. But this is one way.

If you’re playing along at home, exit PowerShell in the container by typing “exit”.

Start Command Prompt as an Administrator

To complete the next steps, start Command Prompt on your host machine as an Administrator. I accomplish this by searching for command prompt in Windows 10, right-clicking it, and clicking “Run As Administrator”.

You are prompted to continue, making sure you really want to execute the command prompt as an Administrator (the prompting is a good thing). Once the host command prompt starts as an administrator, execute the following docker command:

docker inspect –format ‘{{.Id}}’ mySqlServerContainer2

My results appear as shown here:

Select the return value by highlighting it in the command prompt window – leaving out the apostrophes – and copy it to the clipboard by right-clicking the value or using the keyboard Ctrl+C. We use it in the next command, which is executed from the host command prompt:

Start PowerShell from the administrator command prompt by executing the command: “powershell”. Once PowerShell starts (as an administrator, since it was started from an administrator command prompt), execute the following commands:

# create an administrator container session
$ContSession = New-PSSession -ContainerId b9d0e7153587beacc010de7cb87e364bf059656a391f987a4ddc7dd8e892d7cb -RunAsAdministrator

# copy an SSIS package from the host into the session
Copy-Item -Path D:\docker\ssis\ReportAndSucceed.dtsx -Destination C:\ssis -ToSession $ContSession

The first command creates a PowerShell session with the container that also has administrator privileges. The second command copies an SSIS file – from my D:\docker\ssis\ host folder…

… to the C:\ssis directory on the container.

We can view the contents of C:\ssis by closing the Administrator command prompt (which will close the administrator PowerShell session… I don’t like leaving administrator stuff open any more than I need to…), starting a non-admin command prompt, and reconnecting to mySqlServerContainer2’s PowerShell:

docker exec -it mySqlServerContainer2 powershell

… and then listing the contents of the C:\ssis directory:

Mission accomplished

Execute the SSIS Package

Execute the SSIS package by calling dtexec, supplying the /FILE switch followed by the name of the SSIS package. Since I changed directories to the C:\ssis directory in the container, a local path (no path – simply the filename) will do:

dtexec /FILE ReportAndSucceed.dtsx

As you may surmise from the name of the package, this simple SSIS package raises an Information event message – the “report” part – and then succeeds. My execution appears as shown here:

I can hear you thinking, “Where’s the Information message, Andy?”
Good catch.
It’s not displayed.

We can get Information messages to display by adding a /REPORTING switch and passing either the “I” – for Information – or “V” – for Verbose:

(click to enlarge)

This SSIS package was written in an earlier version of SSIS. Each version of SSIS uses a specific version of Visual Studio Tools for Applications (VSTA), which requires .Net script object code – found in the Script Task and Script Component – be migrated to the current version of SSIS prior to execution. We get one Info message for the .Net code migration operation, followed by a second Info (Information) event message. The message displayed in the Description of the second Info event reports: “I am ReportAndSucceed”.

Conclusion

While executing SSIS packages in a container from the command line is helpful, it does not achieve my ultimate goal: Executing SSIS packages in the SSIS Catalog in a container.

In the next two posts I share an unsuccessful attempt to create an SSIS Catalog in a container followed by a successful attempt .

SSIS, Docker, and Windows Containers, Part 1 – Why?

In my first post in this series, SSIS, Docker, and Windows Containers, Part 0 – Getting Started, I covered:

  • Installing docker
  • Choosing a Container OS
  • Pulling a test image
  • Pulling a Windows Container with SQL Server installed
  • interacting with with your instantiated container

In this post I reveal my ultimate goal and then travel down two rabbit holes to explain:
1. How I got here; and
2. One way to get answers to questions no one wants to hear.

My Ultimate Goal

My ultimate goal is to use one or more Windows containers running SQL Server to test SSIS. I can hear some of you thinking,..

“Why, Andy?”

My response? “Cuz.”
And, “I do what I want!”

Seriously, since I hopped the fence from developer to data I’ve dreamed of the day when I could practice lifecycle management with data-stuff like I used to practice lifecycle management with software development.

I recognize the obstacles. The greatest obstacle (in my humble opinion) is software is mostly stateless these days (these days started with Object-Oriented Programming and include its descendants). Stateless development solves lots of engineering problems in lifecycle management, and by “solves a lot of engineering problems” I mean some engineering problems simply don’t exist so lifecycle management for stateless stuff can simply ignore “a lot of engineering problems.”

A database, on the other hand, is all about that state. When it comes to managing lifecycle for a stateful platform – like a database – ACID gets dumped on many lifecycle management tools and solutions (see what I did there?).

Is it possible to manage a data-related lifecycle using stateless tools? Yes. But here there be obstacles. Let’s look at on use case:

Use Case 0

Your team releases an update that adds one column to one table. The column receives data. Someone realizes there’s a bug and determines a rollback is the solution. The web and middle-tier teams successfully rollback the release using functionality built into the release-management / DevOps enterprise tool.

What happens to the column in the table?

  • Is it dropped?
  • Is the data deleted?
  • Since this isn’t a post about data lifecycle management, let’s not even consider what happens if the data is considered personal-identifying-information or some tuple otherwise subject to privacy regulations.

You see the problem? The answer is always going to be, “It depends.”

This is the problem I am trying to solve, specifically for data integration.

I want to be able to easily deploy and rollback data integration deployments. But to accomplish this, I need more metadata (data about the data) and a bunch more automation. Why? My stuff has state – by definition.

“Not Supported”

If you have not yet figured this out, Microsoft is a software development shop. They’re people trying to use software to solve business problems for customers – just like you and me. Just like you and me, they have schedules and deadlines and competition and a need to generate a profit. in other words, Microsoft is a business.

When some redneck from Farmville Virginia starts asking all sorts of out-of-scope questions, he sometimes hears, “That’s not supported, Andy,” in response. Ask me how I know.

So it helps to be stubborn persistent. Persistence sometimes takes the form of thinking of another approach. When I began probing for ways to test SSIS in containers, my ultimate goal was to have the tests simulate Production as closely as possible. In 2019, Production SSIS means executing SSIS in the SSIS Catalog.

Is it possible to execute Production SSIS from the file system or the MSDB database in 2019? It is. I’ve not found a good use case for executing SSIS outside the Catalog, though – not in 2019. I am not trying to hurt anyone’s feelings, I promise. I’m just saying that the SSIS Catalog has been out for north of six years at the time of this writing. Plus: The only way to execute Azure-SSIS packages is via an SSIS Catalog in the cloud (again, at the time of this writing).

At Enterprise Data & Analytics we help clients migrate from file- / MSDB-based SSIS execution to the SSIS Catalog (both on-premises and Azure-SSIS). Some clients realize the benefits of SSIS Catalog-based execution and call us for help with training and consulting. Others ask for help performance-tuning SSIS, and we almost always recommend using the SSIS Catalog because SSIS packages almost always execute faster when executed from the Catalog. When do we not recommend using the SSIS Catalog? Some enterprises have designed their data environments in such a way that using the SSIS Catalog would require a major shift. The technical portion of the shift would be relatively straightforward, but – often – the cultural shift is more disruptive. Not all enterprises are willing to make such a cultural shift; to endure the disruption.

Getting Around “Not Supported”

When I started asking questions like, “Is it possible to run the SSIS Catalog in a Windows container?” the response was, “That’s not supported.”

Fine. What is supported? Running the SSIS Catalog on SQL Server installed on Windows Server Core. I was trying to solve many of the same problems in a container that would need to be solved running an SSIS Catalog on Core. It turned out many of these problems had indeed already been solved.

More Information

If you can make it to SQL Saturday #813 – Boston BI Edition on 30 Mar 2019, Liz Baron and Sreeja Pullagura are delivering an information-packed session titled SSIS DevOps with Azure Containers on this very topic. I’ve had the privilege and honor to work with Liz, Sreeja, and the awesome data engineering team at Teach For America – a worthy non-profit.

I predict Sreeja’s and Liz’s presentation is going to be a session that moves the needle for Data Integration Lifecycle Management (DILM).

My next several blog posts on this topic – sharing some of my efforts and what I’ve learned about executing SSIS using containers – will begin appearing 31 Mar 2019.

Shameless plug: I am delivering Intelligent Data Integration with SSIS – a full-day pre-conference training session – Friday, 29 Mar 2019.
Register here!
I hope to see you there!

In my next post I share one way to execute SSIS in a container.

:{>

SSIS, Docker, and Windows Containers, Part 0 – Getting Started

To begin tinkering with SSIS in containers, you first need to install Docker. There are some prerequisites. I will not exhaust the prerequisites here. I strongly suggest you familiarize yourself with the requirements for Docker prior to attempting to install the software.

Since I use a PC (a Lenovo P51S) running Windows 10 Pro, I chose to use Docker for Windows.

I Needed a HyperVisor

I chose to run Docker for Windows with Hyper-V:

One reason I upgraded to Windows 10 was to work with containers. I read posts and articles that stated I could use Docker for Windows with VirtualBox, and I had been using VirtualBox for a long time. When I started using VirtualBox, it was the only HyperVisor that was:

  1. Free; and
  2. Supported 64-bit guests.

I decided to switch to Hyper-V, though, and rebuilt my demo and development virtual machines in Hyper-V.

IWOMM; YMMV (It works on my machine; your mileage may vary…)  😉

Choose a Container OS Platform

Once Docker is installed you need to decide whether to work with Linux or Windows containers, but you can switch anytime:

One nice thing about switching is it’s fairly painless, as one may glean from the message that displays when I click “Switch to Linux containers…”:

Docker for Windows with Windows Containers

The cool kids are using Linux containers, especially the cool SQL Server kids. I’ve tinkered a little with SQL Server on Linux. I’m interested in SSIS, though, so I’ve been focusing on running Windows containers in Docker for Windows.

Getting Started

Containers are like lightweight virtual machines. They typically consume less disk space. Containers spin up relatively quickly. A developer can configure a container, persist it in some state, and then turn it off. It’s faster and lighter than working with a VM. There are other benefits that we will get to later in this series.

After installing Docker, your next step is pulling an image.

Pulling an Image

I can hear you thinking, “Umm, Andy… what’s an image?” I’m glad you asked. From the Docker Glossary:

An Image is an ordered collection of root filesystem changes and the corresponding execution parameters for use within a container runtime. An image typically contains a union of layered filesystems stacked on top of each other. An image does not have state and it never changes.

I can now hear you thinking, “Umm… what?” I think of an image as a pre-configured container. What’s configured in the pre-configuration? Well, the operating system and other software I may want to use.

For example, the hello-world image is relatively small and will test your installation of docker. In the animated gif below, I show how I:

  • Search for images named “hello-world”
  • Locate the name of an image labeled “Official” and named “hello-world”
  • Pull the “hello-world” image (sped up – my internet is not that fast out here in Farmville…)
  • Re-pull the “hello-world” image to show how things look when the image is up to date.
  • Run the image

(click to open, enlarged, in a new tab)

As stated earlier, the hello-world image is a test.

Searching, Pulling, and Starting a Windows Container with SQL Server Installed

To search for, pull, and start a Windows container with SQL Server installed and configured, execute the following Docker commands:

    1. docker search mssql
    1. docker pull microsoft/mssql-server-windows-developer
  1. docker run -d -p 1433:1433 -e sa_password=$up3r$3cr3t -e ACCEPT_EULA=Y –name mySqlServerContainer microsoft/mssql-server-windows-developer

If all goes well, you will see the container id – a hexadecimal number such as:

2d06a9a756b7b75bb0e388173bdd6925ba712e8843848610b5f5276a69f4bf19

Command Line Switches

#3 above has a lot of switches on that command line. Here’s what they mean:

  • -d == detach, which tells docker to run the container in the background and print the container id
  • -p == publish list, which publishes a container’s port(s) to the host
    • I use -p to map the container’s port 1433 (SQL Server uses port 1433) to my laptop’s port 1433
  • -e == env list, which sets a container’s environment variable(s) to a value (or values).
    • I use -e to set the sa_password and ACCEPT_EULA environment variables
  • –name == assigns a name to the container
  • The final argument is the name of the image

By the way, you can get help on any command in docker by typing docker <command> — help. To complete the list above, I typed:

docker run –help

Interacting with Your Newly-Started Container

If you saw that long hex number, your SQL Server Windows container started. It’s a brand new container – it still has that new container smell.

There are a few ways to interact with this container. Let’s look at one, PowerShell.

Connect to PowerShell in mySqlServerContainer using the following command:

docker exec -it mySqlServerContainer powershell

Once connected, you can use PowerShell to execute all sorts of commands, such as:

dir
(or ls):

Cool? I think so too.

Conclusion

I’m going to stop here because this post is long enough and this is enough to get you started using SQL Server in a Windows container. That was my goal today.

In my next post – SSIS, Docker, and Windows Containers, Part 1 – Why? – I explain why this is important (to me, at least).

Enjoy!

It is Possible to Execute SSIS in a Container

As the title of this post suggests, it is possible to execute SSIS in a container. How? I’ll share more details later – promise. One short version is:

  1. Run a container with the microsoft/mssql-server-windows-developer image (assign lots of RAM).
  2. Add the SQL Server installation files to the container.
  3. Execute SQL Server setup from a command line configured to add Integration Services (this is why you need the RAM).
  4. Add SSIS.
  5. Execute from the command line (dtexec).

The results?

SSIS Catalog in a Container?

I can hear you thinking, “Yeah? But what about using the SSIS Catalog in a container, Andy? Huh? HUH?”

Calm down, will ya? I’m working on it.

I’ve benefited from some great advice and help from Ned Otter (nedotter.com | @NedOtter). I caught Ned’s presentation on containers at SQL Saturday Boston 22 Sep 2018. I’d seen other presentations on containers and learned a lot. Ned’s session put me over the top.

I can hear you thinking, ” Yeah-yeah. Have you made any progress, Andy?”

Well, some:

Above is a screenshot of an SSIS 2017 Catalog hanging out in a container. In this state I cannot do much with it because I am connected using a SQL Server login (even though said login is sa).

I was able to “create” a Catalog folder in this Catalog:

I write “create” in quotes because I “created” the folder in an unconventional manner (executing pieces of SSISDB stored procedures).

I may be able to deploy an SSIS project – again, via some creative combinations of pieces of SSISDB stored procedures. If so, I doubt I will be able to execute an SSIS package in this Catalog.

The only way I can think to accomplish this is to bypass some Catalog security checks which means there is a distinct possibility that important steps will be missed or skipped.

My gut tells me this would be bad.

The best path forward is to find some way to connect to SQL Server in the container using Windows Authentication. I’ve been communicating with my brother and friend, Brian Kelley (truthsolutions.wordpress.com | @kbriankelley) about this but have yet to solve it.

I plan to publish the image once I figure out how.

Conclusion

I am excited about executing SSIS in a container from the command line, though. Very excited.

More to come…

:{>