Shredding a Recordset

by Allan Mitchell 15 Jun 2005 14:00

Doing what to a recordset?

Shredding a recordset in this instance means that we are going to show you how to take a recordset produced in your SSIS package, loop over the rows in that recordset, break apart the columns and do something with them. This is really useful when you want to preform an action on a row of data for every row of data just like we are going to do here. Sure we could use an ExecuteSQL task to get the recordset as well but that does limit our choices of source data whereas doing it in the pipeline does not. Something useful we hope.

Retrieving the Recordset

There are a couple of things that we are going to have to setup first though

The Variables

This task requires four variables. It needs one to hold the recordset and 3 to hold the column values when we shred the recordset. Let's configure those now. In control flow we have a menu titled SSIS and if we drop that down we see Variables as one of the menu choices. Here is how the variables look after completion

Variables

The Connections

For this package we are going to need two connections. One for the source data and one for the SMTP server with which we are going to send our mails.

To set up an OLE DB Connection Manager we will right click in the Connection Manager tray at the bottom of our package and choose New OLE DB Connection

OLEDBConnStart

We click New and configure as seen in the graphic.

OLEDBConnComp

Click OK after doing that and let's create the SMTP connection manager. Same drill as before, right click in the Connection Manager tray only this time we choose New Connection. That brings us to the following screen where we now choose the SMTP Connection as shown.

SMTPStart

We then have to configure the connection manager and luckily it is one of, if not the, most easy to set up of all.

SMTPComplete

If you look in the Connection Manager tray now you should see something very similar to the following

ConnTray

The DataFlow Task

This task in this example is about one thing and that is getting the data we want into a variable so we can use it later in the package. We are going to need to fill our recordset with something so we will need to define a statement that retrieves our rows. Imagine we have sales people who take contact details from potential customers. They must contact those people within 30 days and update our CRM system. If they do not then we want to let them know through eMail that they are overdue.

So now we have something to put into a recordset let's build the means by which we can get the data. Begin by adding a Data Flow task to the Control Flow. Double click on the task to enter the Data Flow section of our package. Now drag from the toolbox onto the design surface the following:

  • An OLE DB Source
  • A Recordset Destination

The first thing we are going to do is configure the OLE DB Source so we double click on it to open up its editor

OLEDBEditor

In here we are going to specify the connection manager built earlier, an Access Mode of SQL Command and a SQL command text. The graphic above shows this completed

If we look to the left of this editor we can see a columns branch on the tree underneath the connection manager branch. This is where we need to tell the source what columns from our statement we want to flow down the pipeline so we click on that now and request that all columns be sent.

OLEDBColumns

Now we need to configure the recordset destination. Before we do that we drag the green connection coming from the OLE DB source and simply attach it to the recordset destination. This will give the destination the ability to know what to expect in terms of structure. After attaching the connector we double click on the recordset destination.

rsVarMap

The thing to see on this page is the VariableName property to which we give the name of the variable in which we want to store the recordset (rsDetails).

Just like we did on the source we need to configure some columns and we do that by clicking on the Input Columns tab. We want to write all columns to the destination so we select all the available columns.

RecordsetDestColumns

When we now come out of the destination editor our Data Flow looks like this.

DataFlowComplete

Breaking It Apart

We have seperated this part of setup because it is logically distinct from the previous phase. This is where we are going to grab the recordset, break it apart and use the values we get from breaking it apart and do something with them.

The ForEach Container

The ForEach Loop container allows us to loop over a collection of objects. We can, using this container, loop over a number of inbuilt collections but in this article we are in interested in only one of the collections, the ADO Enumerator. When the enumerator loops over the collection it returns at most one instance of the collection on each iteration. That object needs to be passed out to the tasks inside the container and we do that through a variable. In this article we use the variable User::rsDetails. We drop a ForEach Loop container onto designer from the toolbox. After double clicking on it we choose from the tree on the left Collection. The following graphic shows our collection chosen and variable assigned.

ForEachFront

So what happens then? Well on each iteration of the loop a row from the recordset is assigned to the variable with in our case three columns. We now need to Shred it. We now click on the Variable Mappings item in the tree. The following graphic shows our variables configured to accept the columns in the recordset.

DataFlowComplete

What''s happening here is that the variables on the left that we configured earlier are being assigned to the column indexes, on the right, in the recordset and they are zero based. We have no expressions to configure so that''s it for this task. Right so we have our columns in the row mapped to variables and we need to do something with them. That comes now with the Send Mail Task

The Send Mail Task With Expressions

This does as the name suggests and for us this is the way that we are going to communicate to our employees that they have not followed up on their leads. Remember the SMTP Connection Manager we configured earlier well this where we are going to use it. Drop a Send Mail task onto the designer and place it inside the ForEach Loop container.

SendMailTaskInsideForeach

We double click on the task and after configuration it looks now like this.

SendMailSetupFront

We haven't configured all the properties we need because we are going to use property expressions to do that in a moment so we click on Expressions in the tree on the left.

ExpressionsWheretoFindEllipses

As you can from the graphic above there are ellipses to the right and we need to click on them now. The window we now see is where we will choose a property on the left hand side of the window and then assign an expression on the right. Again we see here ellipses and this will take us to a great expression editor for the property. The window is shown below.

PropertyExpressionsWindow

The thing about property expressions is that they are evaluated at runtime. We can build quite complex expressions to map to our properties. In this article we need three properties to be dynamic and part of the expressions will be the variables we grabbed earlier. The properties we need to configure are:

  • Message Source
  • ToLine
  • Subject

The following three graphics show these properties in the expression editor with their expressions set.

 

The Message Source

One thing to note about this window is that we have pressed the Evaluate Expression button and as you can see our expression is evaluated and displayed. This gives you an idea of what it ill look like when this happens at runtime

ClosureDateExpression

 

The ToLine

ToLineExpression

 

The Subject

SubjectExpression

 

Now with all our expressions set the expressions window looks like this

FinishedExpressions

That''s it for expressions so the last thing we need to do is join the green connector from the Data Flow Task onto the Foreach Loop Container and we''re done.

DataFlowComplete

Summary

This article has demonstrated a number of new things in SSIS and we hope you have found it useful. Be sure to let us know if you think the Recordset Enumerator is useful and any interesting uses to which you may put it.

Comments (31) -

11/5/2008 4:32:46 PM #

Josh Blair

Allan, this is a great example.  It was well laid out and easy to follow.  Thanks very much.

Josh Blair United States

12/2/2008 4:13:56 PM #

Jorn Lukassen

Lovely. Except from a little hassle with the variable values I was able to run your configuration right out of the box. Nice tutorial. Thanks.

Jorn Lukassen Norway

12/10/2008 12:21:22 AM #

James

Hi Allan,

This seems to be as close to a solution that I can find for my problem. The issue being is that I only have SQL Server 2000 and not 2005.

Is there a similar solution available in 2000?

Cheers,

James

James Australia

12/10/2008 8:05:58 AM #

Allan Mitchell

James.

You want to loop over a recordset and do something with it for each iteration of the loop.  You may find this article helpful.
http://www.sqldts.com/298.aspx

Allan Mitchell Germany

12/18/2008 4:25:22 PM #

Geoff

Allan, what happens if you want to daisy chain two similar shredding processes, and you don't want the second one to start until the first finishes?
I have:
[ExecuteSQLTask A] --> [ForEeach A  [Dataflow A1] --> [DataFlow A2]]
-->
[ExecuteSQLTask B] --> [ForEeach B  [Dataflow B1] --> [DataFlow B2]]

My problem is that B is firing as soon as the first iteration of A is complete, and they end up running in parallel.  Is there any way to prevent B from starting before A finishes all its iterations?  I tried wrapping A and B in separate Sequence Containers, but it didn't make any difference.

Geoff United States

1/30/2009 9:58:02 PM #

Jules Clement

I followed the instructions but my Send Mail Task has the error "Validation error. Send Mail Task Send Mail Task: No recipient is specified."

Jules Clement United States

1/30/2009 11:41:17 PM #

Jules Clement

Ahh...  Set the DelayValidation to TRUE.  Works like a charm!  Thank you!

Jules Clement United States

2/5/2009 8:02:56 PM #

archana

wow! it was great. This is something that I was looking for.

Thanks a lot.

archana United States

3/11/2009 7:33:23 PM #

KK

Excellent example.very easy to follow. thanks

KK

3/30/2009 12:17:14 PM #

Nir Zohar

Hi Allen,
Is there a way to break the rsDetails to 2 bulks and run 2 loop containers in parallel (running the query against the DB only once) ?

Thanks,
Nir

Nir Zohar Israel

6/5/2009 10:01:04 AM #

Abhinay B

Hi Alan,
Great document, but i do not want to send a mail for each row in the recordset, i want to send the whole recordset as an email, any idea how to achieve this? One way is to write to a file, and send it as an attachment, I'm looking for other ways to do this

Abhinay B Singapore

7/9/2009 11:33:38 AM #

Gokul

Excellant article and well defined with steps! Thanks!

Gokul

7/21/2009 7:39:53 PM #

Marianne Daye

Thanks for keeping it simple!  Four years after it was written, this article is still helpful.

Marianne Daye United States

8/10/2009 11:34:18 PM #

Nikhil

Allan,
This is an amazing article!!! This is precisely what we wanted for our Due Date Alerts.
Thanks a lot...

Nikhil United States

8/14/2009 3:49:17 PM #

Raju

Allan,
Grate article!! I just started doing SSIS and I was able to follow, just the thing I was looking for.
Thanks
Raju  

Raju United States

8/21/2009 5:08:33 PM #

shekhar


Great article to learn

shekhar

8/28/2009 4:10:03 PM #

thanda

thank you for such a helpful article, i'm begining to love ssis a lot, Microsoft shud pay u

thanda

12/2/2009 10:44:26 PM #

julia

This is great, just the thing I need.

julia

1/9/2010 10:52:59 AM #

Arul Joseph

Hi Allan,

This is really very useful. It helps to solve many problems while creating complicated package .
Thanks a lot.


Regards,
Arul Joseph

Arul Joseph India

3/11/2010 11:24:35 AM #

Ian

Allan,

I tried following your example but when I try and start debugging my package I get the following error:

Package Validation Error
Additional information:
Error at For Loop Container: The evaluation condition expression on the For Loop "For Loop Container" is empty.  There must be a Boolean evaluation expression in the For Loop.
(Microsoft.DataTransformationServices.VsIntegration)

Can't find any help with this message and I am for sure using a "ForEach Loop" not a "For Loop" so I thought this should iterate over the Rs Object not look for a Boolean condition.

Can you offer up where I may have gone wrong?
Thanks,
Ian

Ian United Kingdom

5/28/2010 4:40:49 PM #

Buck

Very helpful!! Just what I was looking for. Thank you!

Buck United States

7/13/2010 12:08:11 PM #

Akshay

Amazing article. Great for beginners. Shows the true potential of SSIS.

Akshay India

2/16/2011 4:50:59 PM #

Brian

Hello,
Is there an associated download with this example that creates the sample database in the tempDB? I really like these examples here. Thanks

Brian United States

2/21/2011 9:40:49 AM #

Darren Green

Brian, sorry there's no download. The sample tables weren't anything special, and probably came from Northwind or similar.

Darren Green United Kingdom

4/12/2011 3:00:58 PM #

Sid

Hi Allan
Thanks a lot for this article.

Sid United States

5/10/2011 2:54:58 PM #

Nothando

Great example really easy to follow. But i am having a problem with my send mail task when I have set the ToLine to the variable it still gives me an error of "Error at send mail task [send mail task]: No recipient is specified"

Nothando South Africa

5/10/2011 3:15:49 PM #

Nothando

Runs through everything now just get an error at the send mail task saying---Error: The type of the value being assigned to variable "User::currEmail" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.

Nothando South Africa

5/11/2011 4:34:01 PM #

Ashish

Hi Allan,

Nice article. Is there a way to use the recordset as source in further DFTs/Tasks?

Regards,
Ashish

Ashish India

5/25/2011 1:03:18 PM #

Valentino Vranken

Thanks Allan, even six years after you wrote this article it's still useful!

In my scenario I had to clean a bunch of folders by deleting outdated subfolders from them.  So I developed a package that would clean one folder.  That got called in a ForEach loop in another package, which was looping over a recordset that contained the list of folders to be cleaned.
I used a different method to populate the recordset object variable though, through the Execute SQL task!

Best regards,
Valentino.

Valentino Vranken Belgium

6/6/2011 5:06:36 PM #

David

Wonderful tutorial. Really really very useful and clear.
Thanks a lot!!!!

David Spain

6/27/2011 4:32:14 PM #

Leif Hurst

I've spent the last week trying to figure this out (SSIS self-proclaimed newbie) and this helped me knock it out in 10 minutes. Where have you been good sir?!

Leif Hurst United States

Add comment

  Country flag

biuquote
  • Comment
  • Preview
Loading

RecentComments

Comment RSS