I have a SQL job that runs an SSIS package and it basically pulls records from staging table and dumps it into the master table. It used to take around 8 hrs. to run but in these last 10 days or so the run duration has increased and I would like to know if there is a way I can find out where the job is spending more time and what it is waiting on either running a profiler trace or running a tsql script inside a job to capture anything useful. It runs every day except Sat at 11p est.
Though not really enough info here to give a full answer these steps are how I would approach trouble shooting this...
check the execution logs for the package. Take note of the timings on steps. It should he obvious which takes longest.
execution plans are helpful once you know which to look at. If it's stored procs or a fairly static query the should be in the query plan.
change the package to manually log individual steps and information that you can clearly analyse later it will help identify the slow running points. Add row counts to see if you can quantify if a per row metric to calculate run time. Even run a query to log current connections and transactions to see if it's clashing with other tasks.
This being said 8 hour sync between staging and live is huge unless we're talking about 100s of millions of rows. The run time could suggest missing indexes on staging but the execution plans should tell you that.