Readme_Execute SQL Statements in a Loop Package Sample

08/06/2008 01:27:32

This sample works only with SQL Server 2005 and SQL Server 2008. It will not work with any version of SQL Server earlier than SQL Server 2005.
This sample works with the the SQL Server 2005 version of AdventureWorks OLTP database. To install this database, see
Sample Databases for Microsoft SQL Server 2008.
The Execute SQL Statements in a Loop sample package takes customer data, cleans and distributes this data, and finally loads the data into the appropriate tables in the
AdventureWorks database. To complete this process, the Execute SQL Statements in a Loop sample package uses these Integration Services components:

A Foreach Loop container that uses the Foreach File enumerator to iterate through a set of text files that each contain a single Transact-SQL statement. Each Transact-SQL statement creates one of five tables in the
AdventureWorks database. Each table corresponds to one of the valid sales territories in the United States. The collection value of the Foreach File enumerator maps to a variable. This variable is used in a property expression to update the value of
the connection string in a File connection manager, CreateTableSQL, with each enumeration.

A Data Flow task that first detects and corrects incorrect postal codes. (The data in the text file has several problems: some postal codes were incorrectly interpreted as numeric values and therefore are missing their leading zeros, and the identifiers
for some sales territories are not valid.) The data flow task then directs valid data rows to one of five tables in the
AdventureWorks database and writes data rows that do not have a valid sales territory to a separate file.

A second Foreach Loop container that uses the Foreach File enumerator to iterate through a set of text files that each contain a Transact-SQL statement. Each Transact-SQL statement queries and returns the row count for a particular sales territory table.
The collection value of the Foreach File enumerator maps to a variable. This variable is used in a property expression to update the value of the connection string in a File connection manager, SelectCountSQL, with each enumeration. When the package is rerun,
the tables are truncated before the data is inserted.

Note:

To avoid validation errors when you configure the File connection managers, CreateTableSQL and SelectCountSQL, existing files have already been selected in the
File Connection Manager Editor.

If you run the sample on a non-English version of Windows, you may have to substitute the localized name of the Program Files folder to open or run the sample.

Important:

Samples are provided for educational purposes only. They are not intended to be used in a production environment and have not been tested in a production environment. Microsoft does not provide technical support for these samples.

Requirements

Before you run this sample package, ensure that the following conditions are met:

The sample package and data files that it uses must be installed on the local hard disk drive.

You must have installed and have administrative permissions on the AdventureWorks OLTP database.

If you intend only to run the sample package from the command line, you must install Integration Services.

If you intend to open the package in SSIS Designer and run the sample package, you must install Business Intelligence Development Studio. For more information about how to install samples, see the topic, "Installing Sample Integration Services Packages",
in SQL Server Books Online.

Location of the Sample Package

If the samples were installed to the default installation location, the Execute SQL Statements Sample package is located in the following folder:
C:\Program Files\Microsoft SQL Server\100\Samples\Integration Services\Package Samples\ExecuteSQLStatementsInLoop Sample\ExecuteSQLStatementsInLoop\
The following files are required to run this sample package.

File

Description

ExecuteSQLStatementsInLoop.dtsx

The package file.

Customer.txt

The source data file.

CustomersWithInvalidTerritoryID.txt

The file to which data that is not valid is written.

CreateProspectTableTerr1.sql

The Transact-SQL statement that creates the Territory1 table.

CreateProspectTableTerr2.sql

The Transact-SQL statement that creates the Territory2 table.

CreateProspectTableTerr3.sql

The Transact-SQL statement that creates the Territory3 table.

CreateProspectTableTerr4.sql

The Transact-SQL statement that creates the Territory4 table.

CreateProspectTableTerr5.sql

The Transact-SQL statement that creates the Territory5 table.

Select ProspectTableTerr1.sql

The Transact-SQL statement that returns the row count for Territory1 table.

Select ProspectTableTerr2.sql

The Transact-SQL statement that returns the row count for Territory2 table.

Select ProspectTableTerr3.sql

The Transact-SQL statement that returns the row count for Territory3 table.

Select ProspectTableTerr4.sql

The Transact-SQL statement that returns the row count for Territory4 table.

Select ProspectTableTerr5.sql

The Transact-SQL statement that returns the row count for Territory5 table.

Running the Sample

The package can be run from the command line by using the dtexec utility, or can be run in Business Intelligence Development Studio.
If you are using a non-English version of Windows, or if you have installed the samples to a non-default location, you may have to update the
ConnectionString property of any file connection managers used in the package to run the sample package successfully. You should verify that the path used in the connection manager is valid on your computer, and if you need to, modify the path so that
it uses the correct path to the sample files.
For this sample, you may have to update "Program Files" in the ConnectionString property for the Customers and CustomersWithInvalidTerritoryID connection managers.

To run the package by using dtexec

Open a Command Prompt window.

Use the Change Directory command, cd, to change the directory to C:\Program Files\Microsoft SQL Server\100\DTS\Binn, the location of
dtexec.

Press Enter.For more information about how to run the package by using the
dtexec utility, see the topic, "dtexec Utility", in SQL Server Books Online.

To run the package in Business Intelligence Development Studio

Open Business Intelligence Development Studio.

On the File menu, point to Open, click Project/Solution.

Locate the ExecuteSQLStatementsInLoop Sample folder, and then double-click the file named ExecuteSQLStatementsInLoop.sln.

In Solution Explorer, right-click ExecuteSQLStatementsInLoop.dtsx in the
SSIS Packages folder, and then click Execute Package.

Important:

If you open the Execute SQL Statements in a Loop sample package in SSIS Designer before you run the package for the first time, the Data Flow task includes a warning. The warning occurs because the SQL Server tables that the package uses do not exist yet;
the tables are created the first time that you run the package. The warning does not appear when you reopen the package in SSIS Designer, if you have run the package at least one time. The package runs successfully regardless of the warning.

Components in Sample

The following table lists the Integration Services tasks, containers, sources, transformations, destinations, and connection managers that are used in the sample.

Element

Purpose

Foreach Loop

The Foreach Loop container, Run SQL Statements, uses the Foreach File enumerator to iterate through files that contain Transact-SQL statements. The Foreach Loop container includes an Execute SQL task.

Execute SQL task

The Execute SQL task, Create Tables, connects to the AdventureWorks database. The task then uses the File connection manager,
CreateTableSQL, to access the files that contain the Transact-SQL statements that create the tables. Finally, the task runs the Transact-SQL statements and creates the tables in the database.

Data Flow task

The Data Flow task, Extract-Clean-Load Data, executes a data flow that extracts the data from a text file, cleans and distributes the data, and then loads the data into tables in the
AdventureWorks database.

Flat File source

The Flat File source, Extract data, extracts the data from the text file.

Conditional Split transformation

The Conditional Split transformation, Direct Rows by TerritoryID, directs rows to different outputs depending on the value in the
TerritoryID column. Rows with invalid values in TerritoryID are directed to the default output.

Derived Column transformation

The Derived Column transformation, Fix Postal Code, cleans the postal codes by adding a leading zero to four-character postal codes.

OLE DB Destination

Five OLE DB destinations load data into five different territory tables.

Foreach Loop container

The Foreach Loop container, Run SQL Select Statements, uses the Foreach File enumerator to iterate through files that contain the Transact-SQL statements that returns the row count for each table. The container has an Execute SQL task,
Select Table Count, and a Script task, Display Table Count.

Execute SQL task

The Execute SQL task, Select Table Count, connects to the AdventureWorks database. The task then uses the File connection manager,
SelectCountSQL, to access the files that contain the Transact-SQL statements that return the row count for each table. The task runs the Transact-SQL statements and stores the row count, as a
Single row result set, in a package variable.

Script task

The Script task, Display Table Count, displays the row count for each of the five tables.

The File connection manager, Customers, connects to the text file that contains the customer source data.The File connection manager,
CustomsWithInvalidTerritoryId, connects to the text file to which the package writes invalid data.

OLE DB connection manager

The OLE DB connection manager, (local).AdventureWorks, connects to the
AdventureWorks database on the local server.

Sample Results

To see the execution results of the Execute SQL Statements in a Loop sample package, open a Query window in SQL Server Management Studio, type or copy the following Transact-SQL query, and then run the query.