Simple SSIS example with DataFlow, SendMail, ForEach Loop, Aggregate, Result Destination task with Microsoft Integration Services 2008 R2

Well, these days, football fever has covered whole globe so thought to include example related to football in this example. This is just an example; I don’t claim that the data is right regarding current football world cup.

Suppose we have a list of name and total goal hit by individual and we want an email to shoot with total goal,
average goal and highest goal from SSIS.

We are first going to create one dummy table in our database which will be used in this example.

CREATE TABLE FootBallTopScorers

(

      Name varchar(20),

      Goal int

)

GO

INSERT INTO FootBallTopScorers

SELECT 'Gonzalo Higuaín',3 UNION ALL

SELECT 'Elano',   2  UNION ALL

SELECT 'Asamoah Gyan', 2 UNION ALL

SELECT 'Luis Fabiano', 2 UNION ALL

SELECT 'Diego Forlán', 2

GO

SELECT * FROM FootBallTopScorers

GO

Ok, once you are done with creating above table and its data in SQL Server database. Let us close SSMS and open BIDS (Business Intelligence Development Studio) or Visual Studio 2008/2010 (if you have full version and template).

Create a new project by selecting “Business Intelligence Projects” tab and “Integration Services Project” template.

Once you see “Control Flow” tab in your project, follow the steps given below.

1.)    Drag and drop “Data Flow Task” from tool box to your “Control Flow” tab.

2.)    Rename it to “Get Goal Data” so it would be easier to remember what this “Data Flow” is going to do.

3.)    Now Drag and Drop “For each loop” task below “Get Goal Data” and put “Send Mail” task to in your “For Each loop” container  “Send Report”

4.)    Click the Data Flow Task you renamed to “Get Goal Data”, There should be a small, dangling arrow extending from the bottom of the Data Flow Task. Drag the arrow to the Send Mail title bar and drop it to create a precedence

Now, Your screen would seems like below.

Now, let us set some variables which will hold our data returned from SQL Server.  If you are not able to see “Variable” window, you can either right-click on designer and select “Variable” or you can go to View Menu->Other Windows -> Variable

Once you get Variable Window, You will find one small button in Variables Window’s tool box, named “Add Variable”, use it to add variable.

1.)    Add three variable named “AvgGoal”, “TotalGoal”, “HighestGoal” with datatype Int32 and default value to 0 which is by default.

2.)    By default NameSpace of variable is “User”, let us change it to “Football”. You might not be able to see “Namespace” column in “Variables” window. You can make it visible by clicking on toolbar button of “Variables” window named “choose variable columns”.

3.)    One more variable with same namespace should be added. Variable name should be “Results” and type should be “Object” which will hold complete record set return by query.

Now your variable windows would seem like this screen.

Now we are going to configure “Data Flow” task, so just double click on that “Get Goal Data” and you will be redirected from “Control Flow” to “Data Flow” tab.

1.)    From the Toolbox, drag and drop an OLEDB Source Adapter from the Data Flow Sources group of components and rename it to “Retrieve Goal Data” and double click on it.

2.)    As soon as you will “Double Click” on it, you will find “OLEDB Source Editor”, now set your SQL Server, Database. In the Data Access Mode field, select SQL Command.

3.)    Write following query in “SQL Command Text” field. Select Goal From FootBallTopScorers

Since we want aggregate data like Total, Highest (Max), Average etc., let us use one more tool of SSIS in “Data Flow” tab.

1.)    Drag and Drop “Aggregate” task from tool bar to your “Data Flow” tab, right below you OLEDB source, and rename it to “Aggregation”

2.)    Drag the green (on the left) data flow output to the Aggregate component and drop it.

3.)    Now, setup you “Aggregation” task by double click on it. You will find “Aggregate Transformation Editor” to set your output. You have to set three alia with its Operation. Mainly named as “TotalGoal”, “HighestGoal”,”AvgGoal” with Sum, Maximum and Average aggregate function in “Operator”.

Finally your screen of “Aggregate Transformation Editor” would seem like this:

Well now we can use “Data Conversion”, we in this case we are having same datatype in database as well as in Variables but if we have different datatype, we can convert it. Anyway, drag and drop “Data Conversion” task below your “Aggregation” task and double click on it to configure.

Data Conversion task should be configured like below given screen:

Now, insert “Recordset Destination” task below you “Data Conversion” task which will hold all converted data. Double click on “Recordset Destination” task to configure it.

In the VariableName property, type in “Results,” which is the name of the variable of type Object you created earlier and from “Input Column” tab, select all columns which have converted to int. see the screen below for more details.

Now, we are all set to go, 90% of the task we have done.  again go to “Control Flow” tab from your “data flow” tab. You have to set foreach  loop task by double clicking on it.

1.)    From “Collection” tab in ForEach Loop task. Set “Enumerator” property to “ForEach ADO Enumerator”

2.)    In “ADO Object                 source variables” select “Football::results” variable.

3.)    From “Variable Mapping” tab, select our three variables which are going to display the data.

Screen should be like this:

Now, you are just one step far from completing this first package.  Double click on “Send Mail” task and set credential of your SMTP server by “Smtp Connection “ Property. Set From, To email address property along with email subject.

Now go to “Expression” tab. Click on ellipses button of “Expression” property under “Expression” tab.

From “Property Expression Editor”, select “MessageSource” property and keep following text for email in that.

"Here is the GOAL summary " +"Average " + (DT_WSTR, 20) @[Football::AvgGoal] +

"Total Goal" + (DT_WSTR, 20) @[Football::TotalGoal] +

"Highest Goal" + (DT_WSTR, 20)@[Football::HighestGoal]

We are using all three variables in our email body. Now, run this package by hitting F5 and enjoy your first package.

If any task would get failed, it will be displayed with “RED” color and you can get an exact idea about that from “Execution Result” tab which will be there after you end up your package.

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com