Hands-On Microsoft SQL Server 2008 Integration Services part 15. Deploy and manage high-performance data transformation solutions across your enterprise using the step-by-step techniques in this fully revised guide. Hands-On Microsoft SQL Server 2008 Integration Services, Second Edition explains the tools and methods necessary to extract conclusive business intelligence from disparate corporate data. Learn how to build and secure packages, load and cleanse data, establish workflow, and optimize performance. Real-world examples, detailed illustrations, and hands-on exercises are included throughout this practical resource. . | 118 Hands-On Microsoft SQL Server 2008 Integration Services 11. In your package you will store the result set to a variable and then use this variable in the Foreach Loop Container to let it iterate over the resultant records. The result set records will be held in a variable of the Object data type as they can hold a dataset and will be read by the Foreach Loop Container one by one and fed into the defined tasks for further processing. 12. From the left pane of the Execute SQL Task Editor click the Result Set page. 13. In the Result Set page click Add. NewResultName will appear under the Result Name column. Delete it and type 0 zero in this field. Click in the field under the Variable Name column. Click the drop-down arrow to select New variable. . In the Add Variable window in the Name field type Opportunities leave the Namespace field set at User and in the Value type field select Object as shown in Figure 4-4. Variables are case-sensitive so when you type to add or to select a variable pay attention to the case of variable. Click OK to return to the Task Editor window. You will see User Opportunities added as a user variable under the Variable Name column. 14. Click OK to close Execute SQL Task Editor that will select the prospects at run time. Figure 4-4 Creating a user variable Chapter 4 Integration Services Control Flow Containers 119 Exercise Adding Foreach Loop Container Here you will configure the Foreach Loop Container using the Foreach ADO enumerator that allows you to enumerate over records from a record set as provided by Opportunities variable. 15. From the Toolbox drag and drop a Foreach Loop Container below the Prospects task. Now click on the Prospects and drag the green arrow appearing below it onto the Foreach Loop Container. This is the precedence control letting the Foreach Loop Container to proceed if the previous Prospects task has completed successfully. 16. Double-click the icon of the Foreach Loop Container to open the editor. In the .
