Run the BIT Package B Components

Article
08/18/2014

This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.

The Business Intelligence Tools (BIT) in Speech Server are samples that illustrate how to build a data warehouse of large volumes of Speech Server data and Online Analytical Processing (OLAP) cubes for reporting on the data.

Note??????The source and target databases must include a valid schema prior to running BIT. If a valid schema is not present, the following error can occur: "An error occurred during data filtering - Invalid object name dbo.Dates." For information about creating a valid tuning database, see Tuning Database.

Running the BIT Package B Components

The BIT Package B components consist of two core Microsoft SQL??Server Integration Services (SSIS) packages:

PackageB_DW.dtsx. This package is used to create and prepare the data warehouse before first use. The package creates the partitions on the target database and the schema for the data.
PackageB_DM.dtsx. This package is used to migrate data from a Speech Server source database into the data warehouse created by PackageB_DW.dtsx.

BIT Package B packages can be run with:

SQL Server Business Intelligence Development Studio.
DTExec.exe.
DTExecUI.exe.
The SQL Server Agent service to schedule the run time.

In all cases, the packages must be run on the computer holding the target database. It is not possible to run the packages on a remote computer.

Configuring the Connection

In all cases, a configuration file must be supplied and located in the same directory as the package. Sample configuration files, PackageB_DW.xml and PackageB_DM.xml, are provided with the installation. Open each configuration file in a text or XML editor and then edit the values in the connection string to apply to the source and target servers and databases in your environment. For example, [insert source server name here] should be replaced with the name of the source server and [insert source database name here] should be replaced with the name of the source database.

Important Security Notice

Storing SQL??Server credentials in the BIT Package B configuration file is a security risk. BIT Package B users should use Windows Authentication to connect to the SQL??Server database. Windows Authentication is the default and preferred means of connecting to the database.

When SQL??Server credentials are required to connect, users are strongly advised not to store their credentials in the BIT Package B configuration file. If credentials are stored in the configuration file, users should implement appropriate security measures, such as setting the access control list or using the Encrypting File System feature, to prevent unauthorized access to the configuration file.

Time-outs

By default, the remote connection time-out on the source server running SQL??Server is likely set to 600 seconds. This might not be long enough for large migrations or for migrations over a slow network. In such cases, the remote connection time-out on the source server should be disabled by setting the time-out to zero or setting the time-out to a higher value.

Using Business Intelligence Development Studio

Business Intelligence Development Studio offers an environment similar to Microsoft Visual Studio 2005 to run and debug BIT Package B. In Business Intelligence Development Studio, open the PackageB.sln solution to run and debug BIT Package B.

For more information about using Business Intelligence Development Studio to run BIT Package B, see SQL Server Management Studio and Business Intelligence Development Studio.

Using the DTExec.exe Utility

The following sample command line is for running BIT Package B with DTExec.exe:

DTExec.exe /f PackageB_DW.dtsx /config PackageB_DW.xml [> BITPkgB_DW.log]

You can rename the optional output log file titled BITPkgB_DW.log. This log file is useful for tracking the progress of the migration, including timing issues and errors or exceptions.

DTExec.exe can also be used within batch or script files. For more information about using DTExec, see dtexec Utility.

Using the DTExecUI.exe Utility

While it is possible to use the DTExecUI.exe utility, it is not recommended. For more information about using DTExecUI.exe, see How to: Run a Package Using the DTExecUI Utility.

Using SQL??Server Agent to Schedule the Package

SQL??Server Agent can be used to manage data migrations. Use SQL??Server Management Studio to access the agent and schedule the run time. For more information, see Automating Administrative Tasks (SQL Server Agent).