Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SSIS package to import daily SQL trace file to table Expand / Collapse
Author
Message
Posted Wednesday, October 17, 2012 2:40 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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.
Post #1374056
Posted Wednesday, October 17, 2012 2:47 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:40 PM
Points: 12,880, Visits: 31,802
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
Post #1374060
Posted Wednesday, October 17, 2012 3:04 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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,
Post #1374064
Posted Thursday, October 18, 2012 8:13 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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?
Post #1374396
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse