Data Cleaning Package Sample

The Data Cleaning sample is a package that cleans data. The package uses data that is a list of names and addresses that represent potential customers. The data requires cleaning; it contains spelling errors, is missing information, and includes customers already in the database, incorrect customers, or multiple subtly different instances of the same customer.

The package control flow consists of two tasks. The first is an Execute SQL task that creates the input table, CustomerLeads, and creates the three output tables named ExistingCustomerLeads, NewCustomerLeads, and DuplicateCustomerLeads. The second task is a Data Flow data flow that executes the data flow that performs the cleaning of data extracted from the CustomerLeads table. The data flow identifies unique new, existing, and duplicate customers, and writes the rows of each customer type to the appropriate output table.

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.

To learn more about data cleaning, search for the following articles in the MSDN Library at https://msdn.microsoft.com/library.

• Data Cleansing Applications with SQL Server Integration Services (Windows Media Video)
• Data Cleaning using the Fuzzy Grouping and Fuzzy Lookup Transformations (white paper)

Requirements

Running this sample package requires the following:

• You must have installed and have administrative permissions on the AdventureWorks database.
• If you intend only to run the sample package from the command line, you must install SQL Server 2005 Integration Services (SSIS).
• 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 "Installing Sample Integration Services Packages" in SQL Server Books Online. To obtain the latest version of the samples, including new samples released since the original release of SQL Server 2005, see SQL Server 2005 Samples and Sample Databases (April 2006).

Location of the Sample Package

If the samples were installed to the default installation location, the Data Cleaning package is located in the following folder:

C:\Program Files\Microsoft SQL Server\90\Samples\Integration Services\Package Samples\DataCleaning Sample\Data Cleaning\.

The following files are required to run this sample package.

Adding Data Viewers to the Sample

To better understand how the Data Cleaning package works, you can add data viewers to the data flow and then view the data as it moves between data flow components. We recommend that you add data viewers to the following paths:

• Path from Union All to OLE DB Destination-Existing Customers
• Path from Conditional Split on Canonical Record for Group to OLE DB Destination-Unique Customer Leads
• Path from Conditional Split on Canonical Record for Group to OLE DB Destination-Duplicate Customer Leads

To add data viewers

1. Right-click the path and then click Data Viewers.

2. In the Data Flow Path Editor, click Add.

3. In the Configure Data Viewer dialog box, click Grid in the type list. By default, all columns display in the data viewer.

4. Repeat steps 1-3 for other paths.

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, you may have to update the ConnectionString property of any file connection managers used in the package to run the sample package successfully. Verify that the path used in the connection manager is valid on your computer, and if required, modify the path so that it uses the localized name of the Program Files folder.

For this sample, you may have to update "Program Files" in the ConnectionString property for the CreateTables.sql connection manager.

To run the package by using dtexec

1. Open a Command Prompt window.

2. Change the directory to C:\Program Files\Microsoft SQL Server\90\DTS\Binn, the location of dtexec.

3. Type the following command:

   dtexec /f "C:\Program Files\Microsoft SQL Server\90\Samples\Integration Services\Package Samples\Data Cleaning Sample\DataCleaning\DataCleaning.dtsx"
   

4. Press Enter.

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

To run the package in Business Intelligence Development Studio

1. Open Business Intelligence Development Studio.

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

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

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

Components in Sample

The following table lists the tasks, containers, data sources and destinations, and transformations that are used within the sample.

The following table describes the data in the output tables.

Sample Results

To see the execution results of the Data Cleaning sample package, run the following Transact-SQL query:

Select * from AdventureWorks.FuzzyLookupExample.ExistingCustomerLeads
Select * from AdventureWorks.FuzzyLookupExample.NewCustomerLeads
Select * from AdventureWorks.FuzzyLookupExample.DuplicateCustomerLeads

Change History