Automate Trace file import process with SSIS
I have previously written two articles on this subject “Profiler Trace”.
Start and stop server side profiler trace automatically in SQL Server 2012: This article shows how to create profiler trace file automatically for our business hours.
Read SQL Server Profiler Trace files with SELECT statement: This article shows how to read trace file with TSQL which was generated by the previous article.
Reading trace file manually with TSQL and after reading the trace file, delete those trace file is, somehow, repetitive as well as boring work to do. I used to create one SSIS package which reads profiler trace file and insert data into SQL Server table, delete old trace file and then remove 7 days old trace data from SQL Server table.
Let us create one SSIS project in Visual Studio 2010 and follow the steps given:
1.) Have one “Execute SQL Task” in SSIS Package. Assign following INSERT statement in “SQLStatement” property of Execute SQL Task and create one connection of your database.
Insert into Profilerlog SELECT ServerName ,DatabaseName ,SPID AS SessionId ,ObjectName ,EventClass AS EventNumber ,RowCounts ,CASE WHEN Duration IS NOT NULL THEN Duration/1000000.00 ELSE Duration END AS DurationInSeconds ,CPU AS CPUInMiliseconds ,StartTime ,EndTime ,TextData FROM fn_trace_gettable('D:\TraceCollection\Adventureworks2012.trc', DEFAULT) go
You must have “ProfilerLog” table in your database which can take output of the SELECT query we have executed on trace file. I have my trace file at “D:\TraceCollection\Adventureworks2012.trc”, you can have your own path. Have a look at screen capture of the Execute SQL Task.
2.) Once we have imported data from trace file, we can delete all trace file which we have already read so let us have “Foreach Loop Editor” and iterate it for each trace file we have in our folder so that we can remove it. Set the property of Foreach loop as displayed in screen capture.
3.) You have to create variable “FileName” for package and it should be set to 0 as an Index in Foreach Loop’s “Variable Mappings” tab. You can refer above screen shot where you can find “Variable Mappings” right below “Collections” in left hand side.
4.) put “File System Task” inside foreach loop container and set “Delete file” in Operation property as given in following screen shot.
5.) now have one more execute SQL task under the foreach loop and give following query which will remove 7 days old data from ProfilerLog table.
delete from Profilerlog where convert(varchar(10),StartTime,101) < convert(varchar(10),getdate()-7,101) go
6.) finally your package will looks like this:
Now you are ready to schedule this package in your SQL Server Agent job which runs once (may be at midnight) and import trace file.
Reference: Ritesh Shah
Note: Microsoft Books online is a default reference of all articles.