How to use an SQL Configuration Table with Xtract IS

SQL Server Integration Services (SSIS) provides package configurations that you can use to update the values of properties at run time.

In this blog I will show how to store your package configuration in an SQL table. In my sample I will use Xtract IS to load the SAP table T001 for company codes using a where statement. The goal is to use the SQL configuration table to update the value of the where statement and the value of the SAP connection string.

Step 1: Create a Data Flow

Create a Data Flow Task with an Xtract Table component as a source and an OLE DB Destination.

In my sample I selected the Table T001 and set the where clause to an SSIS variable @WhereStatement .

Then I created an SSIS Variable with the same name of type string and set the value to LAND1 = ‘US’ to filter the country. A click on the Preview button shows the result.

Step 2: Create the Package Configuration

In Visual Studio 2010 or 2012 go the Package properties windows and click on the ellipsis of the configuration options to open the Package Configurations Organizer. In older Visual Studio versions you can enable the package configuration by choosing the Package Configurations option on the SSIS menu.

Check the box next to the Enable Package Configurations option.

From the Package Configuration Organizer, click on Add to launch the Package Configuration Wizard to create a new configuration.

Select the Configuration type: SQL Server. This configuration type stores the configuration information in an SQL Server table (see links below for further information).

Set the connection to the SQL Server and use the Package Configuration Wizard to create the configuration table.

The next page in the configuration wizard lets you choose the set of properties to be targeted by the configuration being created.

Select the property ConnectionString of the SAP connection (optional).

And select the property Value of the SAP Variable WhereStatement.

Click on Next, then on Finish to complete the configuration.

The configuration wizard created the configuration table and inserted the required rows.

Now go the Configuration table and change the ConfiguredValue field of the WhereStatement Variable to LAND1 = 'DE'

Step 3: Run the Data Flow Task

Now execute the Data Flow task in SSIS and check the output that the variable has been updated from the SQL Configuration table.

Links:

Online Help Xtract IS

Understanding Integration Services Package Configurations