Converting Existing SSIS Packages to Biml

David Stein shows off a conversion tool built into BimlExpress:

BimlExpress is a free Visual Studio add-in created by the good folks at Varigence. Its a full featured Biml editor which allows you to dynamically create SSIS packages. It was first released back in 2017, and the latest version is 2019 (of course). The current version supports Visual Studio 2010 through 2019 as well as SQL Server 2005 through 2019.

Prior to it’s release, Biml was written with Bids Helper, now known as BI Developer Extensions. While BI Developer Extensions has many nice features, you should no longer use it to work with Biml as it is no longer being updated/supported.

I’m pleasantly surprised by this. It used to be limited to BimlStudio (nee Mist) and BimlOnline.

Extracting ISPAC Files From the SSIS Catalog

Aaron Nelson shows how we can use Powershell to export ISPAC files from the SSIS catalog:

You’ll notice with the code below, that we’re able to extract that Project-object into a variable named $Proj, but when we pipe that to the Get-Member cmdlet we do not see a method called Export.

Read on to learn what the name of this mysterious method actually is and how you can make use of it.

SSIS 2019 Preview Released

Koen Verbeeck notes something very nice:

It is not a joke: SSIS is available for Visual Studio 2019 as a preview. Whoa, hold on. SQL Server 2019 hasn’t been released yet? But there’s already an SSIS 2019? Didn’t we have to wait months after the release of SQL Server 2017 before we had an SSIS version for Visual Studio 2017?

Yes, we did, you can read all about there here.

But times have changed apparently. The SSIS team caught up with the rest of the BI tools: SSIS projects are now available from the Visual Studio market place.

Read on to see what this means for SQL Server Data Tools.

Deploying SSIS Packages with Powershell

Aaron Nelson shows us how we can deploy an Integration Services ISPAC into the SSIS catalog with Powershell:

In my last post, I showed how you can use the SSIS PowerShell Provider to execute an SSIS package with PowerShell.  Of course, in order to execute that SSIS package, it has to get deployed first.  In Part 5 of Andy Leonard’s “SSIS, Docker, and Windows Containers” series he used some PowerShell code from Matt Masson’s blog post to deploy an .ISPAC file to the SSIS catalog.

Click through for the code.

Figuring Out SSIS Memory Requirements

Tim Mitchell tries to give us a better answer for SSIS memory requirements than “all of it and then some”:

When planning for memory needs, it is critical to understand how SQL Server Integration Services uses memory. SSIS will allocate memory from the unallocated system memory for each package executed, and surrenders that memory shortly after the package completes its execution. The memory allocated for SSIS package executions runs in the SSIS execution runtime process (ISServerExec.exe, if you are executing the package from the SSIS catalog).

Here’s where the package design has a significant impact on memory use. If a package uses an SSIS data flow, all of the data passing through that data flow is written to memory used by SSIS. For example, consider a package that loads 10 million rows from a flat file to a table. In this case, all 10 million rows will pass through the SSIS memory space during package execution.

Read on as Tim goes into good detail on the topic.

Temporary Staging with SSIS

Andy Leonard shares one technique for reusing a data set in SSIS:

A work table is a table defined in a nearby data location; either a schema in the source or target database or in a database on the same instance. I take a constraint-driven approach to work table location selection. Closer – a schema in the same database – is often better for performance.

I write this knowing some folks will frown at the suggestion of polluting a data source or target database with additional schemas and tables. Best practices exist for a reason. It’s helpful to maintain a list of best practices and to include in this list the reasons each practice exists. This could be a case where violating one or more best practices is justified.

Andy throws out a few ideas as alternatives but states his preference for using work tables to solve this problem.

Deploying and Executing Containerized Packages

Andy Leonard continues a series on Integration Services in Docker. Part 5 shows how you can deploy a package to a containerized SSIS instance:

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.

Part 6 shows how we can run those packages:

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:

Read both to learn more about Andy’s travails in getting this working.

Adding SSIS Catalog to a Docker Container

Andy Leonard takes two shots at adding the SSIS Catalog to a Docker container. First up is the version which doesn’t work:

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. 
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.

Andy perseveres and succeeds in part 4 of the series:

I can hear some of you thinking, “How do we accomplish this, Andy?”
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.

Read on to see how Andy did it.

Executing an SSIS Package in a Container

Andy Leonard continues a series on SSIS and Docker by executing a package in a container:

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.

Read on to see how to do this.

SSIS on Windows Containers

Andy Leonard is a man who doesn’t like to take “no” for an answer:

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:

Click through for more thoughts and setup for a new series.

Categories

May 2019
MTWTFSS
« Apr  
 12345
6789101112
13141516171819
20212223242526
2728293031