SMO Tables DBCC Package Sample

  • Article

The SMOTablesDBCC sample package illustrates how to enumerate the user tables in the AdventureWorks database by using the Foreach SMO enumerator in a Foreach Loop container. First, a Script task retrieves the schema and table names and puts them into two variables. Next, an Execute SQL task executes a DBCC CHECKCONSTRAINTS command using the two variables.

Note

To allow the package to run in a 64-bit environment the PreCompile property of the Script task is set to True. If you want the step through the script by using debugging tools, you should set the property to False.

For more information, see the topics, "Foreach Loop Container", "Script Task", and "Execute SQL Task" in SQL Server 2005 Books Online.

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

Running this sample package requires the following:

  • Locate the following files and copy them under %windir%\Microsoft.NET\Framework\v2.0.xxxxx (where xxxxx is the highest version of your .NET Framework):
    • %ProgramFiles%\Microsoft SQL Server\90\SDK\Assemblies\Microsoft.SqlServer.Smo.dll
    • %ProgramFiles%\Microsoft SQL Server\90\SDK\Assemblies\Microsoft.SqlServer.SmoEnum.dll
    • %ProgramFiles%\Microsoft SQL Server\90\SDK\Assemblies\Microsoft.SqlServer.ConnectionInfo.dll
  • The sample package and data files that it uses must be installed on your 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 the topic, "Installing Sample Integration Services Packages", in SQL Server 2005 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 SMOTablesDBCC sample package is located in the following folder:

C:\Program Files\Microsoft SQL Server\90\Samples\Integration Services\Package Samples\SmoTablesDBCC\SmoTablesDBCC\

The following files are required to run this sample package.

File Description

SmoTablesDBCC.dtsx

The 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.

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\SmoTablesDBCC\SmoTablesDBCC\SmoTablesDBCC.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 SmoTablesDBCC folder, and then double-click the file named SmoTablesDBCC.sln.

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

Components in Sample

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

Element Purpose

Foreach Loop

The Foreach Loop container, Foreach Loop Container, uses the Foreach SMO enumerator to iterate through the tables in the AdventureWorks database. The Foreach Loop container includes a Script and an Execute SQL task.

Script task

The Script task updates the values of variables with the names of the schema and table on each iteration of the loop.

Execute SQL task

The Execute SQL task, DBCC CHECK CONSTRAINTS, runs a DBCC command on each iteration of the loop.

OLE DB connection manager

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

Sample Results

After you execute the package, you should see output similar to the following output.

Sample Output:

Started: 10:41:04 AM

Progress: 2005-03-15 10:41:06.04

Source: DBCC CHECK CONSTRAINTS

Executing query "DBCC CHECKCONSTRAINTS('[dbo].[AWBuildVersion]') WI".: 100% complete

End Progress

Progress: 2005-03-15 10:41:06.07

Source: DBCC CHECK CONSTRAINTS

Executing query "DBCC CHECKCONSTRAINTS('[dbo].[DatabaseLog]') WITH ".: 100% complete

End Progress

…

Progress: 2005-03-15 10:38:16.81

Source: DBCC CHECK CONSTRAINTS

Executing query "DBCC CHECKCONSTRAINTS('[Sales].[Store]') WITH ALL_".: 100% complete

End Progress

Progress: 2005-03-15 10:38:16.91

Source: DBCC CHECK CONSTRAINTS

Executing query "DBCC CHECKCONSTRAINTS('[Sales].[StoreContact]') WI".: 100% complete

End Progress

DTExec: The package execution returned DTSER_SUCCESS (0).

Started: 10:37:25 AM

Finished: 10:38:17 AM

Elapsed: 51.687 seconds