sql - How can I wait for an SSIS package to complete

Question

I have a SQL agent job. The first step in the job is to run an SSIS package that loads data from a .txt file into a SQL table. The next steps in the job manipulate the data that was just loaded into the table until finally refreshing a table in excel with results. The problem I am having is that it seems the job does not wait for the SSIS package to complete before moving on to step 2 of job, so sometimes it works fine, and sometimes it starts the data manipulation before the data is loaded into the table and gives incorrect results. How can I get the job to wait for the SSIS package to complete before moving on?

Answer 1

Are you running this in SQL Server 2012? There is a specific parameter for IS Catalog packages called SYNCHRONIZED which, if set to false, considers a package execution step to be successful as soon as the package starts.

To see the various commands being executed by your job, run this query on the server running your jobs:

select step_id, step_name, subsystem, command from msdb.dbo.sysjobsteps js
inner join
msdb.dbo.sysjobs j
on j.job_id = js.job_id
where
j.name = <Your Job Name Here>
order by step_id

If you don't see

/Par ""$ServerOption::SYNCHRONIZED(Boolean)"";True

In the command field, that means it's running asynchronously and your next step will start as soon as the package begins execution.

The only way I know to fix it is to either modify the command field for your job step in the dbo.sysjobsteps table directly, use the sp_update_jobstep stored procedure, or drop and re-add your step with the GUI.