The previous Level in this Stairway showed how to create SQL Jobs and database objects to continuously run traces among multiple servers. In this Level, I’ll show a custom SSIS package and SQL Agent job that imports those trace files periodically into a central trace data repository, building on the infrastructure created in the last Level. The end result will be continuously running traces on all managed servers with collected trace data available in a single table for ad-hoc analysis and event correlation across multiple servers.

Prerequisites

The import package uses the databases, objects and folder structures discussed in Level 7. These prerequisites must be created before the package is functional:

• A SqlTraceData database on a central server with a trace data table named ServerSideTraceData along with tables SqlInstance and SqlTrace containing the list of managed SQL Server instances and traces
• A shared folder on each server for server-side trace files
• A DbAdmin database on each instance with trace management stored procedures
• A Start SQL Traces job on each instance to start traces automatically when the SQL Agent service starts

SQL Trace File Import Package

I will assume you are already familiar with basic SSIS package development as I walk through the package components. You can download the Business Intelligence Development Studio (BIDS) solution with the completed package and scripts from here. Unzip the file and open the SqlTraceFileManagement.sln solution in BIDS (SQL 2008 or SQL 2008 R2) if you want to follow along interactively. If you have only SQL Server 2012 installed, open the solution with SQL Server Data Tools (SSDT) and convert the package when prompted but note that the package in SSDT will look somewhat different than the figures in this article. The functionality and concepts are identical, though.

Before I describe the package details, let me first mention that SSIS is quite flexible so there are many alternative methods to accomplish functionally identical tasks in SSIS. For example, I use C# Script Tasks in this package to move and delete files. This is purely personal preference and the same functionality could be accomplished with a Visual Basic Script Task or a File System Task in a Foreach Loop container. Feel free to substitute functionally identical techniques you are most comfortable with and customize as you desire.

Figure 1 shows the completed package control flow along with the connection managers, which I’ll describe shortly. This package performs the following tasks:

• Gets list of managed SQL Server traces from SqlTraceData database
• For each trace:
  • Build SQL trace folder paths
  • Stop trace
  • Move files to Completed folder
  • Restart trace
• For each trace file in Completed folder:
  • Import trace file into SqlTraceData database
  • Delete trace file

Figure 1: Import package control flow

1. I started with a new empty package named SqlTraceFileImport and created the package-scoped variables shown in Table 1. You can review these in BIDS (or SSDT) by right-clicking on the control flow canvas and selecting Variables. Since SSIS development is easier with connected validation, I specified a traced server name (REMOTE1 in this walkthrough) in the ServerName value along with the UNC path to an existing SQL 2008 trace file on the server (\\REMOTE1\SqlTraceFiles\BatchRpcAndDeadlock\BatchRpcAndDeadlock.trc) for the TraceFileSelectCommand variable value. The actual values of these variables will be set dynamically at run time using data from the SqlInstance and SqlTrace tables created in the prior Stairway Level.

Table 1: SqlTraceFileImport package variables

2. The SqlTraceData connection is a SQL Server Native Client OLE DB Connection referencing the trace management instance (named “CENTRAL” in this walkthrough) hosting the SqlTraceData database as shown in Figure 3. This connection is used to retrieve the list of managed traces and is also the destination of the imported trace data.

Figure 2: SqlTraceData connection properties

3. The DbAdmin connection is a SQL Server Native Client OLE DB Connection of the traced instance (“REMOTE1”) with the database name DbAdmin as shown in Figure 3. Remember that we created a DbAdmin database on each traced instance in Level 7 of this Stairway with the utility stored procedures used to manage traces. This connection is used to execute the trace management stored procedures against each traced instance and execute the query in the TraceFileSelectCommand variable to retrieve trace data from each instance.

   The actual server name for the DbAdmin connection is set dynamically at run time for each trace. This is accomplished using a property expression that maps the connection ServerName property to the ServerName variable. You can view the expression by right-clicking on the DbAdmin connection in the Connection Manager and selecting properties. In the properties window, click in the Expressions property value box and click the ellipsis button at the right-hand side to open the Property Expression Editor. Figure 4 shows this property expression.

Figure 3: DbAdmin connection properties

Figure 4: Property expression of DBAdmin connection

4. The first control flow task in the package is an Execute SQL Task named Get SQL Trace List. This task reads the list of managed traces into the SqlTraceList variable that is used by the next task. Figure 5 shows the general properties of this task. The SQLStatement property is set to the query in Listing 1, which retrieves the list of managed traces. Figure 6 shows the details of the Result Set configuration.

Figure 5: Get SQL Trace List task General properties

SELECT
   t.ServerName
   , i.SqlVersion
   , t.SqlTraceName
   , t.SqlTraceFolderPath
   , t.SqlTraceCreateStatement
FROM dbo.SqlInstance AS i
JOIN dbo.SqlTrace AS t ON
t.ServerName = i.ServerName;

Listing 1: Get SQL Trace List task SQLStatement

Figure 6: Get SQL Trace List task Result Set properties

5. The Foreach Loop Container named For Each Trace processes the result set stored in the SqlTraceList variable. For each row in the result set, For Each Trace sets the mapped variables values to the corresponding columns in the result set and then executes the contained tasks. Figure 7 shows the For Each Trace enumerator properties and Figure 8 shows the Variable Mappings.

Figure 7: For Each Trace Collection properties

Figure 8: For Each Trace Variable Mappings

1. The For Each Trace Loop Container contains the following 5 tasks linked together with the precedence constraints shown in the Figure 2 control flow.

   A. The Script Task (C#) named Build CompletedSqlTraceFolderPath updates the CompletedSqlTraceFolderPath and SqlTraceFilePath variables using the current values of the SqlTraceFolderPath and SqlTraceName variables mapped by the For Each Trace Loop Container. These variables are specified as ReadOnlyVariables and ReadWriteVariables in the Build CompletedSqlTraceFolderPath editor (Figure 9) so that these package variables can be used by the C# script in Listing 2.

Figure 9: Build CompletedSqlTraceFolderPath variables

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.IO;
namespace ST_8ddbc4bab2ef4443926805375108b6e2.csproj
{
    [System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {
        #region VSTA generated code
        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
        #endregion
        public void Main()
        {
            //asssign SSIS variables values to local variables
            string sqlTraceFolderPath = this.Dts.Variables["SqlTraceFolderPath"].Value.ToString();
            string sqlTraceName = this.Dts.Variables["SqlTraceName"].Value.ToString();
            //build base path to Completed folder (e.g. "\\REMOTE1\SqlTraceFiles\BatchRpcAttentionDeadlock\Completed")
            string completedSqlTraceFolderPath =
                Path.Combine(sqlTraceFolderPath , "Completed");
            this.Dts.Variables["CompletedSqlTraceFolderPath"].Value = completedSqlTraceFolderPath;
            //build file path for sp_trace_create (e.g. "\\REMOTE1\SqlTraceFiles\BatchRpcAttentionDeadlock\BatchRpcAttentionDeadlock")
            string sqlTraceFilePath =
                Path.Combine(sqlTraceFolderPath, sqlTraceName);
            this.Dts.Variables["SqlTraceFilePath"].Value = sqlTraceFilePath;
            Dts.TaskResult = (int)ScriptResults.Success;
        }
    }
}

Listing 2: Build CompletedSqlTraceFolderPath C# script

B. The second task of the For Each Trace Loop Container is an Execute SQL Task named Stop Trace with the General properties shown in Figure 10 and the SQLStatement in Listing 3. The Parameter Mapping in figure 11 passes the trace file path to stored procedure dbo.usp_DeleteTraceByTraceFilePath (created in the prior Stairway Level). This will stop the trace temporarily so that the files created by this trace can be moved to the Completed folder.

Figure 10: Stop Trace General properties

EXEC dbo.usp_DeleteTraceByTraceFilePath
@TraceFilePath = ?;

Listing 3: Stop Trace SQL script

Figure 11: Stop Trace Parameter Mapping

C. The C# Script Task named Move Trace Files to Completed Folder moves all of the trace files in the current trace directory to the Completed folder. The source and target folders are specified using the SqlTraceFolderPath and CompletedSqlTraceFolderPath as Figure 12 shows. As you may recall from the last Level of this Stairway, the BatchRpcAndDeadlock trace has a rollover specification of up to 10 files so the script in Listing 4 moves multiple files using a “*.trc” wildcard specification.

Figure 12: Move Trace Files to Completed Folder variables

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.IO;
namespace ST_2258a0db35d547d9a1fb4aab93a788e1.csproj
{
    [System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {
        #region VSTA generated code
        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
        #endregion
        public void Main()
        {
            string sqlTraceFolderPath = Dts.Variables["SqlTraceFolderPath"].Value.ToString();
            string completedFolderPath = Dts.Variables["CompletedSqlTraceFolderPath"].Value.ToString();
            //move trace files to completed folder
            string[] sqlTraceFiles = Directory.GetFiles(sqlTraceFolderPath, "*.trc");
            foreach (string sqlTraceFile in sqlTraceFiles)
            {
                string completedSqlTraceFile = Path.Combine(completedFolderPath, Path.GetFileName(sqlTraceFile));
                File.Move(sqlTraceFile, completedSqlTraceFile);
            }
           
            Dts.TaskResult = (int)ScriptResults.Success;
        }
    }
}

Listing 4: Move Trace Files to Completed Folder C# script

D. After the trace files are moved to the Completed folder, the Execute SQL Task named Restart Trace executes the stored procedure to restart the trace. The General properties in Figure 13 show that the name of the procedure is stored in the SqlTraceCreateStatement variable, which was retrieved by the query in Listing 1 and mapped to the variable by the For Each Trace Loop Container.

Figure 13: Restart Trace General properties

E.The last item in the Foreach Trace Loop Container is a nested Foreach Loop Container named Foreach Trace File. The 3 control flow tasks in this container build the trace data select statement, import the trace data into the SqlTraceData table and finally delete the imported trace file. Figure 14 shows the collection properties that gets the list of “*.trc” files in the Completed folder. Note that I specified an existing completed folder as the Folder property to avoid validation warnings during development. The actual directory name will be set at run time using the CompletedSqlTraceFolderPath variable as specified by the Directory property expression shown in Figure 14. Figure 15 shows the Variable Mappings that set the SqlTraceFilePath variable to the current trace file.

Figure 14: Foreach Trace File Collection properties

Figure 15: Foreach Trace File Variable Mappings

Below are the 3 tasks of the Foreach Trace File Loop Container:

1. The C# Script Task named Build TraceFileSelectCommand builds the select statement used to retrieve trace data. Since the trace file GroupID column was introduced in SQL Server 2008, the script adds a GroupID column with a NULL value when selecting from a SQL Server 2005 trace file as determined by the SqlVersion variable value. This allows trace files from different SQL versions to be imported into the same destination table.

Figure 16: Build TraceFileSelectCommand property

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;

namespace ST_d6414fce57af4cd481834a9f94fc8b6d.csproj
{
    [System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {
        #region VSTA generated code
        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
        #endregion

        public void Main()
        {
            string sqlVersion = Dts.Variables["SqlVersion"].Value.ToString();
            //add NULL GroupID to select list for SQL 2005
            string traceFileSelectCommand =
                sqlVersion == "SQL2005" ?
                    string.Format(@"SELECT *, CAST(NULL AS int) AS GroupID FROM fn_trace_gettable(N'{0}', DEFAULT);"
                    ,Dts.Variables["SqlTraceFilePath"].Value.ToString()) :
                    string.Format(@"SELECT * FROM fn_trace_gettable(N'{0}', DEFAULT);"
                        , Dts.Variables["SqlTraceFilePath"].Value.ToString());

            Dts.Variables["TraceFileSelectCommand"].Value = traceFileSelectCommand;
            Dts.TaskResult = (int)ScriptResults.Success;
        }
    }
}

Listing 5: Build TraceFileSelectCommand C# script

2. Import Trace File is a simple Data Flow Task consisting of an OLE DB Source named Sql Trace File and an OLE DB Destination named ServerSideTraceData Table as Figure 17 shows. This task runs the TraceFileSelectCommand query and inserts the rows into the ServerSideTraceData table.

Figure 17: Import Trace File data flow

a. Figure 18 shows the SQL Trace File OLE DB Source Connection Manager properties. The displayed variable value shows the current design-time TraceFileSelectCommand variable value. The actual run-time value will be set by the previous Build TraceFileSelectCommandtask. The columns of the SQL Trace File source are shown in Figure 19.

Figure 18: SQL Trace File Connection Manager properties

Figure 19: SQL Trace File Mappings

b. The ServerSideTraceData Table OLE DB source Connection Manager properties shown in Figure 20. The columns are mapped as shown in Figure 21.

Figure 20: ServerSideTraceData Table Connection Manager properties

Figure 21: ServerSideTraceData Table Mappings

3. The final task of the For Each Trace File Loop Container is a C# Script Task named Delete Trace File. The C# script in Listing 6 uses the SqlTraceFilePath variable value to delete the trace file just imported. Figure22 shows the script properties.

Figure 22: Delete Trace File script properties

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.IO;

namespace ST_7d08cdc4a9e44875afdc81ab840d975d.csproj
{
    [System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {
        #region VSTA generated code
        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
       #endregion
        public void Main()
        {
            //delete trace file
            string sqlTraceFilePath = Dts.Variables["SqlTraceFilePath"].Value.ToString();
            File.Delete(sqlTraceFilePath);
          
            Dts.TaskResult = (int)ScriptResults.Success;
        }
    }
}

Listing 6: Delete Trace File script

Import SQL Trace Files Job

The last element of the SQL Trace Import process is a SQL Agent job named SqlTraceFileImport to execute the SqlTraceFileImport package on a regular schedule. This job is run on the trace management instance and consists of a single SSIS step as shown in Figure 23. I deployed the package to SQL Server (msdb database) here but you can use the File System, SSIS Package Store or SSIS catalog if you prefer.

Figure 23: SqlTraceFileImport job step

Figure 24 shows the schedule for this job. I scheduled this package to run daily at midnight but feel free to adjust the schedule according to your needs. For example, you might schedule it hourly for more proactive intraday analysis.

Figure 24: SqlTraceFileImport job schedule

Although I didn’t include one in this walkthrough, you’ll probably want to create a job to regularly purge old trace data from the \ServerSideTraceData\ table based on your retention criteria. You may also want to add a package configuration in order to make the SqlTraceData server name configurable at run time as well as add logging according to your organization’s standards.

Summary

Automation of Trace file management allows you to create a centralized repository for trace data gathered from multiple instances. This trace data can be used for troubleshooting, proactive monitoring or event correlation. The initial setup of the process takes some effort but it is easy to reuse and extend for other multi-instance management tasks as well.

In the next article in this Stairway, I’ll discuss how to automate SQL Traces using the Data Collector feature of SQL Server 2008.