Automic Workload Automation

Expand all | Collapse all

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

Jump to Best Answer
Leonard Olteanu

Leonard Olteanu03-13-2017 10:41 AM

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

    Posted 02-13-2015 04:53 PM

    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.pnghttps://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
    ;

     

     pbz6answk4om.pnghttps://us.v-cdn.net/5019921/uploads/editor/kj/pbz6answk4om.png" width="746">

    yhwi2mwj2tmk.pnghttps://us.v-cdn.net/5019921/uploads/editor/kj/yhwi2mwj2tmk.png" width="1600">

    dwcjglokprof.pnghttps://us.v-cdn.net/5019921/uploads/editor/fo/dwcjglokprof.png" width="1362">

    ghs1p51os586.pnghttps://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.

     ewsq98uhn6f5.pnghttps://us.v-cdn.net/5019921/uploads/editor/y3/ewsq98uhn6f5.png" width="747">

     yklxistf19e3.pnghttps://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



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

    Posted 05-09-2016 05:43 PM
    LeonardOlteanu I have been asked to do a similar solution here.  I have gotten your posted code to work for me (thank you very much!) but the truncation of the error messages is really annoying.  Have you by any chance figured out how to resolve the message truncation?



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

    Posted 05-09-2016 05:47 PM
    LeonardOlteanu OK, I figured out that it was truncating the message at 50 bytes length, and then discovered this is a setting on the SQL tab of the UC4 object.  So it is very easy to alter this setting and have it return very long error messages.

    This is very cool being able to run SSIS packages synchronously, and deliver error messages when there is a problem!



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

    Posted 03-13-2017 10:39 AM
    Finally I found the reason for the strange issue with the ';'.
    In the new AWI you have to use add on top

    SQL_SET_STATEMENT_TERMINATOR TERM='@';

    and at the end

    END@

    This is done by the Java Client automatically






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

    Posted 07-06-2016 04:16 PM
    Great! I'm glad it worked for you! "set implicit_transactions off" saved me after weeks of pain trying to execute packages synchronously. We enhanced our generic SQL Server job to prompt for the environment, type of load (incremental or full load), project name and package name.
    l8bt0asxntwg.png
    nv4fbdjqi0qo.png

    Also, we made the job send an email when it fails or when it runs longer than ERT:

    ero376x1n6oz.pnghttps://us.v-cdn.net/5019921/uploads/editor/mh/ero376x1n6oz.png" width="785">

    So far it's been working great for us, with no issues whatsoever. The email warnings are particularly useful when experiencing performance degradation on the server and the job has not failed yet. If most of the jobs send warnings, then there is something not quite right on the server and the DBA can have a look before everything breaks down.


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

    Posted 03-13-2017 10:41 AM
    Great! I'm happy it finally worked for you!


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

    Posted 07-19-2016 04:14 PM
    For additional error checking, I added a report filter to do an output scan and set a bad return code if this rule is not satisfied;

    Report    REP - (Report)       Contains: Status: 7 - Succeeded


    Oh wait... I see you did the same thing.  Sorry.


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

    Posted 03-13-2017 10:40 AM
    Hi Kay,
    I'm running the SQL statements posted above directly from an Automic SQL JOBS Object, with the semicolons.
    I don't know why the semicolons cause you issues, but here is how my job object is configured:
    5kze3ixlc58o.pnghttps://us.v-cdn.net/5019921/uploads/editor/nk/5kze3ixlc58o.png" width="783">
    homks3kp74yh.pnghttps://us.v-cdn.net/5019921/uploads/editor/ar/homks3kp74yh.png" width="784">


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

    Posted 03-13-2017 10:23 AM
    Hi Leonard,
    You are right, the error message was misleading. The semicolons ';' caused the errors.
    However the T-SQL requires a semicolon at the end of the line 
    EXEC SSISDB.catalog.start_execution @execution_id
    in order to make it work in the Microsoft SQL Studio but then my SQL Agent Job failed to work.

    Are you executing the T-SQL from above directly from a Automic SQL JOBS Object or is stored as stored as 'Stored Procedure' in the SQL Server and your calling the Stored Procedure instead.

    Later works fine on myside but executing the T-SQL completely would be the better solution.


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

    Posted 05-11-2016 11:55 AM
    I have also learned I can use this command to dynamically pass parameters to my SSIS packages;

    EXEC SSISDB.catalog.set_object_parameter_value
        @object_type=30
      , @parameter_name = N'&SSISPARMNAME#'
      , @object_name = N'&SSISPACKAGE#'
      , @folder_name = N'&SSISFOLDER#'
      , @project_name = N'&SSISPROJECT#'
      , @value_type = V
      , @parameter_value = N'&SSISPARMVALUE#';




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

    Posted 05-23-2016 02:07 PM
    I was running into some (seemingly random) SSISDB table locking issues with this technique.  I've resolved the issue by adding this statement;

    set implicit_transactions off;




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

    Posted 04-29-2015 04:52 PM
    I was using Erland's awesome stored procedure beta_lockinfo together with master..sysprocesses table to figure out the locks in SSISDB database during the 30 seconds timeout, when I noticed a SQL statement issued by ISServerExec process was waiting for exclusive locks on couple of SSIS catalog tables to be released. The locks were acquired somehow by something that was happening early in the job script execution, using the main connection opened by Automic SQL Job. Then I found in Automic documentation one sentence that led to the answer:

    "Note for MS SQL Server Databases (especially in combination with transactions): SET IMPLICIT_TRANSACTIONS is set to the default value ON. Further information is available in the corresponding Microsoft documentation."

    Therefore, SSIS jobs can be executed synchronously from Automic SQL job simply by turning IMPLICIT_TRANSACTIONS OFF at the beginning of the job script. I enhanced the script to write the final execution status to the job report, so that the SQL Job can be failed if the package execution is not successful:

    set implicit_transactions off;
    Declare @execution_id bigint, @environment_id bigint, @etl_status varchar(100)
    SELECT
      @environment_id = reference_id
    FROM
      SSISDB.catalog.environment_references er WITH (NOLOCK)
    INNER JOIN
      SSISDB.catalog.projects p WITH (NOLOCK)
    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
    ;
    with execution_status as
    (
    select 'Created' as status, 1 as status_code
    union all
    select 'Running' as status, 2 as status_code
    union all
    select 'Canceled' as status, 3 as status_code
    union all
    select 'Failed' as status, 4 as status_code
    union all
    select 'Pending' as status, 5 as status_code
    union all
    select 'Ended unexpectedly' as status, 6 as status_code
    union all
    select 'Succeeded' as status, 7 as status_code
    union all
    select 'Stopping' as status, 8 as status_code
    union all
    select 'Completed' as status, 9 as status_code
    )
    SELECT
      @etl_status = 'Status: ' + cast(e.Status as varchar(2)) + ' - ' + s.Status
    FROM
      SSISDB.catalog.executions e WITH (NOLOCK)
    LEFT JOIN
      execution_status s
    on
      e.status = s.status_code
    WHERE
      e.execution_id = @execution_id
    ;
    PRINT coalesce(@etl_status, 'Unknown status');
    IF coalesce(@etl_status, 'Unknown status') <> 'Status: 7 - Succeeded'
    BEGIN
    SELECT
      e.execution_id
      , e.environment_name + '\' + e.folder_name + '\' + e.project_name + '\' + e.package_name as Package
      , m.Package_Path
      , m.Message
    FROM
      SSISDB.catalog.executions e WITH (NOLOCK)
    LEFT JOIN
      SSISDB.catalog.operations o WITH (NOLOCK)
    ON
      e.process_id = o.process_id
    LEFT JOIN
      SSISDB.catalog.event_messages m WITH (NOLOCK)
    ON
      o.operation_id = m.operation_id
    WHERE
      e.execution_id = @execution_id
      and m.Event_Name = 'OnError'
    ORDER BY
      m.event_message_id
    END
    The final status is then captured by an output filter...

    6mc3lx4317fv.pnghttps://us.v-cdn.net/5019921/uploads/editor/ap/6mc3lx4317fv.png" width="496">

    ... in the Output Scan tab of SQL Job:

    e731p5si10gx.pnghttps://us.v-cdn.net/5019921/uploads/editor/rp/e731p5si10gx.png" width="789">

    The final IF construct in the job script writes all SSIS OnError event messages to the output (job report) in case of failure. Unfortunately, the messages are truncated in the job report, so you still have to go to SQL Server Management Studio for more details (by running package execution report in SSMS).


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

    Posted 02-17-2015 01:47 PM
    I've never been a SQLServer developer.  I'm just sharing how I think we do things here in case it might give you some helpful ideas;

    We don't run our SQLServer solutions directly.  Instead we run them via SQLServer jobs.  Someone wrote a generic package that is passed a job name, launches that job, and loops every 60 seconds to check for job completion of that job before returning control to Automic.

    This technique makes it difficult to pass parameters to the SQLServer application, so we do that via a parameter table.  Automic can update it with values that the application can read.


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

    Posted 02-27-2018 02:22 PM
    I ran into a use-case this week where the SQL statement to retrieve the environment_id was returning a false hit.  I was able to correct the problem by adding a folder_id restriction to the statement like so;

    SELECT
      @environment_id = reference_id
    FROM
      SSISDB.catalog.environment_references er WITH (NOLOCK)
    INNER JOIN
      SSISDB.catalog.projects p WITH (NOLOCK)
    ON
      p.project_id = er.project_id
    WHERE
      er.environment_name = N'&ETLENV#'
      AND p.name = N'&ETLPROJ#'   AND folder_id = (select folder_id from SSISDB.internal.folders where name = '&ETLFOLD#')
    ;


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

    Posted 12-12-2017 02:37 AM
    One remark on Pete answer on SSIS parameters. Using set_object_parameter_value to set SSIS parameters changes the default values of the Package but the values does to not get used by the current Package execution.
    In order to set the variable of the current Package execution you have to use 'set_execution_parameter' instead.

    EXEC SSISDB.catalog.set_execution_parameter_value  @execution_id
      , @object_type=20
      , @parameter_name=N'<Name of the parameter>'
      , @parameter_value=N'<Value (String)>'

    By the way 'object_type' 20 set parameters defined on the 'SSIS Package' while the type 30 set the parameters defined on the 'Project'.
    You find more about parameters, type conversion in the latest release of the SSIS Action Pack on the Marketplace


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

    Posted 02-18-2015 11:29 AM
    Thank you Pete for suggesting the MS SQL Server Job solution! However, this solution involves too many moving parts (create SQL Server Agent jobs, set up proxies, find ways to pass parameters from Automic down to the job step, etc.) which would greatly complicate our deployment/support/maintenance procedures. 

    Alternatively, we also thought about running the package asynchronously from Automic and then using DB events to capture the final package execution status. Although it seems simpler than using MS SQL Server Jobs, we find this alternative also too complicated to be easily maintained/replicated/customized from job to job. At most, this might become a temporary workaround for lack of better choice, but we don't consider it a long term solution.

    Since Microsoft allows synchronous execution of the packages simply by setting one parameter, we would like to pursue this route which proves to be very simple and flexible to us. Until we completely understand the issue we are facing here, we are not ready to give up this approach.

    Leonard


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

    Posted 02-27-2018 02:56 PM
    Thanks Pete! You are correct, the folder has to be included in the filter. I had a look at my SQL and it is slightly different from the SQL in my original post. I think I must have updated it at some point in time to include the folder in the where clause. We decided to have one environment per folder, with the folder name being the same as the environment name.  It helps in terms of change management by enforcing a degree of isolation between different environments. Thus, we have to deploy the project code from one environment (folder) to another  environment (folder), instead of running the same project code with different SSIS environments defined in the same folder. The SQL statement we use to retrieve the environment_id is:
    select
         er.reference_id
    from
         catalog.environment_references er with (nolock)
    left join
         catalog.projects p
    with (nolock)
    on
         er.project_id = p.project_id
    where
         er.environment_folder_name = N'&ETLENV#'
         and er.environment_name = N'&ETLENV#'
         and p.name = N'&ETLPROJ#'
    ;



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

    Posted 03-01-2017 10:18 AM
    Unfortunately we are not on the latest versions like in your case to test in identical setting, but I believe this might be a SQL Server thing regardless of the version.

    @execution_id variable is declared at the beginning of the batch, and it works just fine in our case. The error you get makes me think you execute SSIS stored procedures in a different context.
    Are you using EXEC command or EXEC() function? Please see https://technet.microsoft.com/en-us/library/aa175921(v=sql.80).aspx, particularly the section titled 'EXEC() in a nutshell' towards the end of the article, to see the difference between the two.

    EXEC command executes a command string or character string within a Transact-SQL batch. EXEC() function executes whatever you pass between brackets in a different context, which might mean in your case that @execution_id variable is unknown in that execution context because is defined outside the brackets in the caller context.

    In other words, make sure you do something like this:
    EXEC SSISDB.catalog.start_execution @execution_id
    and not
    EXEC('SSISDB.catalog.start_execution @execution_id')
    Generally speaking, make sure you execute all T-SQL statements in the same batch and it should work.


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

    Posted 02-27-2018 05:16 PM
    We too are configured with multiple folders for the different logical environments.  The new use-case we ran into is that we want Automic to be able to dynamically switch the parameters within a single environment.  We accomplished this by having more than one environment object per folder and you can chose the desired environment object from a promptset drop down list.  However some of these additional environment object names matched the environment object names in other folders, thus we needed to filter the query by folder_id.  (Its all really slick, when it all works!)


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

    Posted 07-19-2016 04:21 PM
    Yup, you have to use an output filter because the SSIS process will finish successfully (system return code 0) even if the actual ETL process fails. Automic will only capture the system return code and will report success in all cases, but the report output filter will tell if the ETL did fail. Please see my remarks in the April 2015 post above.


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

    Posted 03-01-2017 07:58 AM
    When we are trying to use this solution we with SSIS 2016 but we are receiving the JDBC error message
    Must declare the scalar variable "@execution_id"

    We have copied the code one by one.  We are using Java 8, and AE v12


  • 22.  RE: How to execute SQL Server Data Tools (SSDT) 2012 package synchronously using SQL Job?
    Best Answer

    Posted 02-04-2020 11:23 AM
    Hello,

    First of all, many thanks to all for this wonderful thread ! I work in a IT Data department and implement Automic for executing every packages of a DataWarehouse solution. Everything work properly for now, either using SSIS environment or overriding some of them with .VARA using ascending hierarchy.

    I just want to tell you that this statement make fail the script if the package execution failed :

    IF
    (
    SELECT [status]
    FROM [SSISDB].[catalog].[executions]
    WHERE execution_id = @execution_id
    ) <> 7
    BEGIN
    RAISERROR('*** The package failed. Check the SSIS catalog logs for more information ***', 16, 1);
    END;​


    With this code, the JOBS fail by himself and you don't need to catch the string in the Report Post-Process.

    I hope my message could help.


  • 23.  RE: How to execute SQL Server Data Tools (SSDT) 2012 package synchronously using SQL Job?

    Posted 02-05-2020 12:16 PM
    Thanks for the update, Axel! You are right, this is a simpler way to catch a package failure!
    It might be good to output the package errors before RAISERROR, like in the original posted script. We find it is really useful to see package errors in the Automic job report, so we don't have to look for them in other places.
    This solution was developed in Automic v10 for SQL Server 2012; now it works unchanged in Automic v12.2 for SQL Server 2016.

    ------------------------------
    Cheers,
    Leonard
    ------------------------------



  • 24.  RE: How to execute SQL Server Data Tools (SSDT) 2012 package synchronously using SQL Job?

    Posted 03-18-2020 09:14 AM
    Hello all,

    I'm facing a new "challenge" : When I Cancel a running task, I need to send a "stop execution" to the SSIS server for the related package. In other words, I need to send command : EXEC SSISDB.catalog.stop_operation @operation_id = 65, where 65 is @execution_id of the running package execution.

    Could you share your experience on this case or telle me how I can RSET the @execution_id while the task is running ?

    Thanks a lot for your help.
    ​​​​


  • 25.  RE: How to execute SQL Server Data Tools (SSDT) 2012 package synchronously using SQL Job?

    Posted 03-18-2020 02:13 PM
    Thank you for you answer Leonard.

    I tried this solution, either with a PRINT statement and with a SELECT @execution_id statement.
    But the problem is, as execution is in Synchronous mode, PRINT and SELECT results are shown only after the package execution has finished, whatever the status. When I cancel a running task in UC4, package never finish on SSIS side, and PRINT or SELECT results are never shown.
    The best way should be to print execution_id in a "step" BEFORE the "EXEC SSISDB.catalog.start_execution @execution_id;".

    Thank you for your nice advises.
    ​​


  • 26.  RE: How to execute SQL Server Data Tools (SSDT) 2012 package synchronously using SQL Job?

    Posted 03-18-2020 10:22 AM
    Hi Axel,
    So far, we had no need for this functionality, but I see the value in being able to cancel a package execution when the Automic task is cancelled. One way I could think of doing this is to print the execution id in the Form tab:
    PRINT 'EXECUTION_ID: ' + coalesce(@execution_id, 'Unknown');
    then retrieve it in post process tab using PREP_PROCESS_REPORT.
    In the post process tab you could implement some logic to start another JOBS which runs SSISDB.catalog.stop_operation if the current task status is ENDED_CANCEL and the execution id is not 'Unknown'. I haven't tried this, but it seems doable to me. Please share your solution here if you manage to get it working.

    ------------------------------
    Cheers,
    Leonard
    ------------------------------



  • 27.  RE: How to execute SQL Server Data Tools (SSDT) 2012 package synchronously using SQL Job?

    Posted 03-18-2020 04:02 PM
    I had a quick and dirty experiment breaking the SQL into two commands (steps), like you suggested. The first step creates the execution, then it prints the execution id to job report. The second step does the rest. The job failed with this error: Must declare the scalar variable "@execution_id". This tells me that the SQL variable @execution_id that was set in the first step is not visible in the second step. Further proof is that the execution id got written in the job report by the first step, so the variable did get initiated. It looks like there is no way to get the execution id in an Automic variable when the job is cancelled.

    But there might be another more elegant way to do it by passing the Automic run id to the package execution. This way you can link the Automic task to the SSIS execution. However, it involves a minor change to any package that is to be executed from Automic:
    1. In the SSIS package, create a numeric package parameter, let's call it CALLER_TASK_ID
    2. In SQL JOBS pre-process tab have this line:
      :SET &RUNNR# = SYS_ACT_ME_NR()
    3. In the JOBS process tab (Form tab) add this:
      EXEC SSISDB.catalog.set_execution_parameter_value
      @execution_id
      , @object_type=30  -- this means package parameter
      , @parameter_name=N'CALLER_TASK_ID'
      , @parameter_value=&RUNNR#
      ;
      I really do hope that &RUNNR# get resolved to the actual id number. If not, you might find a good way to pass that number in SQL
    4. Whenever and wherever needed, retrieve the execution id from the SSIS catalog like this:
      SELECT execution_id FROM SSISDB.catalog.execution_parameter_values WHERE parameter_name = 'CALLER_TASK_ID' and parameter_value = &RUNNR#
    I hope all this makes sense. The main idea is to have an ID exchange between Automic and SSIS, so you can retrieve the execution id from SSIS based on the id of the package executing task in Automic.


    ------------------------------
    Cheers,
    Leonard
    ------------------------------