Dynamically executing multiple packages using execute package task in SSIS

Question

I'm trying to execute several packages dynamically.

I've a table in database that stores the package names and has an indicator for each as 'Y/N'. My ultimate objective is to execute those with indicator 'Y'.

I'm creating a variable that takes all the package names under the indicator 'Y'.

I'm using a for each loop container under which I've placed an execute package task. I'm assigning an ADO enumerator in the for each loop and assigning my variable to ADO object source variable.

Is there a way to use a property expression in the Execute package task, to which I can assign the the package names through the variable and dynamically execute the necessary packages (child packages) in the same solution?

Answer 1

Your approach is correct and just need a little tuning.
ExecutePackage Task has a property PackageName which contains full path to the package. In case of SSISDB Catalogs it is just a package name when you are invoking a package from your project. If packages are stored in MSDB, then it is '\\Folder1\\Folder2\\Packagename'. You can set PackageName property whatever you like with help of Expressions (i.e. prefix package path if needed).
Small example

Answer 2

Yes - I approached the issue differently though. This will call a SQL Agent job - the name of the job is passed in as a variable - this would be populated by your FOR...LOOP.

• The job is executed
• We monitor sysjobactivity to detect when it is actually running and when it has finished.
• Once it has finished we look for the most recent entry in sysjobhistory to see what the status is (an entry is only written to this table after execution has stopped - if we checked immediately we would get the last known status)

In order for this to work you must be confident that you are the only user likely to run this job - otherwise you could get somebody else's history!

The user running the job requires SQLAGENTOPERATORROLE in MSDB.

    SET NOCOUNT ON;

DECLARE @job_name VARCHAR(100) = 'YOU_JOB_NAME_HERE'
EXEC msdb.dbo.sp_start_job @job_name = @job_name

WHILE EXISTS(
    SELECT 1 FROM msdb.dbo.sysjobs_view job
    INNER JOIN msdb.dbo.sysjobactivity activity ON job.job_id = activity.job_id
    INNER JOIN msdb.dbo.syssessions sess ON sess.session_id = activity.session_id
    INNER JOIN (SELECT MAX(agent_start_date) AS max_agent_start_date FROM msdb.dbo.syssessions) sess_max ON sess.agent_start_date = sess_max.max_agent_start_date
    WHERE job.name = @job_name  AND run_requested_date IS NOT NULL AND stop_execution_date IS NULL
)
BEGIN
    PRINT 'Running'
    WAITFOR DELAY '00:00:01'
END
PRINT 'Finished'

SELECT TOP 1 HIST.run_status
    -- 0=Failed, 1=Succeeded 2=Retry 3=Cancelled
FROM msdb.dbo.sysjobs JOB
INNER JOIN  msdb.dbo.sysjobhistory HIST ON HIST.job_id = JOB.job_id
WHERE JOB.name = @job_name
ORDER BY HIST.run_date, HIST.run_time