Capture Data Lineage Package Sample

The Capture Data Lineage sample is a package that captures audit information. When the package is run, it loads five identically configured files, adds columns of historical information, such as the file names, by using the Audit transformation, and then loads the data into a table in the AdventureWorks database by using an OLE DB destination. The table, Lineage_Fact_Table, is created by the Execute SQL task and the task truncates the table every time that the package runs.

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.

Requirements

Running this sample package requires the following:

• 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 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 2005 Books Online. To obtain the latest version of the samples, including new samples released after 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 Capture Data Lineage sample package, CaptureDataLineage.dtsx, is located in the following folder:

C:\Program Files\Microsoft SQL Server\90\Samples\Integration Services\Package Samples\CaptureDataLineage Sample\Capture Data Lineage\

The following files are required to run this sample package.

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. 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 localized name of the Program Files folder.

For this sample, you may have to update "Program Files" in the ConnectionString property for the Sample Data 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\CaptureDataLineage Sample\CaptureDataLineage\CaptureDataLineage.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 click Project/Solution.

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

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

Components in Sample

The following table lists the Integration Services tasks, containers, data adapters, and transformations that are used within the sample.

Element

Purpose

Execute SQL task

The Execute SQL task, Create Lineage_Fact_Table, runs an SQL statement that creates the Lineage_Fact_Table table the first time that you run the package, and then truncates the table when you rerun the package.

Data Flow task

The Data Flow task, Get Data Lineage Information, executes the data flow in the package.

Flat File source

The Flat File source, Extract Data from Files, loads the flat file source data and adds a column for the file name to each output row.

Audit transformation

The Audit transformation, Add Data Lineage Information, adds two new columns for lineage information to each output row. The columns contain user name and start time.

Note:

The default length of the column for the user name is 64 characters. If your organization might have user names that exceed 64 characters, you must update the column length by using the Advanced Editor dialog box.

OLE DB destination

The OLE DB destination, Load Data into Lineage_Fact_Table, loads the results to the Lineage_Fact_Table in the AdventureWorks database.

Multiple Flat Files connection manager

The Multiple Flat Files connection manager, Sample Data, connects to files that have the .txt extension.

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 Capture Data Lineage sample package, run the following Transact-SQL query:

Select * from AdventureWorks.dbo.Lineage_Fact_Table

In these results, you will see the columns populated with the data retrieved from the flat files, with the addition of generated lineage information in the File Name, User Name, and Execution Start Time columns.