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:

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:

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 .