Hi i followed below steps for creating of package but how to give SMTP server name for sending mail when package executed in my personal computer i used DOS promt for getting SMTP server name where i gave command ping.mail.mysite.com and ping.smtpmail.mysite.com but it shows is not recognised internal or external command plzz help me....... samba ch 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 dont 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 Higuan',3 UNION ALL SELECT 'Elano', 2 UNION ALL SELECT 'Asamoah Gyan', 2 UNION ALL SELECT 'Luis Fabiano', 2 UNION ALL SELECT 'Diego Forln', 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 Windows 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.

Most likely you just do not have the local SMTP service. You need to realize that SMTP is a service run by for example a MS Exchange server, so unless your machine is a Server type of OS and you have it installed and configured you cannot reference the local SMTP in your package.Arthur My Blog