index Execution Not Capturing from Sql query

• Question

• 

  

  0

  Sign in to vote

  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

  Friday, July 27, 2012 5:31 AM