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:
- Run a container with the microsoft/mssql-server-windows-developer image (assign lots of RAM).
- Add the SQL Server installation files to the container.
- Execute SQL Server setup from a command line configured to add Integration Services (this is why you need the RAM).
- Add SSIS.
- 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…
:{>
Any updates, friend? We’ve got Windows Authentication working but run into this error when trying to deploy packages from VS 2017 onto Docker:
A .NET Framework error occurred during execution of user-defined routine or aggregate “deploy_project_internal”:
System.ComponentModel.Win32Exception: Access is denied
System.ComponentModel.Win32Exception:
at Microsoft.SqlServer.IntegrationServices.Server.ISServerProcess.StartProcess(Boolean bSuspendThread)
at Microsoft.SqlServer.IntegrationServices.Server.ServerApi.DeployProjectInternal(SqlInt64 deployId, SqlInt64 versionId, SqlInt64 projectId, SqlString projectName)
. (Microsoft SQL Server, Error: 6522)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=14.00.1000&EvtSrc=MSSQLServer&EvtID=6522&LinkId=20476
Hi Liz,
Yep. I worked around that one and figured it out! I’ll be writing more soon-ish.
:{>
Hey Andy
Have you come any further with your docker SSIS surveys?
Hi Geir,
I have, I just need to make time to blog about it.
:{>
Hi Andy,
looking forward to your blog and the way you cracked this. Also getting that SQL Server Agent enabled ;-). Would be lovely if you could share this soon… I see there is more guys trying to solve the same thing. Thanks a lot for your efforts!