In today’s blog post, I am going to show you how to debug ForEach Loop Container variable.
Let’s say you fetched a data list from table and want to loop this list to do other tasks item by item. But you want to debug the item value before run other task.
First, Create two variables for Foreach Loop Container:
In the control flow of an SSIS package, add an Execute SQL Task and open the editor. Set the ‘Result Set‘ property to Full result set. Create an OLE DB connection to the database. Add the following T-SQL statement:
SELECT URL as BookListURL,Id as BookCategoryId,PreviousProcessedPageNumber as BookPreviousProcessedPageNumber
FROM URL_BookList
Click the ‘Result Set‘ option to the left. Set the Result Name to 0 and for the Variable Name, select User::BookListURLs. This variable will hold the results returned by the query. Click OK to close the editor.
Next, add a ForEach Loop container and connect the Execute SQL task to it. Open the editor and click Collection. In the enumerator drop down menu, select ForEach ADO Enumerator. In the ADO Object Source Variable drop down menu, select BookListURLs and for the Enumeration Mode, select Rows in the first table.
Set up Variable Mappings then click OK to close the editor:
To debug looping item value, you need to add a Breakpoint:
Now, execute the package. The package will stop and a watch window should open at the bottom of the screen. If it doesn’t, click Debug > Windows > Watch 1. In the watch window, add the names of the variables you want to monitor. Keep in mind that the variable names are case sensitive here.
