LeonardOlteanu

How to execute SQL Server Data Tools (SSDT) 2012 package synchronously using SQL Job?

Discussion created by LeonardOlteanu on Feb 13, 2015
Latest reply on Feb 27, 2018 by Pete Wirfs

Could someone please help with automating the execution of a SQL Server Data Tools (SSDT) 2012 packagedeployed to Integration Services Catalog SSISDB (via project deployment model)using a SQL job in Automic? This package updates a Data Warehouse table.

 Our environment:

AutomationEngine: 10.0.3+hf.2.build.925

SQL Agent: 10.0.3+build.663

 SQL Agent, DW and Integration Services Catalog are all on the same server.

AutomationEngine and its back-end database run on a different server.

 The SQL Agent runs under an Active Directory service account and is configured to use Windows authentication when connecting to SQL Server databases SSISDB and DW on the server. This service account has ssis_admin role and it also has read/write access to DW tables:

 gibuye6d4s87.png

We have to use the package execution parameter ‘SYNCHRONIZED’ set to True (@parameter_value=1) so that the SQL Job waits for the package execution to finish. The SQL Job runs successfully in Automic even though the package execution fails with Unexpected Termination status.

There are two issues here:

1. We are unable to make package execution successful in synchronous mode
2. At least, the SQL Job should fail in Automic if the package fails with Unexpected Termination status.

 The SQL Job script is as follows:

Declare @execution_id bigint, @environment_id bigint
SELECT
  @environment_id = reference_id
FROM
  SSISDB.catalog.environment_references er
INNER JOIN
  SSISDB.catalog.projects p
ON
  p.project_id = er.project_id
WHERE
  er.environment_name = N'&ETLENV#'
  AND p.name = N'&ETLPROJ#'
;
EXEC SSISDB.catalog.create_execution
  @package_name=N'&ETLPKG#'
  , @execution_id=@execution_id OUTPUT
  , @folder_name=N'&ETLFOLD#'
  , @project_name=N'&ETLPROJ#'
  , @use32bitruntime=False
  , @reference_id=@environment_id
;
EXEC SSISDB.catalog.set_execution_parameter_value
  @execution_id
  , @object_type=50
  , @parameter_name=N'SYNCHRONIZED'
  , @parameter_value=1
;
EXEC SSISDB.catalog.start_execution @execution_id
;

 

 pbz6answk4om.png

yhwi2mwj2tmk.png

dwcjglokprof.png

ghs1p51os586.png

 The event log on the server shows this error:

 The SSIS Execution Process could not write to the IS catalog: ######SQLDEV:SSISDB Error details: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.; at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData() at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader() at Microsoft.SqlServer.IntegrationServices.Server.Shared.ExecutionSpecifier.CheckParameter(ServerOperationStatus status) at Microsoft.SqlServer.IntegrationServices.Server.ISServerExec.ProjectOperator.PerformOperation()


 If we set SYNCHRONIZED parameter to default value False (@parameter_value=0), then the SQL Job returns success without waiting for the package to finish. Moments later, the package execution is also successful.

 ewsq98uhn6f5.png

 yklxistf19e3.png

 In conclusion, we cannot run ETL packages in synchronous mode from Automic although they run just fine in asynchronous mode. Any thoughts?

Thank you,

Leonard

Outcomes