Automate SQL Server Testing with Profiler, SQLCMD and SSIS

By:   |   Comments (5)   |   Related: > Testing


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.


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:

Launch SQL Server 2005 Profiler

Navigate to Start | All Programs | Microsoft SQL Server 2005 | Performance Tools | SQL Server Profiler.

Profiler InitialScreen 1


Start a New Trace

Navigate to File | New Trace... | Authenticate to the SQL Server you want to monitor

Profiler Login 2


General Tab - Configurations

Specify the name and configure the results to be saved to a table.

Profiler GeneralTab 3


Events Selection Tab - Configurations

In order to review all events, enable the check box for 'Show all events' and 'Show all columns' on the bottom right of the screen.

Profiler ShowAllEvents 4

Depending on how the testing is being conducted, it might make sense to filter the Profiler session by a combination of the application name, host name, login name, spid, etc in order to capture only a specific set of code.

Profiler EditFilter 5

Once you have filtered the settings then select the Profiler events that are needed based on the type of code that is being captured.

Profiler EventsSelectionTab 6

Once you have made these configurations or others as desired, then press the 'Run' button to begin the Profiler session.


Testing and Profiler Results

Begin to test the application and the results in Profiler will be something like the following:

Profiler Results 7


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:

SQL Server 2005 - SQLCMD Example

sqlcmd -S JTKLaptop -d CustomerDB -E -i C:\TestScripts\sqlcmd_input.txt
-o C:\TestScripts\sqlcmd_output.txt

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

SQLServerIntegrationServices 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:
  • 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!


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Jeremy Kadlec Jeremy Kadlec is a Co-Founder, Editor and Author at 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

Comments For This Article

Wednesday, December 12, 2012 - 9:54:32 AM - Jeremy Kadlec Back To Top (20913)


You would need to have an Execute SQL Task run this code:

To stop a trace sp_trace_setstatus traceid, 0 TraceId would be the value of the trace

To learn more about this, check out this tip -

Thank you,
Jeremy Kadlec
Co-Community Leader

Wednesday, December 12, 2012 - 5:16:15 AM - Razia Back To Top (20909)


Any idea if we can start/stop profile trace through SSIS?


Sunday, July 15, 2012 - 1:09:58 PM - Jeremy Kadlec Back To Top (18529)


I assume your question is related to SQLCMD not SSIS.  If that is the case, then in your input file for your SQLCMD file, why not use a INSERT...SELECT command or SELECT...INTO command to load the data into a new table.  Here are some URLs as a point of reference:


Thank you,
Jeremy Kadlec

Thursday, July 12, 2012 - 8:42:16 AM - Dinesh Vishe Back To Top (18470)

I am getting data in csv format but I want to in Ecle or table format ...

Please help me..

Monday, July 9, 2012 - 3:52:18 AM - Dinesh Vishe Back To Top (18399)

If i want sql cmd result in mssql table then what to do ???

Please help me...

get free sql tips
agree to terms