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-Track Execution Time of a Data Flow Task Expand / Collapse
Author
Message
Posted Sunday, February 16, 2014 9:20 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 11:15 AM
Points: 38, Visits: 183
Hi Everyone,

I have this requirement,can any one give me an idea on how to implement this

I have a FlatFile which is around 500mb and loads the data daily into SQL Destination,which is considered as important task for business and some times this load hangs because of other people using the same table.
but my requirement is to send a email to specified people when the task is in progress and it
takes more than 10 minutes,such that every one stops using that table.
Post #1541981
Posted Monday, February 17, 2014 4:26 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:36 AM
Points: 13,345, Visits: 10,205
Maybe you can add a Send Email Task before and after the data flow task?



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1542038
Posted Monday, February 17, 2014 4:38 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 11:15 AM
Points: 38, Visits: 183
Hi Koen,

thanks for your reply.
I need to send an email only in-case the data load(DFT) takes more than 10 minutes(or a specified interval),and it is still in execution but not all times...any thoughts ?
Post #1542042
Posted Monday, February 17, 2014 4:53 AM This worked for the OP Answer marked as solution


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:36 AM
Points: 13,345, Visits: 10,205
Maybe you can monitor the log? Or kick off a monotoring script in parallel with your data flow.
You insert a row into a log table before and after the data flow. The monitoring script sleeps for 10 minutes, than checks if the second row has been inserted. If not, a mail is sent.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1542048
Posted Monday, February 17, 2014 6:48 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, July 24, 2014 1:50 PM
Points: 532, Visits: 448
Can you change the timing of the job, so that it runs when no one is using the table?
Post #1542086
Posted Monday, February 17, 2014 9:11 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 11:15 AM
Points: 38, Visits: 183
I used WAITFOR to achieve the same instead of script.
thanks for your idea.
Post #1542189
Posted Monday, February 17, 2014 7:03 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, April 17, 2014 11:08 AM
Points: 2, Visits: 29
What about Kicking off a second DFT, with a timer in a script. while the package with the large file sets a variable at completion or whenever your time sink it passed, the variable would negate the timed package from completing, or by bypassing the email and just failing into nothing, or triggering the notification of your users.
Post #1542330
Posted Monday, February 17, 2014 11:57 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 12:45 AM
Points: 4,983, Visits: 11,677
Koen Verbeeck (2/17/2014)
Maybe you can monitor the log? Or kick off a monotoring script in parallel with your data flow.
You insert a row into a log table before and after the data flow. The monitoring script sleeps for 10 minutes, than checks if the second row has been inserted. If not, a mail is sent.


Nice idea!



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1542386
Posted Tuesday, February 18, 2014 12:01 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, July 25, 2014 11:42 AM
Points: 64, Visits: 660
If the table is re-loaded every day, you could consider using view swapping/schema switching/table partitioning/rename table. I.e. you load a table the user can’t see, once the load is done, you swap the tables. That way you should never have to send an email.
Post #1542701
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse