Trace File Source Adapter

by Darren and Allan 1 May 2009 08:40

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.

Example Usages

Properties

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.

Property Type Description
AccessMode Enumeration This property determines how the Filename property is interpreted. The values available are:
  • DirectInput
  • Variable
  • 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".

    Installation

    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.

    Downloads

    Trace Sources for SQL Server 2008

    Trace Sources for SQL Server 2012

    Version History

    SQL Server 2012

    Version 3.0.0.382 - SQL Server 2012 release. Includes upgrade support for both 2005 and 2008 packages to 2012.
    (5 Jun 2012)

    SQL Server 2008

    Version 2.0.0.382 - SQL Sever 2008 public release.
    (9 Apr 2009)

    Screenshots

    TraceFileSourceEditor-File TraceFileSourceEditor-Columns

    Comments (5) -

    8/2/2010 3:14:34 PM #

    Michael Rivera

    I found that the component ignores the Validate External Data Property. I have a mixed environment of SQL2K, 2K5 and 2K8 and this component only works on the latter two. I use another method to collect the data from 2K traces. Since I use a variable for the Trace File, I would like to not validate the external data when I have 2K trace file.

    Michael Rivera United States

    8/10/2010 10:46:54 PM #

    Darren

    Michael, hands up, your right we don't observe ValidateExternalMetadata, however we didn't write it to support 2000 either. From memory I'm not sure why it would be an issue though. If you have more info on a specific problem please drop me an email with more details and ideally a test trc and I'll see what I can do - darren.green@konesans.com

    Darren United Kingdom

    11/17/2010 4:55:32 AM #

    Jimmo Vink


    I have installed the TraceFileSource component on my local machine and it works great.  However, when I deploy my packages to our UAT environment (after running the Konesans installer) I get the following error:

    "The managed pipeline component "Konesans.Dts.Pipeline.TraceFileSource.TraceFileSource, Konesans.Dts.Pipeline.TraceFileSource, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b2ab4a111192992b" could not be loaded.  The exception was: Could not load file or assembly 'Konesans.Dts.Pipeline.TraceFileSource, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b2ab4a111192992b' or one of its dependencies. The system cannot find the file specified.."
    "The component is missing, not registered, not upgradeable, or missing required interfaces. The contact information for this component is "Trace File Source (Trace Sources); Konesans Ltd; Copyright © 2005-2008 Konesans Ltd; http://www.konesans.com".";  

    My machine is running Windows 7 32-bit.  Our UAT environment is Windows Server 2008 64-bit.

    Any help would be appreciated.

    Jimmo Vink Australia

    12/6/2010 9:25:34 AM #

    Darren Green

    Jimmo,

    As above...

    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.

    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.

    Darren Green United Kingdom

    5/6/2011 6:46:41 AM #

    Ashley McLellan

    I am getting the below error:
    -1073450910, System.IO.IOException: The process cannot access the file '\\Server\c$\SSAS_Audit1.trc' because it is being used by another process.
    We have a server side trace running into this file, would that be what is blocking it? I was able to access older files that are no longer written to fine.
    I was hoping that we could grab data from the trace file and place it into a table as the trace was running.
    Am I guessing that this may not be possible correct?

    Ashley McLellan Australia

    Add comment

      Country flag

    biuquote
    • Comment
    • Preview
    Loading

    Widget Amazon Book not found.

    Object reference not set to an instance of an object.X

    RecentComments

    Comment RSS