Configuration - SQL Server Integration Services Design Patterns - page 424

name from a database, storing the values in variables, and then using them to dynamic-
ally set the ConnectionString for a flat file connection manager.
Creating the Database Table
Listing 17-5 shows the SQL for the table that you will be reading your configuration
values from. Each row in the table is a new flat file that you will want to process with
this package. The two main columns you are interested in are directory and
name —the id column is a surrogate key to uniquely identify each row in the table,
and the processed column lets us easily filter out files that have already been pro-
cessed. Sample values are shown in Table 17-3 .
Listing 17-5 . SQL Definition of the Table Our Package Will Read Its Configuration Val-
ues From
CREATE TABLE [dbo].[PackageConfiguration]
(
[id] int IDENTITY(1,1) NOT NULL,
[directory] nvarchar(255) NOT NULL,
[name] nvarchar(255) NOT NULL,
[processed] bit NOT NULL
)
Table 17-3 . Sample Rows from the PackageConfiguration Table
Retrieving Configuration Values with an Execute SQL
Task