The Trace File Source adapter is a useful addition to your SSIS toolbox. It allows you to read profiler traces stored as .trc files and read them into the Data Flow. From there you can perform filtering and analysis using the power of SSIS. There is no need for a SQL Server connection this just uses the trace file.
- Cache warming for SQL Server Analysis Services
- Reading the flight recorder
- Find out the longest running queries on a server
- Analyze statements for CPU, memory by user or some other criteria you choose
The Trace File Source adapter has two properties, both of which combine to control the source trace file that is read at runtime. SQL Server 2005 and SQL Server 2008 trace files are supported for both the Database Engine (SQL Server) and Analysis Services. The properties are managed by the Editor form or can be set directly from the Properties Grid in Visual Studio. The SQL Server 2012 release supports 2012 trace files.
|AccessMode||Enumeration||This property determines how the Filename property is interpreted. The values available are:
|Filename||String||This property holds the path for trace file to load (*.trc). The value is either a full path, or the name of a variable which contains the full path to the trace file, depending on the AccessMode property.|
Trace Column Definition
Hopefully the majority of you can skip this section entirely, but if you encounter some problems processing a trace file this may explain it and allow you to fix the problem.
The component is built upon the trace management API provided by Microsoft. Unfortunately API methods that expose the schema of a trace file have known issues and are unreliable, put simply the data often differs from what was specified. To overcome these limitations the component uses some simple XML files. These files enable the trace column data types and sizing attributes to be overridden. For example SQL Server Profiler or TMO generated structures define EventClass as an integer, but the real value is a string.
- TraceDataColumnsSQL.xml – SQL Server Database Engine Trace Columns
- TraceDataColumnsAS.xml – SQL Server Analysis Services Trace Columns
The files can be found in the %ProgramFiles%\Microsoft SQL Server\nnn\DTS\PipelineComponents folder, where nnn matches the SQL Server version number. SQL Server 2008 is 100, SQL Server 2012 is 110. e.g.
- “C:\Program Files\Microsoft SQL Server\100\DTS\PipelineComponents\TraceDataColumnsSQL.xml”
- “C:\Program Files\Microsoft SQL Server\100\DTS\PipelineComponents\TraceDataColumnsAS.xml”
If at runtime the component encounters a type conversion or sizing error it is most likely due to a discrepancy between the column definition as reported by the API and the actual value encountered. Whilst most common issues have already been fixed through these files we have implemented specific exception traps to direct you to the files to enable you to fix any further issues due to different usage or data scenarios that we have not tested. An example error that you can fix through these files is shown below.
Buffer exception writing value to column ‘Column Name’. The string value is 999 characters in length, the column is only 111. Columns can be overridden by the TraceDataColumns XML files in “C:\Program Files\Microsoft SQL Server\100\DTS\PipelineComponents\TraceDataColumnsAS.xml”.
The component is provided as an MSI file which you can download and run to install it. This simply places the files on disk in the correct locations and also installs the assemblies in the Global Assembly Cache as per Microsoft’s recommendations.
You may need to restart the SQL Server Integration Services service, as this caches information about what components are installed, as well as restarting any open instances of Business Intelligence Development Studio (BIDS) / Visual Studio that you may be using to build your SSIS packages.
Fro 2008 Only – Finally you will have to add the transformation to the Visual Studio toolbox manually. Right-click the toolbox, and select Choose Items…. Select the SSIS Data Flow Items tab, and then check the Trace File Source transformation in the Choose Toolbox Items window. This process has been described in detail in the related FAQ entry for How do I install a task or transform component?
We recommend you follow best practice and apply the current Microsoft SQL Server Service pack to your SQL Server servers and workstations.
Please note that the Microsoft Trace classes used in the component are not supported on 64-bit platforms. To use the Trace File Source on a 64-bit host you need to ensure you have the 32-bit (x86) tools available, and the way you execute your package is setup to use them, please see the help topic 64-bit Considerations for Integration Services for more details.
Trace Sources for SQL Server 2008
Trace Sources for SQL Server 2012
Trace Sources for SQL Server 2014
Trace Sources for SQL Server 2016
Trace Sources for SQL Server 2017
Trace Sources for SQL Server 2019
SQL Server 2019
Version 22.214.171.1245 – SQL Server 2019.
(29 Sep 2019)
SQL Server 2017
Version 126.96.36.1995 – SQL Server 2017.
(29 Sep 2019)
SQL Server 2016
Version 188.8.131.525 – SQL Server 2016.
(29 Sep 2019)
SQL Server 2014
Version 184.108.40.2065 – SQL Server 2014 updated for SSAS columns ActivityID and RequestID.
(29 Sep 2019)
Version 220.127.116.115 – SQL Server 2014 release. Includes upgrade support for both 2005, 2008 and 2012 packages to 2014.
(6 Sep 2014)
SQL Server 2012
Version 18.104.22.1682 – SQL Server 2012 release. Includes upgrade support for both 2005 and 2008 packages to 2012.
(5 Jun 2012)
SQL Server 2008
Version 22.214.171.1242 – SQL Sever 2008 public release.
(9 Apr 2009)