Configuration - SQL Server Integration Services Design Patterns - page 426

SELECT * FROM [dbo].[PackageConfiguration]
WHERE [processed] = 0
8. Set the ResultSet value to Full Result Set. This means that the Ex-
ecute SQL task will retrieve the values as an ADO Recordset that can
be processed by the Foreach loop. Note that you could also use an
ADO.NET connection manager here, which would cause the results
to be returned as an ADO.NET data table.
9. Click on the Result Set tab.
10. Click Add, and use these mappings:
a. Result Name: 0
b. Variable Name: User::FilesToProcess
11. Click OK to save the changes to the Execute SQL task.
12. Add a Foreach loop container to your package.
13. Connect the Execute SQL task to the Foreach loop container.
14. Add a Data Flow task inside of the Foreach loop container.
15. Add a new Execute SQL task inside of the Foreach loop container.
16. Connect the Data Flow task to the Execute SQL task.
17. Double-click the Execute SQL task to open its editor.
18. Set the connection to the same connection manager you created in
step 5.
19. Listing 17-7 shows the SQLStatement to mark a row in the table
as processed. Note that the statement contains a parameter marker
(the question mark). You will map a variable value to this parameter
in the next step.
Listing 17-7 . SQL Statement to Mark the File as Processed
UPDATE [dbo].[PackageConfiguration] SET
[processed] = 1 WHERE id = ?
20. Click the Parameter Mapping tab.