• Iowa City, Iowa – Friday, July 26th (before SQL Saturday 239 – East Iowa)
• Orlando, Florida – Friday, September 13th (before SQL Saturday 232 – Orlando)
• Denver, Colorado – Friday, September 27th (before SQL Saturday 190 – Denver)

For each of these events, I’ll be delivering a full day of content entitled “Real World SSIS: A Survival Guide.”  This day of content consists of many lessons that I’ve learned – many of which were learned the hard way – through my decade or so in this business.  I’ve got lots of demos to illustrate the concepts we’ll be covering.

If you are able to make it to any of these SQL Saturday events, I’d be honored if you’d join me for one of these talks.  Registration is open for all three seminars, as well as each of the SQL Saturday events.  I hope to see you there!

Before we examine the how, let’s talk about the why.  Although this is not a design pattern you’ll have to use every day, there are any number of cases that would lend themselves to building and using an ADO recordset enumerator:

• You need to create a series of export files – one per client – showing that client’s charges for a given period.
• You’re dealing with a very large set of data, and/or your processing hardware has limited resources.  You want to explore breaking up the workload into smaller chunks to be processed serially.
• You are performing a data load operation, and want to design the package in such a way that the loaded data can be immediately used as a validation source in the same package execution.

For cases such as these (among others), using this design pattern can be an excellent way to address your ETL needs.

Design Pattern

At a high level, this design pattern will have three moving parts:

• A relational query used to populate the object variable (thus transforming its internal type into an ADO recordset)
• A For Each Loop container to loop through the list stored in this variable
• Some business logic for each value (or set of values) in each row of the object variable

Note that while the first two moving parts I mentioned will be relatively consistent from one package to another, the business logic component will, by nature, vary greatly from one package to another.  For the purposes of this post, I’m purposefully keeping my business logic piece simple so as to not distract from the larger design pattern.

For my sample data, I’m  going to deal with a data domain that is near and dear to my heart: baseball.  In this case I want to get a list of all postseason baseball games, and for each game, create an export file detailing the at-bat statistics for that game.  Because I don’t know at design time how many games will be played in the postseason, I can’t simply hard-code my outputs – I need design the ETL in such a way that the data will dictate, at runtime, the number of output files and their respective filenames.

Configuring and Populate the Object Variable

The first thing I’ll do in my demo package is set up an SSIS variable, giving it the data type of Object.  As shown below, I’m using the SSIS variable named [GameList] as the object typed variable, which will store the ADO recordset list of playoff game IDs that should be processed.  Also included is a variable specifying the directory to which the output files will be written, as well as a variable to store the individual game ID for each iteration of the loop.

Next up, I’m going to add an instance of the Execute SQL Task to the control flow of my package, typing in my query to select the IDs of the playoff games from the database.  In the settings for this task shown below, you’ll also see in the highlighted portion that I’ve changed the behavior of the Result Set to use Full result set (remember the default is None, which would expect no data rows to be returned).  By setting this behavior, I’m configuring the task to expect a result set to be returned.

When I configure the Result Set setting in this way, I also need to indicate where those results should end up – specifically, I have to indicate which object typed variable will store these results.  In the Result Set tab of the same task, I’ll set the variable name to use the [GameList] variable I set up in the previous step.  Also note that the result set name should always be 0 in this case.

What I’ve done here is quite simple, and required no code (other than the SQL statement, of course).  What’s happening behind the scenes is a little more complex, however.  At runtime when the Execute SQL Task is executed, the [GameList] variable will be instantiated as a new object of type ADO recordset.  Note that this action will not change the data type shown in SSIS; even though the in-memory object will be configured as an ADO recordset, it will still show up as an object type variable in the designer.  This ADO recordset object will then be loaded with the resulting records, if any, from the query I used in the Execute SQL Task.

Using the SSIS Variable as an Enumerator

My next step will be to consume that list, processing each game ID in turn to extract the data I need.  To handle this, I’ll add a For Each Loop container to the control flow, and connect the previously configured instance of Execute SQL Task to this new container.  When I configure the properties for the loop container, in the Collection tab I’m presented with several different options for the enumerator (the list that controls how many times the logic within the loop will be executed).  Since I’m working from the ADO recordset list created in the previous step, I’m going to select Foreach ADO Enumerator, and use the variable drop down list to select the [GameList] object variable.  I also set the Enumeration Mode to use Rows in the first table, which is the only option I can use when working with a ADO recordset (note that we have more options when working with an ADO.NET recordset, which I plan to cover in a future post).

With the collection tab set to use my object variable as an enumerator, I’ll next jump over to the Variable Mappings tab.  It is on this tab where I will align fields in the record set with variables in the package.  As shown below, I’m only expecting one column to be returned, and for each iteration of the loop, this value will be stored in the variable named [ThisGameID].  As you can see, I’m using index [0] to indicate the position of this value; if the record set is expected to return more than one column, I could add those in as additional column/variable mappings, using the ordinal position of each column to map to the proper SSIS variable.

With that done, I’ll add an instance of the Data Flow Task to the loop container configured above, which will complete the work on the control flow:

Configure the Business Logic in the Data Flow

Now it’s time to dive into the data flow I just created.  Within that data flow, I’ll add a new OLE DB Connection component, the purpose of which will be to retrieve the at-bat statistics for each playoff game. To the output of that source, I will attach an instance of the Flat File Destination, which will be used to send each game’s data to the respective output file.

Within the data source, I need to configure the query such that it retrieves data for one and only one game at a time.  Since the current game ID value is stored in the [ThisGameID] SSIS variable, I can simply map that variable as a query parameter, so that each execution of this SELECT query will limit the results to only include statistics for the current game ID.  As shown below, I’m using a parameter placeholder (the question mark in the query) to indicate the use of a parameter:

… and when I click the Parameters… button, I can map the SSIS variable containing the game ID to that query parameter:

I have already configured an instance of the Flat File Destination (and by extension, set up the Flat File Connection Manager) to allow me to write out the results to a file, but how will I create a separate file per game?  It’s actually quite easy: by using a simple SSIS expression on the ConnectionString property of the Flat File Connection Manager, I can configure the output file name to change on each iteration of the loop by using the game ID value as part of the file name.  As shown below, I’m accessing the Expressions collection within my Flat File Connection manager, overriding the static value of the ConnectionString property with an interpreted value using the amalgamation of two variables – the directory location I specified earlier, along with the current game ID.  Remember that since SSIS variables are evaluated at runtime, the value of the variables can change during execution, thus allowing the use of a single Flat File Connection Manager to write out multiple files during each package execution.

Finally, when I execute the configured package, I end up with a few dozen output files – one per playoff game.  As a side note, my Texas Rangers were only represented in one of those playoff games from last year.  We’ll get ‘em this year.  As shown below, each output file is distinctified with the game ID as part of the file name.

Conclusion

Use of the SSIS object typed variable can be a very powerful tool, but it need not be complex.  As shown in this example, we can easily leverage the object variable for iteration over a result set without writing a single line of programmatic code.

In the next post in this series, I’ll dig further into object typed SSIS variables, and will explore how to use and manipulate other types of objects not natively represented in SSIS.

This will be my third year to present at the PASS Summit.  As always, I’m happy to be able to participate and humbled to be selected to speak.

On a side note, this is the first time in several years that the Summit has been scheduled to be held outside of Seattle, and the first time it will have been held in Charlotte. Although I have to admit that I’m going to miss Seattle, I’m happy that PASS was open to community feedback requesting that the Summit be occasionally held in locations outside of Seattle.

Now we just need to task someone with finding us a karaoke dive bar in Charlotte.

Join SQL Server MVP Tim Mitchell and Andy Leonard as they discuss and demonstrate scripting in SSIS! In this demo-packed session, two co-authors of the book SSIS Design Patterns share their experience using the Script Task and Script Component to accomplish difficult transformations and improve data integration.

You can register online here.  We look forward to seeing you tomorrow!

Monday (3/18): Northern Colorado Database Professionals

Tuesday (3/19): Boulder SQL Server User Group

Wednesday (3/20): Colorado Springs SQL Server User Group

Thursday (3/21): Denver SQL Server User Group

At each of these user group meetings, I’ll be delivering a session entitled “When ETL Goes Bad: Handling Errors and Anomalies in SSIS”.  This is the second such tour that I’ve done: two years ago I made trip up there and spoke at 3 of these user groups.  It’s always a pleasure to visit this area – the Colorado SQL community has a lot of great people, and I’ve got several friends in the area that I hope to see.

This will be a working trip, too.  I’ve got a client in the Denver area that I’ll be working with during the week, so I expect it to be a very packed week.  I’m taking the day off on Friday and am staying over until Sunday morning, so I should have two full days to squeeze in some skiing while I’m there.

If you’re in the area, please stop by one of the user group meetings and say hello.  And if you’re up for some skiing on Friday or Saturday, let me know .

You can read more about this release on the SSRS team blog and SSAS team blog, or download the code from the Microsoft website.

When defining variables in SSIS, the ETL developer has several data type options to choose from depending on the information to be stored in each variable.  Included in the options are String, Boolean, Char, DateTime, and several flavors and sizes of Int.  However, there’s another variable data type that is very handy but also underutilized: the Object data type.

Why Object Variables?

Variables with a data type of Object are the most flexible variables in SSIS.  Data types such as Int, String, and DateType are designed to store just one type of data; however, an Object typed SSIS variable can store almost any type of data, even information that can’t otherwise be represented in Integration Services.  In most cases, SSIS doesn’t even have to be configured to know what type of data you’re storing in an Object typed variable – usually, it can simply pass the value along the wire as a bunch of bits without knowing or caring what’s in there.

When considering the use of Object typed variables in SSIS, I’ll give the same disclaimer that I give for using script tasks/components in SSIS: Just because you can doesn’t mean you should.  If a native data type will work to store any possible value for a particular variable, by all means, don’t complicate your code by adding unnecessary moving parts.  Use Object typed variables only when a native type won’t do – otherwise, stick to the well-worn path.

Common Uses

There are a few cases that come to mind that lend themselves to using Object typed variables in SSIS:

• Iterating over a result set.  This is probably the most common and well-documented use of Object variable, as well as the easiest to implement as it requires no manual coding.  This pattern will allow you to retrieve a set of data from a database, and then perform some operation for each row in that result set.  I’ll work through the mechanics of how to do this in my next post.
• Handling binary data.  A common example of this is extracting binary data from or writing binary data to a VARBINARY field in a relational database.  If you need an interim storage mechanism in which this binary data should be stored, and Object variable can be a good solution.  In this case, the ETL pipeline doesn’t necessarily need to be aware of what is stored in the Object typed variable .
• Creating or consuming binary data in SSIS.  Let’s say you need to either generate or process binary data as part of your ETL.  For example, you might need to retrieve a JPEG or PNG file from the file system and write it into a relational database, or retrieve a binary object from a database and process the various elements of that object.  By storing said data in an Object typed variable, you can directly write to or read from this variable within your code.

Risks and challenges

Naturally, with a construct as flexible as an Object typed variables, there are a few challenges to be aware of when considering when and how to use objects in your SSIS packages.  Among the risks:

• Some coding required.  In many cases, especially when you’re processing the information contained in the Object typed variable (as opposed to simply passing the value through from a source to a destination), you’re going to have to write some code to address that object.
• SSIS expressions not allowed.  Because they are designed to store a variety of information structures, Object typed variables cannot be used in an SSIS expression.  Even if the underlying data stored in the variable is of a type that could be stored in a native SSIS type, attempting to add an Object typed variable to an SSIS expression will throw an error.
• Debugging challenges. If you overflow an Int32, or happen to truncate a string in SSIS, you’ll get a (mostly) friendly error message telling you what went wrong.  Often, when dealing with Object typed variables, you don’t get that luxury in SSIS.

Conclusion

Object typed variables in SQL Server Integration Services allow a great deal of flexibility when dealing with atypical data structures in the ETL cycle.  Although they are not as commonly used as simple native types, Object typed variables can make otherwise difficult ETL tasks easier.

In my experience, I’ve found that Object typed variables are sometime avoided in SSIS packages simply because they’re misunderstood or believed to be too difficult to use.  In the next few posts in this series, I’ll illustrate how the Object variable can be leveraged in your SSIS package by demonstrating a few use cases where Object typed variables in SSIS are appropriate.

But what about that resume?  After all, the resume is just a very small piece of the big picture… a document that be easily thrown together as soon as you need it – right?  (Note: If you nodded after that last sentence, please, keep reading.)

“Tell me about yourself…”

Writing an effective resume isn’t easy.  Most people think writing about themselves is easy until they actually go about doing it.  To describe oneself in a way that is flattering but not overly boastful, colorful enough to be interesting yet still truthful, while keeping the description to one or two pages at most, takes a great deal of time and concentration. Sadly, I see some resumes that appear to be an afterthought – just a means to an end, without much planning or proofreading involved.

Resumes that were thrown together at the last minute have several telltale signs:

• They enumerate every piece of software or hardware you ever touched, without describing how you used said hardware or software to solve actual problems.
• They are full of filler phrases like “dynamic”, “uniquely qualified”, “fast learner”, “track record”, and “progressive”.
• They contain too many errors in grammar or spelling.  (How many is too many? Any number greater than zero.)
• After I read the whole resume, I still have no idea who you are or what you can do for the company.

My friend Steve Jones delivered a professional development presentation some time back in which he recommended that everyone touch their resume at least once per quarter, regardless of whether they were actually looking for a new job.  I believed in that advice so strongly that I’ve repeated it numerous times since.  However, like an out-of-shape cardiologist, I’ve been quite adept at ignoring my own advice.  When I recently needed a current copy of my resume for a training initiative, I discovered that I had not updated this document in over three years.  I succumbed to the thought that “I’ve got a good job, I’m not looking to make a move, so it can wait” and let the information go stale.

But I’m not looking for a job…

Is keeping your resume up to date really necessary, unless you are (or expect to soon be) looking to make a career move?  I submit that it is important, for several reasons:

• A properly written resume takes time to create.  Don’t allow yourself to be sucked into thinking that you can spent an hour or two to create a superb resume.  At a minimum, you’re going to need several days to get it right.  A resume isn’t ready to be sent to a prospective employer until you’ve gone over it, word by word, to make sure it’s perfect.  Write your resume, put it down for a few days, and come back and reread it to be sure it really tells a story.  You should engage others as well – get as much feedback as possible before you finalize it.  These things take time!
• You might not be looking for a job today, but you might be tomorrow.  Let’s face it – for those of us in the ranks of full-time employment, we’re just one really bad day away from joblessness.  Anyone who works for someone else could, on any given day, find himself out of work due to a high-profile error, an unforeseen downturn in business, a personality conflict, or for no reason at all (in many states).  If you find yourself suddenly and unexpectedly looking for a job, you shouldn’t let a stale resume slow down your job search.
• Your career changes faster than you think, and it’s easy to lose track of those changes.  During the three years that I ignored my resume, I had contributed to two books, was elected to the board of my local user group, received the Microsoft MVP award three consecutive years, and learned several new technologies.  What I thought would be an easy task of documenting three years worth of career changes turned out to be much more work than I expected.  Especially in high-tech fields such as ours, careers can evolve quickly, and an up-to-date resume should reflect those changes.
• You occasionally need an up-to-date resume for reasons other than getting a job.  At a previous job, I was asked on a few occasions to provide a copy of my resume for the benefit of potential clients of my employer – these prospects wanted to know the kind of people they’d be working with in case my employer was selected as their service provider.  Further, some extracurricular activities (community board service, authorship opportunities, etc.) require the candidate to produce a current resume.

Conclusion

Keeping your resume up to date takes time, and it’s even harder to motivate yourself to keep current if you’re not looking for a job.  But in the same way you continue learning and networking while not actively shopping for a new position, it’s beneficial to keep your resume polished and ready to go.

Side note: If you’re a senior BI professional and are looking for a new challenge, why don’t you send me a copy of that freshly-updated resume?  My employer is hiring, and it’s a great place to work!

The BI practice here at Artis has been the strongest team I’ve ever worked with, and I’m very excited to have my friend Erin joining us here.  If you know Erin, be sure to give her a shout of congratulations.

I’m happy to report that in addition to my full-day preconference seminar at SQLBits, I’ll also be delivering a regular session entitled Cleaning Up Dirty Data with SSIS on Friday, May 3.  For those unfamiliar, SQLBits is the largest and most well-known SQL Server event outside of the US.  This year’s event will be held in Nottingham, England from May 2-4.  I’ll be speaking all day on Thursday, May 2nd, with the data cleansing session to follow on Friday.

Registration is still open, and until next week you can still get in on the early bird pricing.  If you make it to SQLBits, please do stop by and say hello.