Unit testing a database deployment with DBpro can be very valuable. You can always assess whether the new deployment of the database objects has break existing code or functionality. Also, it can be used as a proof that specific database objects have been properly deployed.

Why data driven DB unit test

One of the caveat that can arise when building database unit tests is that the result value of a specific test may be hard coded into it. Let's say that one of the tests verify that an existing contact exists in the contact table.

As shown in the picture above, you have to supply a value for the "@ManagerID" parameter. For now, its value is 0. The unit test verify that a resultset is returned by the stored procedure. So, for a specific "@ManagerID", one or more rows shoud be found in the database and returned by the stored procedure.

Now if we need to test several manager's IDs, we might then need to create as many unit tests as necessary. Or, we might have to open the unit test and change the "@ManagerID" parameter's value. This is not an efficient way to unit test our uspGetManagerEmployees stored procedure. By using data driven unit testing, a single test is created and get reused for every contact that you want to test.

Create necessary tables

The basis of data driven unit test is that for a specific test (or bunch of tests within the same test project), a table or data source is available. For the test created above, I created a schema called ADBTest and a specific table for it that I called ManagerEmployees.

This table contains the following data:

Unit test data properties

In order to bind a specific test to the ADBTest.ManagerEmployees table, we need to initialize several properties of each unit tests. In order to to it, we use the testview window in visual studio:

In the properties windows, we then select data connection string:

The data source wizard appears:

By selecting "Database" the following screen appears:

Here, I select the database where my table is located. Then, o the next screen, I choose ADBTest.ManagerEmployees table:

When the "Finish" button is pressed, the test properties windows looks like this:

Unit test code modifications

Bow that the tables where resides the unit test data is selected, we need to make slight modifications to the unit test code. By right-clicking our unit test in the solution explorer and select "VIew code", the code of the unit test class appears. The following modifications have to be made for the row count test:

public void dbo_uspGetManagerEmployeesRowCnt()
       {
           DatabaseTestActions testActions = this.dbo_uspGetManagerEmployeesRowCntData;
           // Execute the pre-test script
           //

          //Set the rowcount condition with the database value
           RowCountCondition cond1 =
           (RowCountCondition)testActions.TestAction.Conditions[0];
           cond1.RowCount =
           Convert.ToInt32(TestContext.DataRow["EmployeeRowCount"].ToString());
           //Set the manager parameter
           DbParameter p = this.ExecutionContext.Provider.CreateParameter();
           p.Direction = ParameterDirection.Input;
           p.ParameterName = "@ManagerID";
           p.Value = TestContext.DataRow["ManagerID"];
           System.Diagnostics.Trace.WriteLine("Row count test for : " + TestContext.DataRow["ManagerFirstName"] + " " + TestContext.DataRow["ManagerLastName"]);

…

The first thing we need to do is to create a condition for our test: cond1. This variable will hold the row count value that we want to compare with the test execution row count:

           RowCountCondition cond1 =
           (RowCountCondition)testActions.TestAction.Conditions[0];

Then, we initialize it with the EmployeeRowCount value found in the ADBTest,ManagerEmployees table:

           cond1.RowCount =
           Convert.ToInt32(TestContext.DataRow["EmployeeRowCount"].ToString());

Nest, we define a parameter: p. It will contain the parameter name as well as the manager ID:

DbParameter p = this.ExecutionContext.Provider.CreateParameter(); 
p.Direction = ParameterDirection.Input; 
p.ParameterName = "@ManagerID"; 
p.Value = TestContext.DataRow["ManagerID"];

Then,we tell the unit test class to output a line telling us which "@ManagerID" has been tested:

System.Diagnostics.Trace.WriteLine("Test for @ManagerID: " + p.Value + " (" + TestContext.DataRow["ManagerFirstName"] + " " + TestContext.DataRow["ManagerLastName"] + ")");

This way, we can see the following information in the test results window:

This tells us that the stored procedure parameter (@ManagerID) used was 158, which is the ID of Dylan Miller. This line of code enable the unit test to document itself.

The last code modification we need to make is to modify the test's execution line:

ExecutionResult[] testResults = TestService.Execute(this.ExecutionContext, this.PrivilegedContext, testActions.TestAction,p);

The parameter p has been added at the end of the command line.

With these small changes, our unit test can now execute as many time as there are data in our ADBTest.ManagerEmployee table!

Test designer modifications

The last changes we need to make to properly run the unit test is to comment out (or remove) the following code in the test designer:

As you can see in the image above, the @ManagerID as been commented out in both declaration and initialization lines. If we do not comment or remove designer<s references to the variable, the test would fail complaining that the variable is declared twice.

Running the data driven unit test

Now, we can execute the DUT. We can launch it directly from the designer or by using Visual Studio Test View to run only this unit test if you have created many in the same project.

Summary

This article demonstrated how Visual Studio for Database Professional unit tests can be driven by a data source. Doing this can add tremendous flexibility to your database deployment.

Deploying a database project to multiple environments can be challenging. Especially when all target machines are not equal; that is, their files are not stored in the same folder, the various database or server objects differ.

Visual Studio provides essential features that makes deployment works like a breeze: configurations and MSBuild.

One configuration for each environment (DEV, Test, Production)

By using Visual Studio configuration manager, you can define specific configurations for your solution. Simply right-click on your solution and select "Configuration manager" from the contextual menu.

Once Configuration Manager is displayed, you can see that two default configurations are created:
– Debug: this is the default configuration
– Release: this configuration is (or can be) used for deployment of VS projects

Configurations used for database deployment may be different than those used for software deployment. Simply create as configurations as necessary. For example, in my solutions, I always a configuration named "Development" for my own machine, "Test" for test server and "Production" for the production server.

<Add configuration manager picture>

Once configurations are defined, we can set my DBPro project accordingly.

Some files contain project's properties. They are located just below the project:

If we bring the project's properties screen (right-click project and select properties), we can create or update files as needed by the selected configuration. A properties file can be used by several configurations.
for different server and database objects. 

Deploying the project

MSBuild enables us to deploy the project by using the command line. To be able to use MSBuild easily, open the visual studio command prompt from the start menu–> Visual Studio 2008:

Here is a common syntax that can be used to deploy a database project:

MsBuild <database project> /t:Build

So, the resulting command line is:

MsBuild DB_ETL_DEMO.dbproj /t:Build

Now the project has beeen built. A DB_ETL_DEMO.dbschema file has been created. We can now deploy the dbschema file to our target database. The command line syntax is:

MsBuild DB_ETL_DEMO.dbproj /t:Deploy /p:Configuration="Test"

The file db_etl_demo.sql is now created in the test folder of my project:

Find out more MSBuild command line options

Refer to "An Overview of Database Build and Deployment" in DBPro documentation. It contain lots of sample to use MSBuild in conjunction with DBPro.

Summary

This article demonstrated how a database project can be deployed using Visual Studio configurations and MSBuild command line. MSBuild is installed by Visual Studio and is very convenient when DBPro projects have to be deployed to servers or machine where the database does not have similar setup. Also, since this is a command line tool, it can be wrapped into a scheduled task in order to automate database deployment at specific intervals. In another article, I will talk about TFSBuild, the build feature that comes with Team Foundation Server.

Labels: VSDB

Recently, I blogged about the new VSTS4DBP 2008 GDR command line utility. In this post, I will dig into the "how to use" the utility.

Prerequisites

To be able to use the utility, we need to copy some files to a folder:
C:\Program Files\Microsoft Visual Studio 9.0\VSTSDB\Deploy:
– VSDBCMD.exe
– DatabaseSchemaProviders.Extension.xml
– Microsoft.Data.Schema.dll
– Microsoft.Data.Schema.ScriptDom.dll
– Microsoft.Data.Schema.ScriptDom.Sql.dll
– Microsoft.Data.Sql.dll
– Microsoft.SqlServer.BatchParser.dll

C:\Program Files\Microsoft SqlServer Compact Edition\V3.5:
– Sqlceer35en.dll
– Sqlcecme35.dll
– Sqlceqp35.dll
– Sqlcecs35.dll 

C:\Program Files\Microsoft Sql Server Compact Edition\V3.5\Desktop:
– System.Data.SqlServerCe.dll

These files can be copied on the target machine or a thumb drive. They must be in the same folder in order to properly use VSDBCMD. In my directory, I created a folder “ImportedSchemas”. It will contain all my .dbschema files:

Reverse engineer an existing database

Before we reverse engineer our target database, we will make some changes to its structure. Let's rename the FirstName column to First_Name in Staging.Stg_Customers table.

The VSDBCMD term for database reverse engineer is import. So, the option we use at command line is /a:Import.

This command generate a file called ImportedSchemas\MyTarget.dbschma. This the offline representation of the target database on the machine where the VSDBCMD utility has been executed:
Now, using this file, we are able to compare it with our database project. We simply call the schema compare tool from the data menu and set the screen as this:

After the schema comparison, we get the following result:

The comparison tells that the target database Stg_Customers differ by one column; exactly the one we changed before generating our MyTarget.dbschema file!

Deploying our database project

Database project can be deployed using VSDBCMD by using two similar command line option.

• Generate a script that can be reviewed before executing it against the target database. This gives us a .SQL file that can be specidically named using the /DeploymentScriptFile option. To generate the script we use the /deploy option:
   
• Generate a script and directly deploy the target database. This gives us a script that can be version controlled and immediatly deployed to the database. To do it, we use the /deploy and /dd option:
  

Summary

This article demonstrated how the new VSDBCMD utility can be used to import or deploy a database. It is very useful when you want to compare a database against your database (DBPro) project or deploy it without using Visual Studio.

SSIS has a great feature called indirect configuration. It is used to store the configuration access path into the computer’s environment variable. This feature greatly facilitates the deployment of an SSIS solution. All you have to do is to set the environment variable on the target computer and all packages in the solution automatically points to the right place.

Configurations are at the very heart of SSIS packages. They are used to set specific properties to packages objects. 5 configuration types are available within an SSIS package:

Some information’s specific to my computer and SQL Server instance will be different in your file.

Using indirect configuration
Ok, now we have a DIRECT configuration. Our package references the full path of our configuration file. That’s not exactly what we want since once our package will be deployed, it's more than likely that the configuration file will be in the same location (drive, folder, etc.). To circumvent this behavior, we need to make our package to get its connection information (the configuration we just created) in a more generic way: we are going to use an environment variables – we are going to have an INDIRECT configuration.

First of all, we need to create an environment variable. But you need to be aware that our new environment variable won't be visible for our SSIS package or project if BIDS is opened before we create the variable. So, if BIDS is opened, close it.

There are two type of environment variables:

• System: these variables are available to all users that log on the computer.
• User: these variables, as the name of the type implies, are available to the user that create them and log on the computer.

SSIS is able to see both types. It is just easier to use system environment variables since all users can use them. Especially on a server. Several process could use it.

Also, there are several ways to create environment variables. My favorite way is using the "Setx" command line.If your operating system is Windows XP, you will have to download Windows XP SP2 Support Tools  Then, at command prompt, type:

Setx cfgn_AW_Path C:\MyProjectFolder\cfgn_AW_Path.dtsConfig -m

The -m option will make the environment variable "System". Now, open another command prompt window and type :

set cfgn_AW

Your screen should look like this:

Now, start up BIDS again and open your project. Open your package, right-click in the control flow and select "Package Configurations" from the popup menu. Now Edit your configuration that we've created earlier. Select the “Configuration location is stored in an environment variable” and then choose the cfgn_AW_Path environment variable.

Click "Next" and "Finish" and voila, your package reference your dtsx configuration file via an environment variable.

This article showed you how to use indirect package configuration. By using this type of configuration, you can change the location of your configuration files and even rename them. All of this will be transparent to your packages.

Labels: Intermediate SSIS