Walkthrough: Creating and Running a Data Generation Plan

Article
08/23/2010

In this walkthrough, you create a data generation plan and run it to fill a test database with randomly generated data. First you create a database project and deploy it to an isolated test database. You can use the isolated database to test data generation without touching your production data or database.

Prerequisites

To complete this walkthrough, you need the following:

Visual Studio Team System Database Edition
SQL Server 2005 or SQL Server 2000
Access to a database server that has the Northwind database installed

Creating the Database Project

First you create a database project and import the schema from the Northwind database.

Note

In a team environment, you might check an existing project out of the source code control system to work on a database project. For more information, see Starting Team Database Development.

To create a database project

On the File menu, point to New, and then click Project.

The New Project dialog box appears.
In the Project Types list, expand the Database Projects node, and click SQL Server 2005 or SQL Server 2000.
In the Templates list, click SQL Server 2005 Database Project or SQL Server 2000 Database Project.
In Name, type WalkthroughDataGenerator, and click OK.

A solution is created that contains the WalkthroughDataGenerator empty project. This is your database project. No one else has access to your database project when you work on it.
In either Solution Explorer or Schema View, click WalkthroughDataGenerator.
On the Project menu, click Import Database Objects and Settings.

Note

You can also right-click WalkthroughDataGenerator and then click Import Database Objects and Settings in either Solution Explorer or Schema View.

The Import Database Wizard dialog box appears.

In the Source Database Connection list, click the connection that corresponds to your existing Northwind database.

Important Note:
If you have not yet connected to that database, you must first click New Connection to create a connection to it. For more information, see How to: Create a Database Connection.

Click Start.

As the schema is imported, project items that correspond to the objects in the database appear under the database project in Solution Explorer and Schema View.

Note

Even though you connected to the database to import the schema, you are now disconnected and working offline.
When the schema is imported, click Finish.

Deploying to a Sandbox

Next you deploy the project to a new database. This creates a database that has the Northwind schema, but without the data. This database is an isolated environment (or sandbox) in which you can develop and test the database.

To build the database project

In Solution Explorer, click the database project WalkthroughDataGenerator.
On the Project menu click WalkthroughDataGenerator Properties.

The project properties appear.
Click the Deploy tab.
In the Deploy Action list, click Create a deployment script (.sql) and deploy to database.
In Target Database Settings, click Edit to display the Connection Properties dialog box.
Set the connection properties for the database where you want to work, and then click OK.

The Target Connection box is filled with the correct connection string.

Warning

You should create the new database on a test server, a development server, or on your local computer. You should not use your production server.
In the Target database name text box, type NorthwindDataGenerator.
On the File menu, click Save All.
On the Build menu, click Build Solution.

The deployment script is built based on the project properties you have just set. You see the status of the build in the Output window, and you should see Build: 1 succeeded or up-to-date as the last line.

To deploy the database project

In Solution Explorer, click the database project WalkthroughDataGenerator.
On the Build menu, click Deploy ProjectName. You can also right-click the project in Solution Explorer and click Deploy.

Warning

You should run this deployment against a test server, a development server, or your local computer. You should not use your production server.

The database project is deployed to a new database. You see the status of the deployment in the Output window, and you should see Deployment succeeded as the last line.

Creating the Data Generation Plan

Next you create the data generation plan. The data generation plan contains the information about which tables and columns you want to fill with data. For more information, see How to: Create Data Generation Plans.

To create the data generation plan

In Solution Explorer, select the Data Generation Plans node.
On the Project menu, click Add New Item.

The Add New Item dialog box appears.
In the Categories pane, click Data Generation Plans.
In the Templates pane, click Data Generation Plan.
In the Name text box, type Products.dgen.
Click Add.

The data generation plan is created. The data generation plan and the Data Generation Preview window appear. The data generation plan window is divided horizontally into two panes. The upper pane lists the tables that are defined in the database project schema. The lower pane displays column details for the table that is highlighted in the upper pane.

Note

If the Data Generation Preview window is not open, you can open it by opening the Data menu, pointing to Data Generation, and then clicking the window name. By default, the Data Generation Preview window is docked and tabbed at the bottom of the data generation plan window. To expand your view, click the window, and then click Tabbed Document on the Window menu. You can also right-click the title bar and then click Tabbed Document.
In the Products.dgen designer, clear the check boxes for all the tables.

Note

You can select or clear the check boxes for all the tables by using the Include All Tables In Data Generation and Exclude All Tables From Data Generation commands. You can access these commands by right-clicking any row in the data generation plan window or, on the Data menu, pointing to Data Generator.
In the Products.dgen designer, select the check box for the Products table.

The check boxes for the Categories and Suppliers tables are automatically selected also. Because the Products table has foreign keys to the Categories and Suppliers tables, you must fill the other two tables to fill the Products table. For more information, see How to: Specify Tables for Data Generation.
On the File menu, click Save All.

Specifying Details for Data Generation

Next, you specify the details of how you want to fill columns with data. For more information, see Specify Details of Data Generation for a Column.

To specify details for data generation

Set the number of rows of data to generate by doing the following:
1. In the Products.dgen designer, click the row for the Products table.
2. Click the Related Table column and use the drop-down arrow to select the Categories table.
3. Click the Ratio to Related Table column and type 10:1.
This means that you will generate 10 products for each 1 category that is generated. For more information, see How to: Specify the Number of Rows to Generate.
Set the number of NULL rows of data to generate by doing the following:
1. In the Products.dgen designer, click the row for the Categories table.
2. In the column-details pane, click the row for the Description column.
3. In the Properties window, set the Percentage Null property to 10.
This means that 10% of the data generated in the Description column will contain NULL. You can open the Data Generation Preview window and verify that the Description column contains some null values.
Set the text of the data that is generated by doing the following:
1. In the Products.dgen designer, click the row for the Products table.
2. In the column-details pane, click the row for the ProductName column.
3. Click the Generator column, and use the drop-down arrow to specify the RegularExpression data generator.
4. In the Properties window, set the Expression property to the following:
```
(Gala|Fuji|Braeburn|Granny Smith|Red Delicious) Apples
```
The data that is generated in the ProductName column will contain only the name of a type of apple. You can open the Data Generation Preview window and verify that the ProductName column contains randomly generated apple names. For more information, see The Regular Expression Generator.
On the File menu, click Save All.

Running the Plan to Generate Data

Finally you run the data generation plan. After you generate the data, you can use a different tool to log on to the database and verify the new data.

To run the plan to generate data

In Solution Explorer, click Products.dgen.

Note

The data generation plan must also be open. If the plan is not open, open it first.
On the Data menu, point to Data Generator and then click Generate Data.

The Connect to Database dialog box appears.
In the Data Generation Connection Information list, click the NorthwindDataGenerator database, and then click OK.
Click Yes when you are prompted to clear the contents of the tables before inserting new rows.

The data is generated. In the Population window, the status column is updated with the status of the data generation. The status bar summarizes the data generation for all the tables.
(Optional) Use a different tool to log on to the database. You can use the Transact-SQL (T-SQL) editor that is provided in Database Edition for this step. For more information, see Editing Database Scripts and Objects with the Transact-SQL Editor. View the new data by running the following query:
```
use NorthwindDataGenerator

select * from Suppliers
select * from Categories
select * from Products
```
Verify that 10 times as many rows were generated for the Products table as for the Categories table. Verify that the Description column in the Categories table contains NULL values. Verify that the ProductName column in the Products table contains only data of the form "<type> Apples". Verify that the types are the types that you specified in the Expression property of the RegularExpression generator.

Share via