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:
https://us.v-cdn.net/5019921/uploads/editor/aq/gibuye6d4s87.png" width="624">
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
;
https://us.v-cdn.net/5019921/uploads/editor/kj/pbz6answk4om.png" width="746">
https://us.v-cdn.net/5019921/uploads/editor/kj/yhwi2mwj2tmk.png" width="1600">
https://us.v-cdn.net/5019921/uploads/editor/fo/dwcjglokprof.png" width="1362">
https://us.v-cdn.net/5019921/uploads/editor/4x/ghs1p51os586.png" width="1600">
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.
https://us.v-cdn.net/5019921/uploads/editor/y3/ewsq98uhn6f5.png" width="747">
https://us.v-cdn.net/5019921/uploads/editor/3s/yklxistf19e3.png" width="1600">
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