Recently, my friend Jack Corbett asked a question on Twitter:

In a nutshell, the SSIS foreach loop will enumerate a given list of items (files in a directory, nodes in an XML file, static list of values, etc.) and will perform some operation for each of the items in the collection.  This behavior is similar to foreach loop constructs that are found in most high-level programming languages.  These programming elements normally include a continue statement, which allows you to stop processing of the current item and move on to the next one.  Jack hoped to find this capability built into the SSIS foreach loop, but unfortunately it doesn’t exist natively.

However, there is a workaround that will allow you to reproduce the continue statement in the SSIS foreach loop.  By including a “dummy” data flow within the foreach loop, we can use a precedence constraint to skip some of the items in the list based on the value of a variable.  To demonstrate, I’ll use a foreach loop to make a copy of all of the files in a given directory except for one specific file.  I'll first set up the foreach loop in the control flow pane to enumerate over the files in a particular directory, by using the Foreach File Enumerator setting:

Now, within the foreach loop above, I create two data flows; one that does nothing at all, and the other that reads the current file in the list and writes the data out to a new file, the name of which is set using an expression. 

Next comes the important part: I create a precedence constraint from my dummy data flow to the one that actually performs the work, and I’ll edit the expression to exclude one of the file names:

In this scenario, the package flow will only move from the dummy data flow to the one containing our business logic if the variable value, in this case the current filename, does not match the one we intend to skip.

So the short answer is that the SSIS foreach loop doesn’t have native capability to skip processing for certain values, but using a do-nothing container and a precedence constraint within the loop is a creative and effective substitute.

[Cross-posted from SQL Server Central]

Continue in Foreach Loop.dtsx (46.79 kb)

The monthly North Texas SQL Server User Group (NTSSUG) meeting will be held this Thursday, September 17, at 7:00pm at the Microsoft campus in Irving, Texas.  Our guest will be SQL Server MVP and consultant Geoff Hiten, who will be presenting “Bad SQL - Why Does This "Perfectly Good" T-SQL Run So Slow?”

Admission is always free, and pizza and drinks will be provided.  Bring a friend!

For more information on this event or the NTSSUG group, visit our website at http://northtexas.sqlpass.org/.

In a couple of previous posts (Part One and Part Two), I shared some thoughts about starting a career path as a database professional, including some notes from my own journey.  In this final segment, I’ll share a few discrete tips that will hopefully be useful to those interested in this field.

Build Your Foundation: You’re going to need some base skills outside the immediate scope of SQL Server.  Regardless of what role you pursue (DBA, database developer, or BI engineer), you need to have at least a decent understanding of the technologies that support SQL Server.  Get to know how Active Directory works, particularly with respect to users, groups, and authentication.  Learn about Windows server architecture, including clustering technology.  Spend some time learning about disk configuration, RAID levels, and SAN architecture.

Get Training: Be proactive in training yourself. If your employer won’t provide training, don’t use that as an excuse – remember that you are the one with the most skin in the game in your career development.  Take advantage of the training resources available to you, many of which are free (here’s a good list to start with).  Get a developer copy (nominal cost) or an evaluation version (free) of SQL Server, and start practicing.  Get a couple of books and spend the time reading them – in fact, most beginner SQL Server books will have some examples you can work through to get some good hands-on work with the product. 

Get Experience: Spend some time getting your hands dirty with real projects.  Even though you won’t find a DBA job without having previous experience, there are ways to build tangible, legitimate, documentable experience even if you’re not working full-time as a database professional:

• Get experience at your current job.  Do you have a DBA at your place of work?  Spend some time with that person or team, and find some ways you can assist them, even if you’re performing menial tasks during your lunch break.  If your place of work doesn’t have a DBA, are there currently any SQL Server instances there?  Volunteer to run the backups, or to do a test restore to test your disaster recovery procedures.  The key here is getting your foot in the door; prove you can do a good job with small things and you’ll eventually receive more responsibilities.
• Volunteer.  There are a lot of organizations that would welcome volunteer labor to assist with their technical needs.  Talk to the folks at your kids’ school, local nonprofits, places of worship and the like, and find out if there are any SQL Server-related projects needing some attention.  You may even find structured volunteer opportunities, such as GiveCamp (FWIW, I’ve participated in GiveCamp for a couple of years, and it’s a great opportunity).  Even though you’re working for free, you’re building experience on legitimate projects which will improve your skills and provide documentable evidence of your work.

Get Involved in the Community: Much has been written about the benefits of being involved in the SQL Server community, and I won’t try to reproduce it all here.  Suffice it to say that active involvement with other similarly-minded professionals provides an avenue for learning and advice, and can expose career opportunities not otherwise available to you. Join PASS and take part in their free webcasts.  Find a local SQL Server user group and attend regularly.  Join Twitter and follow some SQL Server tweeps. The community is open to newcomers, and most venues welcome even the most basic questions for those trying in earnest to learn about SQL Server.

Pay Your Dues. Know up front that you’re not going to walk straight into a DBA job.  With hard work and perseverance, you can eventually work your way into a role where you're handling difficult problems and working on cutting-edge projects.  To get there, however, you’re going to have to start small, running database backups, changing tapes, and reviewing log files.  Note that paying your dues may include some seemingly unrelated tasks (see Build Your Foundation above).

Above all else, keep your chin up - don’t get discouraged by the “Can’t get a job without experience, and can’t get experience without a job” cycle.  Put yourself in an employer’s frame of mind: would you want to hand over the keys to the kingdom to an untested, inexperienced rookie?  Know that it won’t happen overnight, and that you’ll most likely take a series of small steps rather than a huge leap into this field.  Prove yourself worthy with the small stuff, and the big stuff will come in time.

Good luck to you!

[Cross-posted from SQL Server Central]

Just a quick update on the SQL Saturday event we are planning for the Dallas area next year.  Late last week our local event planning committee met to discuss the big stuff (venue, date, and advertising) and came away with a short but critical action list. 

We’ve got a venue that has given soft confirmation to accommodate us, and we’re working to lock that in this week.  For the event date, we had to dodge a number of other events in our January/February timeframe; the remaining Saturdays were January 23 and January 30, and we expect to have a decision between the two in the next week or so.  We’ve got some folks who are contacting potential advertisers, both international and local, to help fund what we expect to be an event with 500 or more professionals in the database, development, and business fields.  [Sidebar: If you are a business principal interested in sponsoring this event, let me know – tdmitch at gmail.]

There are still significant decisions to be made and lots of work to do.  Once the date is set, we’ll publish the event to the SQL Saturday website and open it up to registrations and speaker abstracts. Later, the abstracts will need to be reviewed and the schedule created from the submitted sessions.  I’ll also be contacting several high-profile speakers to see if they can work this event into their schedule.  We need to arrange catering for lunch, along with donuts, snacks, and drinks for the remainder of the day.  We will be seeking a host hotel for out-of-town attendees, and the speaker reception and post-event party will need to be set up.  That’s just the big stuff – there are flyers to print, conference bags to stuff, tables to set up and tear down, and hundreds of other little things that (hopefully) will be planned well in advance.

Fortunately, we’ve got a good group that has expressed a willingness to get involved.  Our combined area user groups (Dallas and Ft. Worth) represent hundreds of technical professionals, and we’ve already had a number of volunteers come forward and offer to help organize the event or present a session.  Several of our group, myself included, have been to SQL Saturday events before and have been witness to what works and what could use improvement.

In the meantime, we’re still gathering a list of volunteers.  If you’re interested in helping plan or execute this event, please let me know and I’ll add you to our volunteer contact list.  We’re also building a list of those interested in presenting, and we’ve already heard from several of you who would like to speak.

I’ll keep the updates coming as things develop.

I see a lot of questions on the forums about updating existing data in SSIS.  When the update is dynamic and is based on elements within your data flow, a common mistake is to use the Ole DB Command within the data flow pane:

The above poorly designed update has us retrieving data from some source, and then running an update on our target database based on one or more values in the source.  Does it work?  Sure.  The problem occurs when you touch more than a handful of rows.  When you bring in the Ole Db command into the picture in a data flow, you’ll be firing the statement in this control once for every row of data in your pipeline.  So let’s say our source retrieves 100,000 rows into the data flow: the downstream UPDATE command will be executed 100,000 times!  Such operations could bring the most capable server to its knees.

A better solution could include staging your data.  Using this method, you’ll retrieve the data from the source and write it into a staging table in the destination database.  You can then use an Execute SQL task to run your update in a more organic manner.

Set up your data flow as shown above to pull in the data to a staging table, then you can run a single UPDATE statement:

UPDATE i
SET InvoiceAmount = st.UpdatedInvoiceAmount
FROM Invoices i
INNER JOIN StagedData st ON i.InvoiceID = st.InvoiceID

The advantage here is that you’re executing the expensive UPDATE statement once for each table rather than once for each row affected.

Of course, there are some situations that explicitly disallow the use of staging tables in destination systems.  If storage or access restrictions keep you from using this method, you may have to use the row-by-row insert, so be aware that it's going to be a bottleneck.

[Cross-posted from SQL Server Central]