Unit Tests for SSIS

(Joe) #1

Greetings!

I have been briefly investigating solutions for unit testing SSIS products. I understand the basic approach has been little more than find some data, present it to the package, and check the output.

I wondered if anyone had experimented with other manual or automated solutions.

Thanks!
Joe

(Jesper) #2

Joe, what are SSIS products?

(Joe) #3

Hello @jesper!

Thanks for asking!

SSIS is a part of Microsoft SQL Server. It provides abilities to migrate and transform data in support Extract-Transform-Load (ETL) operations. While I have not created or tested an SSIS package that perform these operations, I understand the interface is drag-n-drop.

The testing approach I mentioned is very black box so I was asking if anyone had tried different approaches. The “code” does not appear, to me, to lend itself well to deep inspection but I’ve little experience with it.

Thanks!
Joe

(Jesper) #4

We are so used to (talking about) testing where the source code and developers are available to us, that we stall when this is not a given :smiley: When it’s a commercial standard (black-box) package, but we still want to test the solution, but the UI is all there is…

Perhaps some visual validation?

(Joe) #5

Thanks!

Were there a UI to review, these might be helpful. Generally, the evidence of an SSIS package execution is an updated database or a file either of which contain the transformed information.

As an example of a transform, if the input contains a field named Color and the value is Red. After the SSIS package executes, a database table of Colors has a column named Hue and our value, Red, appears there. The SSIS package mapped the field Color to the column Hue.

The complexity can rise from there when a field value must be transformed (e.g., Ohio to OH), or multiple fields are combined (e.g., FirstName and LastName are combined to Name). The SSIS package performs these transformations.

I can compare inputs and outputs but I’d like a method to isolate the complex transforms so the input/output comparison is simpler.

Joe

1 Like
(Jesper) #6

Excellent elaboration - appreciated :wink: … So more along the lines of no UI only back-end. interesting!
Perhaps this will get you somewhere:

1 Like
(Joe) #7

That is pretty cool and deserves more ponderment. What I like about it is that not only does it capture an idea for “no UI only back-end”, but it demonstrates the flexibility we might all keep in mind around the testing pyramid concept. That is, match the “pyramid” shape to the products or projects under test.

Thanks! More likes deserved on that post @jesper!

Joe

1 Like