Automate SQL Server Testing with Profiler, SQLCMD and SSIS

By: Jeremy Kadlec   |   Updated: 2007-11-14   |   Comments (5)   |   Related: > Testing

Problem

One of the Edgewood Solutions team members recently spoke at a testing conference held in Washington, DC by the name of Verify.  During the presentation, a few different questions were asked about automating database testing with the native SQL Server 2005 tool set.  In this tip, we will outline some options to automate database testing and verification with Profiler, SQLCMD and SQL Server Integration Services.

Solution

Conducting thorough testing of a database driven application or automated (database) process can easily be a full time job by itself.  Trying to automate the testing process typically results in an application of one type or another in order to streamline manual repetitive steps.  Typically the testing process follows these steps:

• Identify the applicable code
• Build test cases
• Test an application build
• Verify results
• Report status to team

Based on this general process, let's see how some of the native SQL Server tools can help with some of these specific processes.

Identify the applicable code - Profiler

The best native tool for capturing the code issued by an application, user or host is Profiler.  SQL Server Profiler has been available for the last few versions of SQL Server and provides a mechanism to capture the T-SQL commands issued against specific SQL Server instance, then save the results to a table or file for further analysis.  In addition, Profiler has the means to filter the data being collected so that only specific users or host traffic is captured.  Here are some quick steps to setup a Profiler session:

Be sure to review the code captured to ensure only the needed commands are included in your test cases.  It might be a good idea to cross check the code captured versus the steps taken by the user or application.  Then save out the code to separate files based on the logical progression in your application.

In addition, one item to keep in mind here is that the code being captured by Profiler is anything passed to SQL Server.  So this provides the opportunity to separate testing the front end application from the back end code and database.  Be sure to test the front end as well to ensure the front end behaves as expected and does not throw any errors when columns are added\removed, objects change names or security changes.  With the information captured from the Profiler session these scenarios will probably not be caught.

Test an application build - Option 1 - SQLCMD

Once you have the needed code as an input, then the next step is to be able to specify an input file with the code and an output file with the results.  This is where SQLCMD comes into play.  SQLCMD is a Windows command line interface into a SQL Server 2005 instance where you can specify a server name, instance name, database, user name, password, input file, output file, etc.  Let's take a look at an example set of code to call SQLCMD at a Windows command line:

Here is a brief explanation of the code parameters:

• sqlcmd - Command
• -S JTKLaptop - SQL Server name and instance (default instance in this example)
• -d CustomerDB - Database name
• -E - Trusted user connection
• -i C:\TestScripts\sqlcmd_input.txt - Input file
• -o C:\TestScripts\sqlcmd_output.txt - Output file
• Listing of all of the SQLCMD command line options - sqlcmd Utility

SQLCMD is offers a great deal of functionality beyond the set of parameters listed in this example, but the greatest value may be the simplicity.  Once you have your code captured (input), the learning curve is small to be able to run SQLCMD and determine if the process was successful or not. 

Here are a few tricks with SQLCMD in the input file:

• Add PRINT statements in order to determine the process
• If possible, do not add SET NOCOUNT ON statements to your code because you will not be able to determine if rows were inserted, update or deleted
  • If you have SET NOCOUNT ON statements then you may want to issue SELECT statements before and after the code changes to be able to validate the process

Test an application build - Option 2 - SQL Server Integration Services

To turn up the functionality, SQL Server Integration Services (SSIS) is the next native tool available to assist with the automated testing.  SSIS is traditionally an extraction, transformation and loading tool (ETL), but does have the functionality to execute scripts and add conditional logic in a rich graphical interface.  In a nutshell, it is necessary to do the following with SSIS in order to setup a package:

• Build connections
  • Input data (text file)
  • Output data (text file)
  • SQL Server database
• Execute the needed scripts
  • The Execute SQL Task might be the best approach to house the T-SQL test cases.
• Review the output
  • The results could be saved to a table, text file or reviewed as a log for the process.

Here is an example using SSIS package to execute automated testing built in the Business Intelligence Development Studio (BIDS):

SQL Server 2005 Integration Services Example

SSIS is a very powerful tool and has a great deal of functionality.  The explanation in this tip is just the beginning, so check out the SQL Server Integration Services tips category.  Depending on the testing that is needed, SSIS might fit the bill and improve the overall functionality or might be more tool for the task.  You be the judge and consider SQLCMD or SSIS to assist in the testing automation.

Next Steps

• Depending on the database testing you need to accomplish, using Profiler, SQLCMD and SSIS might be a viable alternatives to meet your needs.
• Although all of the tools are freely shipped with SQL Server, depending on your proficiency with the tools, you might need to invest some time learning them or work with your DBAs to capture the necessary code then work to automate the process.
• If you are responsible for testing SQL Server based applications at your organization, we would love to hear about approaches you use to automate the testing.  Please add your comments to the forum via the URL below.
• Check out these related resources:
  • Clearing Cache for Valid Performance Testing
  • SQL Server Code Review Checklist
  • Code Deployment Best Practices
  • Testing Options with SQL Server 2000 and 2005
  • SQLCMD
  • Profiler
  • SQL Server Integration Services
• Stay tuned for an upcoming tip on replaying a Profiler session.
• Many thanks to everyone who attended the recent session by the Edgewood Solutions team at the Verify conference.  Hopefully we were able to share as much knowledge as we gained from learning about some of your testing challenges with SQL Server.  Hope to see you next year!

About the author

Jeremy Kadlec is a Co-Founder, Editor and Author at MSSQLTips.com with more than 300 contributions. He is also the CTO @ Edgewood Solutions and a six-time SQL Server MVP. Jeremy brings 20+ years of SQL Server DBA and Developer experience to the community after earning a bachelor's degree from SSU and master's from UMBC.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips