SSIS Junkie : Foreach Loop

SSIS: Initialise variables the easy way

jamie.thomson — Tue, 09 Sep 2008 18:58:48 GMT

Its possible that at some point when building SSIS packages that you will want to initialise a variable with some value at execution time and the most obvious way of doing this is to use a script task. That code would look something like the following:

    Public Sub Main()
        Dim vars As Variables
        Dts.VariableDispenser.LockForWrite("User::Variable")
        Dts.VariableDispenser.GetVariables(vars)
        vars("User::Variable").Value = "Some silly string"
        vars.Unlock()

        Dts.TaskResult = ScriptResults.Success
    End Sub

Not everyone likes writing code mind you so here's a nifty little trick you could try instead. The ForEach Loop uses enumerators to define the collection that needs looping over; one of those enumerators is the ForEach Item enumerator which simply allows you to type in values at design-time that will be looped over at execution time. Very simple stuff indeed. Here you can see that I've set up a ForEach Loop accordingly:

I've defined a 1-column, 1-row collection containing a single value "Some Silly String". On the Variable Mappings tab I just have to assign that value into a variable like so:

Errr and that's it, the variable User::Variable is initialised with the value "Some Silly String". Admittedly I can't really think of a practical use for this (can you think of one?) but at least it introduces the ForEach Item Enumerator, a little known feature of SSIS. Truthfully I've only written this to kill some time on a long car journey but hopefully it proves useful to someone out there, let me know if so. Until next time...

-Jamie

SSIS: Deleting a group of files

jamie.thomson — Thu, 17 Apr 2008 21:38:35 GMT

A question I quite often see on the SSIS forum is:

Is there a way to delete a group of files within a directory? ie: I want to delete all files in directorty c:\logs\*.txt
(http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=3196521&SiteID=1)

Well its actually quite easy, you just need ForEach Loop container and the FileSystem Task. Here's how you set up the ForEach Loop container:

In the 'Collection' tab you need to specify the enumerator, the folder from which to delete, and a fie mask to specify the files that you want to delete.

In the Variable Mappings tab you need to store the enumerated filename in a pre-created variable. The index will be 0 in this case because we are only interested in the file name which gets returned in the first column of the zero-based enumeration.

After that we have the simple task of wiring up the File System task that we do like so:

Note that we have used the variable "[User::FileName] that we populated using the ForEach Lloop container.

And that is pretty much it. Very simple indeed when you know how. I have made a demo package available here:

-Jamie

SSIS: Put a package to sleep

jamie.thomson — Mon, 23 Oct 2006 21:28:00 GMT

A question was recently asked on the SSIS forum about how you can put a package "to sleep". In other words, pause execution for a defined length of time.

It was suggested to use a script task to do this. That would definately work but there is actually a much easier way - use an empty For Loop. Here's a screenshot showing how to set it up.

As you can see, all you need to do is set the EvalExpression property. It really is that easy. The expression here:

DATEADD( "ss", 10, @[System::ContainerStartTime] ) > GETDATE()

will pause the package for ten seconds. It works by looping until the time that it started looping plus ten seconds is less than the current time. Very simple indeed.

One thing to be aware of in regards to this. In the current build of SSIS (SP1) there is a small bug that you should be aware of when using the For Loop or ForEach Loop. Each iteration of the For Loop or ForEach loop hangs onto a bit of memory - in other words there is a small memory leak. Hence if you loop enough times there is a miniscule possibility that you could get an out of memory exception. This bug has been fixed in SP2.

This bug information comes from Travis Maddox. who was running a process in an infinite loop. On each loop iteration he was processing a 100k file (approximate size). The steps outlined above for putting a package to sleep should not result in an infinite loop unless you code it up wrongly. The point being that it would take a huge processing requirement for you to encounter this bug so in all but the most extreme circumstances it really isn't anything to worry about when you are building a "sleep" For Loop that doesn't actually process any data..

My thanks go to Travis for this information.

-Jamie

UPDATE 2006-11-02: I neglected to check this out properly and I should make you aware that this technique may send your CPU utilization sailing a little close to the edge. Make sure you check out CPU util before you fully employ this technique.

Thanks to Darren Gosbell (and a couple of others) for pulling me up on this. And I apologise for not checking this out fully first. A lesson learnt methinks.

SSIS: Processing data from multiple files all at once

jamie.thomson — Sat, 14 Oct 2006 16:54:00 GMT

Quite often you may want to process data from multiple files all at the same time. There are a number of options available to you if you want to do this.

1. Have a seperate source adapter for each file and amalgamate them together with the Union All component.
Advantages of this approach:

This works well if the files are of differing formats
You only have one executable in order to do everything

Disadvantages:

This does not work if the number of files differs each time

2. Loop over the files in a Foreach loop and append to a raw file. Then, process all data together in a seperate data flow.
Advantages:

You can re-use data-flows for files of the same format
Works regardless of the number of files

Disadvantages

You have more executables in your package
You will need to build control-flow logic to handle differing formats or else have some clever logic in your data-flow inside the Foreach loop
You need to stage the data (in the raw file)

3. Use a MULTIFLATFILE connection manager
Advantages

Only one executable in your package
One set of logic if all files are of the same format
Works regardless of the number of files

Disadvantages

You need some clever logic in your data-flow to handle differing file formats

Its clear that you choice depends on your circumstances so given that I don't know your circumstances I won't make a recommendation.

-Jamie

SSIS: Expressions on ForEach enumerators

jamie.thomson — Fri, 13 Oct 2006 21:35:00 GMT

I've noticed a few people on the SSIS forum lately asking about expressions on the Foreach Loop. They are aware that its possible to set expressions on properties of the Foreach container but are flummoxed when it comes to setting expressions on the properties of the enumerators themselves. That's understandable because the properties of the chosen enumerator are not available from the "Property Expressions Editor" on the "Expressions" tab of the "Foreach Loop Editor" (which is where they usually appear). You can see that here:

All is not lost however. There's a seperate route into expressions on enumerator properties via the "Collection" tab of the "Foreach Loop Editor". I've highlighted that on the next screenshot that shows properties being set on the Foreach File Enumerator.

Note the "Expressions" section on the "Collection" tab. That's the bit you're interested in.

Dead easy when you know where to find it! I must admit it took me a while until I discovered it.

-Jamie

SSIS: Append to a raw file

jamie.thomson — Thu, 22 Jun 2006 22:42:00 GMT

I have just arrived home from an excellent BI-flavoured meeting of Tony Rogerson's UK SQL Server User Group. Aside from some great content the thing I love about these events is the change to meet up with people from around the BI community - people that I've met both in person and online in the past few years.
So Rastko Petrovic, Mat Stephen, Simon Sabin, Tony, Sanjay Nayyar...great to see you guys again. To Chris Webb, Sutha Thiru and Mark Hill...glad to finally put some faces to your names - I just wish I'd had more of a chance to have a natter with you guys!

Chris opened the evening by giving a great demo of some of the new features in MDX. That was really valuable to me due to the work I am currently undertaking in my day-to-day role. MDX Scripts are going to relieve some of my headaches I'm sure!

Mark followed by talking through his experiences of implementing a SSAS solution for a 2 billion row fact table. It was really valuable to hear some first-hand experience of the pain that he and Rastko have gone through on their current project and I'll definately be applying some of those learnings to what I'm doing now.
I was slightly disappointed to hear some criticism of SSIS's performance against none-SQL Server data sources but I can't argue with hard facts and Mark raised some serious concerns that I hope the SSIS team are going to take on board.

Simon followed up with a whistlestop demo of how to load and analyse unstructured ASP.Net exception files using SSIS. I love Simon's style of presenting. He comes across as being utterly unprepared (I'm sure he won't mind me saying that) and yet somehow manages to get his point across through sheer tenacity, wit and crazy exuberance.

Part of Simon's demo was showing how to loop over a set of files using a ForEach Loop and append the results to a raw file so that the full set of data from all source files can be processed as a whole. In order to do this Simon showed how it is necassary to, prior to the ForEach Loop execution, create an empty raw file with the same metadata as the raw file required to load the data into. His method of doing this was very simple - use a script component. All well and good.

I made a slight fool of myself by piping up in the middle of Simon's presentation that creating the empty raw file manually like that wasn't necassary - it is possible to make SSIS create the raw file on the first iteration of the loop- but when he challenged me to prove it I couldn't remember how. I was sure it was possible though.

Well, naturally, afterwards I went away to try and prove myself right and thankfully I'm not really as foolish as I might have first appeared. It IS possible to loop over files, creating the raw file on the first iteration and then appending to it thereafter. The trick is to set ValidateExternalMetadata=False on the raw file destination adapter as shown here:

If you want to see this for yourself then download a demo package from here. There are no connection managers in the package so running it will not be a problem. Here's a screenshot of the control-flow:

The only thing you need to make sure of is that it has read-write access to C:\temp because that is where it creates the raw file. Here's a screenshot of the data-flow:

So, the upshot of this is:
-You can append to a raw file within a ForEach loop
-It is possible to configure the raw file destination so that on the first iteration of the ForEach loop the raw file will get created.

Its an important point because being able to process data from multiple files all at once is an common requirement. Simon himself gave a clear example of this by taking a LOOKUP component out of the ForEach loop so that it only had to execute (and therefore cache all its data) once. Leaving it inside would be a major faux pas in terms of performance.

So all in all a good night and thanks to Chris, Mark, Simon and Tony for that. And a lesson learnt for me - don't jump headlong into someone else's demo unless you can back up what you are saying!

-Jamie

SSIS Nugget: Setting expressions

jamie.thomson — Sat, 11 Mar 2006 09:24:00 GMT

Whilst reading posts on the SSIS forum of late it has become evident that people are unaware of the power that expressions can provide within an SSIS package. This is understandable - alot of people are starting to use SSIS now but expressions aren't the most obvious feature.

So what are they? Well the most generic explanation is that expressions allow your packages to be dynamic by setting properties of objects at runtime. What are they used for? Well there are a number of scenarios, the most common being:

Dynamically setting the connection string of external connections
Conditional precedence constraints
Setting the location of raw files
An alternative to parameterised SQL statements in the Execute SQL Task or a source adapter

The reason that expressions aren't the most visually obvious feature is they're usually hidden away in the properties pane. Lets demonstrate that in one of the scenarios already mentioned - dynamically setting the connection string of external connections.

Imagine the following scenario:

You have a data-flow that loads data from a CSV file.
That data-flow is within a ForEach loop that iterates over a collection of CSV files.
The ForEach Loop populates a variable with the name and location of the file

In other words your package looks something like this:

On each loop iteration @[FileNameAndLocation] will, not surprisingly, store the name and location of the current file.

Now, we need to make sure that the data-flow is going to process that file. We do that by dynamically setting the ConnectionString property of "FlatFileConnectionManager" using an expression. To do that select "FlatFileConnectionManager" and press F4. The properties pane will appear in which you will be able to see the entry point to the connection manager's expression collection:

Clicking the ellipsis will display the Property Expressions Editor dialog from which we select the ConnectionString property.

Thereafter we go through to the Expression Editor which is where we set up an expression that will dynamically set the ConnectionString property. In this case we use the @[FileNameAndLocation] variable:

As you can see from the evaluated value, the ConnectionString property will be set to the name of the variable. This means that on every iteration of the ForEach loop the file that the "FlatFileConnectionManager" will be pointing to to the currently iterated file.

This is only one use of expressions but hopefully through this simple scenario we've demonstrated how expressions are set and how they can be used.

Remember that expressions are not only available to be set on connection managers, they can be set on variables, task properties and precedence constraints as well. You'll find that as you begin to build ever more complicated solutions you will start to use expressions more and more.

-Jamie

SSIS: Caching result sets for re-use in a ForEach loop

jamie.thomson — Mon, 20 Feb 2006 17:08:00 GMT

Peter (sorry, don't know your surname) made a very good comment here: http://blogs.conchango.com/jamiethomson/archive/2006/02/20/2895.aspx#2903 that data-flows in a ForEach loop carry out repeated SELECTs against a data source and it would be good to be able to cache that data rather than executing the same query over and over.

Its a great point and one that I wholeheartedly endorse. Hopefully Peter has raised it at the Feedback Centre. In the meantime, there is a workaround, of sorts, that can be used in certain situations.

Peter's example talked of an OLE DB Source component that was being used in a MERGE JOIN component. He reasoned that seeing as that data wasn't changing for each iteration then the data could be cached somewhere. Well, how about populating a raw file with that data prior to the ForEach loop? That way the data source is only accessed once and the raw file can be used repeatedly as the source for the MERGE JOIN. Sure, its not quite as ideal as caching the data in memory but raw files are supremely fast and there is virtually no overhead in using them so speed should not be a problem. The main benefit here is of course that you don't put undue load on your RDBMS but also, it doesn't tie up memory resource.

If the data cache is absolutely required to be held in memory then using an ADO recordset to store the data (as explained here: http://blogs.conchango.com/jamiethomson/archive/2006/01/04/2540.aspx) could be done instead.

I find it useful to remember that there is usually more than one way to achieve a desired action in SSIS. Explore the options that are available and you may find that better options exist that the obvious one - as exhibited here!

-Jamie

SSIS Nugget: Execute SQL Task into an object variable - Shred it with a Foreach loop

jamie.thomson — Mon, 04 Jul 2005 08:55:00 GMT

Here is the first in what will hopefully be a long series of postings in what I have colloquially called my SSIS Nuggets series.

I was reading a post this morning from Sam Bendayan in which he was pondering how to store a full result set returned from the Execute SQL Task. He had followed BOL (which according to Sam states that a Full Result Set should be stored in a variable of type String) but was getting errors.

Well, that's not surprising really. BOL is wrong! A Full Result Set needs to be stored in variable of type Object. Typically you would then navigate through (sometimes called shred) this result set using a Foreach Loop (using the "Foreach ADO Enumerator"), carrying out whatever operations you wanted to on the way. In the example I have put together I simply output the contents of the current row of the result set using the familar message box.

Here's a screenshot of this package:

Here's everything that the package does:

Populates a variable called User::FullResultSet using the Execute SQL Task. The data comes from the AdventureWorks.Production.Culture table (so you will need to have the AdventureWorks DB installed)
Shreds the recordset using the Foreach loop and populates 2 variables: User::CultureID & User::Name with values from the current row of the result set
Outputs the values of User::CultureID & User::Name in a message box

Yes yes, I know that Kirk, Allan & Darren, and myself have all previously posted similar examples of doing this, but one more can't hurt right?

You can download the package from here. All you need to do to run it is edit the connection manager to point at your SQL Server instance.

-Jamie

SSIS: Getting a value out of a file to use it in our package

jamie.thomson — Wed, 15 Jun 2005 21:21:00 GMT

Someone recently asked on a newsgroup how they could grab hold of a value from a given field in the last row of a file. For example, in the file depicted below we would like to grab the value "Posh".

I figured this might be a fairly common requirement so figured I'd put something together to show how to do this. There's probably a number of ways to accomplish this, but this is the way I would do it.

The solution to this is two-fold:
1) Populate a recordset with the contents of the file
2) Iterate over the recordset using a Foreach loop, each time capturing the "Name" field into a string variable

Once the Foreach loop has finished iterating the string variable will contain the value "Posh".

Here's how it works.
1. Define a variable to hold the recordset and a variable to hold the value extracted from the recordset

2. Populate the recordset variable using the Recordset Destination component in a data-flow

3. Define a Foreach loop with the "Foreach ADO Enumerator" and set the source variable to be the recordset variable we have just populated

4. In the Foreach loop, populate the string variable with a value from the current enumerated record.

5. For demo-ing, stick on a script task that outputs the current value of our string variable.

Here's our simple control-flow. Note that there is nothing actually inside the foreach loop - its not needed.

Executing the package results in the following message box.

And its as easy as that! All we've done is capture the contents of the file into memory and then iterated over it until we get to the record we want.

If you want to try this, download the package and source file from here.

-Jamie