BIML: C# looping through array example

Yesterday I was working with a colleague on an interesting problem in BIML.  We are using BIML to build one SSIS package per table in a database, each package has an execute SQL task.  Depending on what table we are looking at we want to be able to have the execute SQL task either include a WHERE clause or not.  Easy.  I bet there are a lot of ways to solve this problem.  Here’s how we did it.

I like to start with a simple test harness.  Something that I can right click on and select ‘Check BIML for errors’ and know it will work.  Basically, the idea is to isolate the code I’m playing with from all the other code I already know works.

I start with the following (usually)

Then I begin to add things to my simple code harness to make it more interesting.  First I add two lines AFTER the </Biml> tag (lines 10 and 11 below) to make the c# MessageBox control available to my BIML.

Now I’m ready to add some code to play with.  First I add two variable definitions.  The first one is a simple string to hold a table name I will test with (line 6 below).  The second variable is a little more interesting (line 7 below).  It’s going to be an array of strings, and it will hold a list of tables I am testing for.

The basic idea is that my ‘real’ BIML code will be looping through tables, as it comes to a table it will compare that table name to the list of tables contained in the array and if it finds a match it will take an action, if it doesn’t find a match it will take a different action.

Here is the code with the variables added …

And here is the code with the first test.  The part you want to focus on here is the ForEach loop.  It’s defined on lines 9-11 in the following code block.

If you copy the code above into a fresh BIML file and test it, a MessageBox will pop up telling you the ‘table1 was found in array’.  Very cool!  Now we just need to add in code to handle any tables NOT found in the array.

Notice line 16 (above) ?  That’s where I’m testing if the value of TableName exists in the array.  If it does NOT exist, the IndexOf will return -1, otherwise it would return the index position where the value in TableName was found.You can copy the final version of this code into your fresh BIML file and test it.  Change the value of TableName to something that IS and then something that IS NOT in the array and you will get different results.

Of course there are other ways to do this, but for this blog post I wanted to show how to test for a value in an array as well as test an array to see if a value is NOT in it.

I love writing BIML script.  I haven’t always loved writing C# in BIML, learning to work in a test harness and isolating what I’m testing from other code has really made my coding go a lot more smoothly.

Leave a Reply

Your email address will not be published. Required fields are marked *