Configuration - SQL Server Integration Services Design Patterns - page 425

You will retrieve the list of files you need to process from the PackageConfigura-
tion table you created using an Execute SQL task. You will store the result set in a
package variable, and then loop through each row with a Foreach loop container. You
will use the processed field to mark the files that have already been processed—you
will set the processed value to True once you have successfully loaded the file.
Note This example assumes that all of the flat files listed in the PackageConfig-
uration table have the same schema. It does not cover the logic needed to actually
load the flat file into the database—it is meant to illustrate the pattern that you'd use as
a template for processing a number of items in a loop.
Setting up the package takes the following steps:
1. Add four package variables.
• FileID (Int32): The row ID for the file you are currently process-
ing
• Directory (String): The directory containing the flat file you need
to process
• FileName (String): The name of the file you are processing
• FilesToProcess (Object): The result set of the Execute SQL task
2. Add an Execute SQL task to your package; name it Retrieve File
List .
3. Double-click the task to open its editor.
4. Ensure the ConnectionType is OLE DB .
5. Click on the Connection dropdown and select New connection....
6. Click New and configure the connection manager to point to the data-
base containing the PackageConfiguration table.
7. Select all of the files that have not been processed from the Pack-
ageConfiguration table (as shown in Listing 17-6 ) .
Listing 17-6 . Query to Pull Out All Entries in the Configuration Table
That Have NotBeen Processed Yet