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 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 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.


  • 3.  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


  • 4.  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).


  • 5.  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?



  • 6.  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!



  • 7.  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#';




  • 8.  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;




  • 9.  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.


  • 10.  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.


  • 11.  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.


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

    Posted 20 days ago
    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.


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

    Posted 19 days ago
    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
    ------------------------------



  • 14.  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


  • 15.  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.


  • 16.  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.


  • 17.  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






  • 18.  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">


  • 19.  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!


  • 20.  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


  • 21.  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#')
    ;


  • 22.  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#'
    ;



  • 23.  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!)