2010 Recap

Categories: News, Professional, SSC
Comments: 1 Comment
Published on: December 28, 2010

As a part of a recap on the year that was 2010, I should start with a recap of my goals.  For that review I had to go back to my Q3 review.  As I looked it over, I found that there was nothing really different from that review than now that needs to be reported.

I have been extraordinarily busy this quarter with things other than that list of goals and things other than my blog.  That is not necessarily bad – and is actually quite good.  Here are some of the posts that talk about some of what has kept me busy.

  1. New Baby Girl born
  2. Job Change
  3. User Group
  4. Holidays and Birthdays (4 in December alone between myself and children)

In all reality, despite all of the busy-ness of the past quarter – it has been good stuff.

As I ponder over 2011, there have been plenty of good things that have happened to me and my career.  Several things have changed for me over the year.  I have blogged about many of those things as well.  Some of the professional related things are:

  1. Started Tweeting
  2. TSQL Tuesday (even hosted one)
  3. Actively Involved with the Local UG
  4. Blogger Awards
  5. SQLSat

All in all there have been some things that have helped me a lot in my career this past year.  This has been a full year with plenty to show for it.  For me, the items that are harder to quantify over this past year has been the friendships I have gained.  In addition to those friendships, I would also add that my network has grown significantly.  More friends – bigger network I guess is one way to describe that.  But there is also the community involvement that has increased exponentially (over prior years) for me.  I have put myself out there more (blog, speak, tweet, etc).  I have written a few articles that have been published.  I have more that I need to write and will write.  It is a bit weird to think of being an MVP, but some people out there have brought it up.  I know I was nominated, but I am completely ok with not being a recognized MVP.

Another aspect of my career that has improved over the past year is the improvement in my communication and writing skills.  This is a side-effect of the blogging and article writing.  I just need to become more consistent with it and maybe start pumping out some more technical as well as longer articles.

Next years goals will be coming soon.

SSIS Multiple File Export

Comments: No Comments
Published on: December 28, 2010

Have you ever needed to export different data sets to different flat files?  Each of these data sets could be customer information for different clients – but they all require the same fields.  I have run into this requirement a few times and have found that it is not as hard as it may sound.  To evolve that requirement, I have also had the requirement to export multiple different files for each of the clients.  For the sake of this post though, I will just stick to how to do the first level.  (It is only a little more complex but not much different to add the extra requirement.)

SSIS to Save the Day

SSIS has a looping mechanism built in to help achieve this.

If you want to learn more about this particular data flow object, here is the MSDN resource on it.

When trying to create an SSIS package solution that meets our requirements, the first step (in my opinion) is to make sure you have an Object variable created for use by the Foreach Loop Container.

To use this variable in the foreach loop container, we first need to load data into it.  This is accomplished by using an Execute SQL Task.

Note:  There is a red-x in this image simply because I have not defined any properties for this object.

The Execute SQL Task needs to be given a data source and a SQL command to run.  The result set of this task should be set to full result-set on the general page.  On the result-set page, set the result-set to be stored in the object variable that we created already.  On the same page, you should set the result name to a value of “0” (zero without the quotes).

Now we can start looking more at the Foreach Loop Container.  When we open the properties of the Foreach Loop Container, we want to go to the Collection page and adjust two settings.  The first is to select the appropriate type from the dropdown.

In this case, we will select ForEach ADO Enumerator.  The next change to be made is the next drop down labeled “ADO Object source variable:”.  In this box we will select the object variable that we already created.

Now we need to configure the Variable Mappings page.  For this you can either create a new variable from this page, or use variables you have already defined.  Let’s Create some variables from here.

Once that is established we can work on configuring the Data Flow task.  For the sake of the example, we can do something simple.  It is a good idea to make sure you have your connection managers created by this point.  If they aren’t there – then let’s create some.  First, let’s create an OLEDB connection.  Second, we need to create a Flat File Connection Manager.  I won’t cover how to create those connection objects.  I will cover how to make a configuration change to the Flat File Connection Manager though.  To make sure the files will be written differently – we should use an expression on this connection manager.

In your flat file connection manager, you will need to look at the properties tab in order to create an expression.  In the Property Expressions Builder, select the ConnectionString Property from the drop down.  In the expression side build something like this:

[codesyntax lang=”sql”]


Now back to that Data Flow task I mentioned already.  In your Data Flow Task, select the appropriate OLEDB Connection manager that you have created.  Then select SQL Command in the data access mode dropdown menu.  In the SQL Command text box, type an appropriate query to retrieve your data.


[codesyntax lang=”tsql”]


Then click on the Parameters button.  From here, you will assign the appropriate variable to the Parameter that was created in the query.  In our example, we should use the variable [ User::TestSub ].

That pretty much covers it.  In a follow-up I will go through an actual setup that I have created.  More screenshots, and slightly different setup than this baseline.  Hope you enjoy.

page 1 of 1

December 2010
« Nov   Jan »

Welcome , today is Monday, January 27, 2020