Get exit code - package executed from SSIS catalog

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

SSIS and Import/Export (2012)

Get exit code - package executed from SSIS catalog

Author

Topic

Peffa
Starting Member

4 Posts

Posted - 2013-04-10 : 03:20:21

I’m new to SSIS/SQL so the following naming references might be incorrect but I hope to convey the gist of the issue

The scheduling tool I’m using executes deployed SSIS packages in SQL 2012 and this tool needs to indicate when a SSIS package fails and then stop running any subsequent scheduled jobs.

The project uses the SSIS project deployment model in SQL 2012. The deployed SSIS packages are then called by a 3rd party scheduling tool. In the SSIS catalogue we use the Execute option to generate a SQL script to pass to the scheduler. This script is edited to add a parameter to ensure that the job runs SYNSCHRONOUSLY (i.e. the caller keeps waiting till the job is finished). The SQL script is run from the scheduling tool and will only move to the next job upon completion.

The issue is that the scheduling tool is not receiving a return code when the SSIS package fails. If a SSIS package fails it contains a step to capture and send an email with error notification, hence we do have a view on failures. However any dependant jobs in the scheduling flow are also run irrespective of whether the job has completed successfully or not. Is there a parameter to force a return code to be sent to the 3rd part scheduling tool?

Example of the script being used to execute the package:


Declare @execution_id bigint
EXEC [SSISDB].[catalog].[create_execution] @package_name=N'Extract_Job.dtsx',  @execution_id=@execution_id OUTPUT, @folder_name=N'ETL', @project_name=N'ETL', @use32bitruntime=False, @reference_id=Null
Select @execution_id
DECLARE @var0 smallint = 1
EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id,  @object_type=50, @parameter_name=N'LOGGING_LEVEL', @parameter_value=@var0
EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id,  @object_type=50, @parameter_name=N'SYNCHRONIZED',  @parameter_value= 1; -- turn on synchronized execution
EXEC [SSISDB].[catalog].[start_execution] @execution_id
GO

Things I’ve tried

Using the DTSEXEC command. This however doesn’t send a return code and also runs synchronously and it needs to run synchronously
The 3rd party scheduler has a plugin for the SSIS catalogue but has an unresolved bug so isn’t working on the current version

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-10 : 04:38:56

you can make use of [catalog] .[executions] table to get this information. It has a column called status which will indicate the status of package execution. you can pass the execution_id as a parameter to get corresponding package execution status. then save it in a variable and do a if check on variable value before you execute the rest of the jobs

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Peffa
Starting Member

4 Posts

Posted - 2013-04-10 : 08:12:44

Thanks, that does seem to return the info I want however I need it to return the exit code back to the calling program as part of the execution.
I'll try including it as part of the call.

quote:
Originally posted by visakh16

you can make use of [catalog] .[executions] table to get this

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-11 : 02:12:02

how is calling program invoking package execution? If possible keep package calling statement inside a procedure. then declare an output parameter through which you can return the exit code back to calling app

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

scurrp
Starting Member

1 Post

Posted - 2013-05-06 : 00:40:53

I've noticed the same thing when executing project packages.
Individual tasks on sub packages can fail but the overall execution reports success.
Not very helpful, looking for an improved fix.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-06 : 02:10:59

quote:
Originally posted by scurrp

I've noticed the same thing when executing project packages.
Individual tasks on sub packages can fail but the overall execution reports success.
Not very helpful, looking for an improved fix.

Check values set for FailParentOnFailure and FailPackageONFailure properties for the package.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Peffa
Starting Member

4 Posts

Posted - 2013-05-06 : 02:21:37

This is the solution that I've opted for.

While using T-SQL to execute the SSIS package does seem to work however it seems DTExec is still more suitable and stable. I found using the new version of DTExec does have an option (poorly documented) to pass a parameter to allow synchronous processing. Using this method also allows the exit code to be returned which is the problem I was experiencing with the T-SQL option.

Example of script:

DTExec.exe /ISSERVER "\SSISDB\project\package_name.dtsx" /SERVER "server_name" /Par "$ServerOption::SYNCHRONIZED(Boolean)";True

The older version of dtexec will not support the new options, ensure that you're executing the new version if both are available on the server. To check which version is in the search path (default) use "WHERE DTEXEC". Type "DTEXEC /?" and check if the new options appear in the help text, if not, prefix the command execution with the correct path location.

New version location
C:\Program Files\Microsoft SQL Server\110\DTS\Binn\DTExec.exe
Older version location
C:\Program Files (x86)\Microsoft SQL Server\110\DTS\Binn\DTExec.exe

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-06 : 02:55:49

so is your machine 64 bit? seems like it is from your explanation

it seems you were calling 32 bit dtexec before (one under x 86) and now you're calling 64bit

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Peffa
Starting Member

4 Posts

Posted - 2013-05-06 : 03:55:48

This is a 64bit machine but the initial issue wasn't that it was calling the incorrect version but that DTExec & the T-SQL didn't give a return code. I mention the 32bit version as you get errors if using the new parameters with the older version and the info was listed to assist with debugging if anyone runs into a similar issue.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-06 : 04:43:54

Ok ok...I understand

But this is a new addition in SSIS 2012. In all previous versions package will only execute locally. The server based synchronous execution option is not available for them.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

quistdba
Starting Member

4 Posts

Posted - 2013-09-18 : 16:14:11

When I used /Par "$ServerOption::SYNCHRONIZED(Boolean)";True, I am only able to kick off 4 packages simultaneously versus 15+ without this parameter. It times out attempting to connect to the environment if I try to run more than 4 packages at once. Has anyone else experienced this issue?

quistdba
Starting Member

4 Posts

Posted - 2013-09-18 : 16:15:20

quistdba
Starting Member

4 Posts

Posted - 2013-09-18 : 16:15:24

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources