October 17, 2012 at 2:40 pm
I need to create SSIS package to copy daily .trc file that is created every 2 hours in a new folder
And delete anything older then 8 days.
For example :
I have trace file located at C:\tracefile\2012-10-01-06-20\
C:\tracefile\2012-10-01-06-21
C:\tracefile\2012-10-01-06-22
C:\tracefile\2012-11-01-04-23
C:\tracefile\2012-11-01-04-24
And ctr
And every day I am generating 12 .trc file so inside this folder (2012-10-01-06-20) is my .trc file I need to Load it to SQL database and update one more table just with trace file name and date for reference .
So I need only 2 table inside this Database
TraceTable
infoTable.
Any help will appreciated in advance.
October 17, 2012 at 2:47 pm
if you know the traceId(select * from sys.traces) , you can import it into a table, or create a view for it, all from within TSQL, so you probably do not need to use SSIS unless you are sending it to excel or something.
--SELECT * from sys.traces
declare @TraceIDToReview int
declare @path varchar(255)
SET @TraceIDToReview = 1 --this is the trace you want to review!
SELECT @path = path from sys.traces WHERE id = @TraceIDToReview
--INSERT INTO StaticTraceTable
SELECT
TE.name As EventClassDescrip,
v.subclass_name As EventSubClassDescrip,
TE.*
FROM ::fn_trace_gettable(@path, default) T
LEFT OUTER JOIN sys.trace_events TE ON T.EventClass = TE.trace_event_id
LEFT OUTER JOIN sys.trace_subclass_values V
ON T.EventClass = V.trace_event_id AND T.EventSubClass = V.subclass_value
or create aview and query it at will:
CREATE VIEW MySpecialTrace
AS
SELECT
TE.name As EventClassDescrip,
v.subclass_name As EventSubClassDescrip,
TE.*
FROM ::fn_trace_gettable'C:\tracefile\2012-10-01-06-20\TheTracefileName.trc', default) T
LEFT OUTER JOIN sys.trace_events TE ON T.EventClass = TE.trace_event_id
LEFT OUTER JOIN sys.trace_subclass_values V
ON T.EventClass = V.trace_event_id AND T.EventSubClass = V.subclass_value
byatoo (10/17/2012)
I need to create SSIS package to copy daily .trc file that is created every 2 hours in a new folderFor example
C:\tracefile\2012-10-01-06-20\ and in side this (2012-10-01-06-20) folder is my .trc file
Load it to SQL database lets call it DailyTrace and I have 2 table inside one for .trc (importTrace)and one (Traceinfo) to just update trace file names and time so I do not upload the same trace file again.
I use different script but some how it did not work for me.
Any help will appreciated in advance.
Lowell
October 17, 2012 at 3:04 pm
Thanks Lowell,
I need to automate this process so by running sql job every 2 hours it should grab old trace file that is 2 hours behind and dump it to table and update the other table and delete old data from table.
Thanks,
October 18, 2012 at 8:13 am
anyone created such SSIS package or Tsql script?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply