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. | RequirementsBefore you run this sample package, ensure that the following conditions are met:
Location of the Sample PackageIf 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. |
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. |
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 and runs five Transact-SQL statements that create tables. |
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. |
File connection manager | The File connection manager, Create Tables SQL, connects to files that contain SQL CREATE TABLE statements. |
Flat File connection managers | 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. |
SELECT * FROM AdventureWorks.dbo.Territory1 SELECT * FROM AdventureWorks.dbo.Territory2 SELECT * FROM AdventureWorks.dbo.Territory3 SELECT * FROM AdventureWorks.dbo.Territory4 SELECT * FROM AdventureWorks.dbo.Territory5 | This query retrieves the data that was extracted from the text file and loaded into SQL Server tables. © 2007 Microsoft Corporation. All rights reserved. |