SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 SQL Server Administration (2012)
 Calling series of SSIS packages from SQL script
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

malachi151
Posting Yak Master

152 Posts

Posted - 06/10/2014 :  15:16:50  Show Profile  Visit malachi151's Homepage  Reply with Quote
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

USA
37446 Posts

Posted - 06/10/2014 :  15:18:55  Show Profile  Visit tkizer's Homepage  Reply with Quote
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/
Go to Top of Page

malachi151
Posting Yak Master

152 Posts

Posted - 06/10/2014 :  15:38:14  Show Profile  Visit malachi151's Homepage  Reply with Quote
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
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37446 Posts

Posted - 06/10/2014 :  15:40:17  Show Profile  Visit tkizer's Homepage  Reply with Quote
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/
Go to Top of Page

malachi151
Posting Yak Master

152 Posts

Posted - 06/10/2014 :  15:49:44  Show Profile  Visit malachi151's Homepage  Reply with Quote
Non-starter. Everything has to be controlled within the script.

--------------------------------------------------
My blog: http://practicaltsql.net/
Twitter: @RGPSoftware
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37446 Posts

Posted - 06/10/2014 :  15:52:24  Show Profile  Visit tkizer's Homepage  Reply with Quote
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/

Edited by - tkizer on 06/10/2014 15:52:57
Go to Top of Page

malachi151
Posting Yak Master

152 Posts

Posted - 06/10/2014 :  15:57:31  Show Profile  Visit malachi151's Homepage  Reply with Quote
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
Go to Top of Page

malachi151
Posting Yak Master

152 Posts

Posted - 06/10/2014 :  16:46:00  Show Profile  Visit malachi151's Homepage  Reply with Quote
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

Edited by - malachi151 on 06/10/2014 16:53:52
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000