|
|
|
Forum Newbie
      
Group: General Forum Members
Last Login: Wednesday, October 24, 2012 8:20 PM
Points: 3,
Visits: 23
|
|
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.
|
|
|
|
|
SSChampion
        
Group: General Forum Members
Last Login: Today @ 3:24 PM
Points: 11,605,
Visits: 27,649
|
|
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 folder For 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
--There is no spoon, and there's no default ORDER BY in sql server either. Actually, Common Sense is so rare, it should be considered a Superpower. --my son
|
|
|
|
|
Forum Newbie
      
Group: General Forum Members
Last Login: Wednesday, October 24, 2012 8:20 PM
Points: 3,
Visits: 23
|
|
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,
|
|
|
|
|
Forum Newbie
      
Group: General Forum Members
Last Login: Wednesday, October 24, 2012 8:20 PM
Points: 3,
Visits: 23
|
|
| anyone created such SSIS package or Tsql script?
|
|
|
|