index Exection time not Capturing the from query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

index Exection time not Capturing the from query

Author

Topic

kond.mohan
Posting Yak Master

213 Posts

Posted - 2012-07-27 : 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

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-07-27 : 06:03:33

Have you looked to see what data is in the log?

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources