|
kond.mohan
Posting Yak Master
India
140 Posts |
 Posted - 07/27/2012 : 01:42:42
|
Dear all
we have designed SSIS (Sql server 2008 r2) Packages.Pkg has Designed
1.truncate all tables using Execute Sql Task( inside task using Stored Procedure )
2. truncate all ReportTables using Execute Sql Task( inside task using Stored Procedure )
3.Drop Index Container(Sequence Container used here)
inside of this container 10 Execute Sql Tasks are Existing .Each Execute Sql task is calling one Stored procedure.Each Stored procedure is having Droping Existed indexes on tables
After that i am loading the Data next
Create index container(Sequence Container used here)
inside of this container 10 Execute Sql Tasks are Existing .Each Execute Sql task is calling one Stored procedure.Each Stored procedure is having Creating indexes on tables.
here i have Configured Text log file i capturing the data i have Exported the data into my sql server 2008 r2(flat file to oledbdestination)
here we have created Sql query for to find how much time is taking by containers on Everyday At execution
query is fetching the all taskExecution time but
My Query is NOT FETCHING THE CREATE DROP INDEX CONTAINER(tasks) EXECUTION TIME
my query is existed like this
SELECT * FROM(
SELECT MIN(starttime)starttime,MAX(endtime)Endtime,
DATEDIFF(MI,MIN(starttime),MAX(endtime))AS [Time Taken In Minutes],
[SOURCE],CONVERT(varchar,starttime,103)etl_date FROM sysssislog
WHERE
starttime >= '19-JULY-2012' and starttime < '19-JULY-2012'
and [event] in ('OnPreExecute','OnPostExecute')
GROUP BY source, CONVERT(varchar,starttime,103)
)a --where [source] like '%import4%'
--ORDER BY etl_date,[Time Taken In Minutes] desc
union all
SELECT * FROM(
SELECT MIN(starttime)starttime,MAX(endtime)Endtime,DATEDIFF(MI,MIN(starttime),MAX(endtime))AS [Time Taken In Minutes],[SOURCE],CONVERT(varchar,starttime,103)etl_date FROM sysssislog
WHERE
starttime >= '19-JULY-2012' and starttime < '2012-07-19 16:45:33.000'
and [event] in ('OnPreExecute','OnPostExecute')
GROUP BY source, CONVERT(varchar,starttime,103)
)a --where [source] like '%import4%'
ORDER BY --[source],
[Time Taken In Minutes] desc
how to fetch my create and drop index container Execution time from the query
pls guide me |
|
index Exection time not Capturing the from query
