How to get the time taken by an ETL job and status- success or failure ?

Question

Post reply

How to get the time taken by an ETL job and status- success or failure ?

blasto_max

SSCertifiable

Points: 5025
More actions
October 23, 2013 at 11:43 pm

#284228

How do I get the time taken by an ETL job and status of a job, ie success or failure ?

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 1

Can you be a bit more specific?

Is the job scheduled with SQL Server Agent, or with another scheduler?

With which tool did you make the ETL job? If SSIS, did you enable logging?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

blasto_max SSCertifiable Points: 5025 More actions · Answer 2

Koen Verbeeck (10/24/2013)
Can you be a bit more specific?
Is the job scheduled with SQL Server Agent, or with another scheduler?
With which tool did you make the ETL job? If SSIS, did you enable logging?

I am running the job inside BIDS/Visual studio 2008 shell. Is the SSIS service going to run the job for me ?

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 3

BIDS is meant to develop and debug the SSIS packages.

After that, you need to deploy the packages to a server which will run them.

You either deploy them to SQL Server (in the MSDB database) or to the filesystem. (this is more of a personal choice, both options are OK)

The SSIS service needs to be installed on the server in order to run the SSIS packages, which you can schedule with SQL Server Agent.

Remark: the SSIS service itself doesn't run the packages. When you install the service, the necessary binaries for running packages are installed along with it.

When you run an SSIS package with a SQL Server Agent job, the job history will detail how long it took for the package to run.

You can also enable logging in the SSIS package, which will log the start and end of the package.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Luis Cazares SSC Guru Points: 183706 More actions · Answer 4

If you're running the package on BIDS, there's an execution results tab with that information.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

blasto_max SSCertifiable Points: 5025 More actions · Answer 5

But how do i see the exact line of my script task which caused an exception ?

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 6

blasto_max (10/24/2013)
But how do i see the exact line of my script task which caused an exception ?

Script task or script component? Last one is in the data flow, the other in the control flow.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

blasto_max SSCertifiable Points: 5025 More actions · Answer 7

Koen Verbeeck (10/24/2013)
blasto_max (10/24/2013)
But how do i see the exact line of my script task which caused an exception ?
Script task or script component? Last one is in the data flow, the other in the control flow.

script task.

Phil Parkin SSC Guru Points: 246962 More actions · Answer 8

But how do i see the exact line of my script task which caused an exception ?

If this was your question, why did you start the thread with:

How do I get the time taken by an ETL job and status of a job, ie success or failure ?

which is quite different.

You can set a breakpoint in a script and run it row by row to work out which row is failing.

You can also implement Try ... Catch error handling to give you useful error diagnostics. When there is an error, you should get a system exception, which you can process in your Catch bock.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin