Calling series of SSIS packages from SQL script

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

SQL Server Administration (2012)

Calling series of SSIS packages from SQL script

Author

Topic

malachi151
Posting Yak Master

152 Posts

Posted - 2014-06-10 : 15:16:50

We want to run a serves of SSIS packages in different environments as part of a deployment process.

I created a SQL script to do this where I call each of the packages in turn, but the problem is that it launches all of the packages immediately, but I need the package to run one at a time sequentially.

What is an easy way to do this within a SQL script?

Thanks

--------------------------------------------------
My blog: http://practicaltsql.net/
Twitter: @RGPSoftware

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-06-10 : 15:18:55

I suppose you could have the packages write somewhere when it is done so that your script can read that info, but I would create an SSIS package that calls each sequentially.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

malachi151
Posting Yak Master

152 Posts

Posted - 2014-06-10 : 15:38:14

quote:
Originally posted by tkizer

I suppose you could have the packages write somewhere when it is done so that your script can read that info, but I would create an SSIS package that calls each sequentially.

But the specific packages, their sequence, and the parameters are all unique per deployment, so that would require creating a temporary single run package wrapper for each deployment (once a week), and it would have all kinds of parameter passing problems.

For example, the same package is called 3 or 4 times with different parameters.

I was hoping there was a procedure in the SSISDB to check and see if a package is running, then I could lop until no packages are running or something. I don't see any obvious procs there that would do this though...

--------------------------------------------------
My blog: http://practicaltsql.net/
Twitter: @RGPSoftware

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-06-10 : 15:40:17

Just have the wrapper package read values from somewhere, like a table. That's the table you would manage for the unique parameters per deployment.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

malachi151
Posting Yak Master

152 Posts

Posted - 2014-06-10 : 15:49:44

Non-starter. Everything has to be controlled within the script.

--------------------------------------------------
My blog: http://practicaltsql.net/
Twitter: @RGPSoftware

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-06-10 : 15:52:24

Then have a batch file or SQL script that calls the wrapper package with the unique parameters.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

malachi151
Posting Yak Master

152 Posts

Posted - 2014-06-10 : 15:57:31

I think I've got it.

There is a view, catalog.executions, with a status.

I can query that and wait until the status is 7 to move on.

--------------------------------------------------
My blog: http://practicaltsql.net/
Twitter: @RGPSoftware

malachi151
Posting Yak Master

152 Posts

Posted - 2014-06-10 : 16:46:00

Here is basically what I've done as a simple start:

DECLARE
@status tinyint,
@statusDescription nvarchar(128);

DECLARE @statusTable TABLE
(
[status] tinyint,
[statusDescription] varchar(20)
);

INSERT INTO @statusTable
VALUES
(1, 'created'),
(2, 'running'),
(3, 'canceled'),
(4, 'failed'),
(5, 'pending'),
(6, 'ended unexpectedly'),
(7, 'succeeded'),
(8, 'stopping'),
(9, 'completed');

/* CALL PACKAGE HERE */

SET @status = 0;
WHILE (@status IN (1, 2, 5, 8))
BEGIN
SELECT @status=[status]
FROM SSISDB.[catalog].[executions]
WHERE
execution_id = @execution_id;

SELECT @statusDescription = [statusDescription]
FROM @statusTable
WHERE [status] = @status;

RAISERROR(@statusDescription, 1, 1) WITH NOWAIT;

WAITFOR DELAY '00:00:05';
END;

Then use that same loop between every package call. I'll end up creating a proc to do the loop, or perhaps just create a temporary proc at the beginning of the script.

--------------------------------------------------
My blog: http://practicaltsql.net/
Twitter: @RGPSoftware

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources