locked
SQL 2012 Integration Services Catalog and DTEXEC RRS feed

  • Question

  • Hi, 

    We are deploying projects to the SQL 2012 Integration Services Catalog and then turning the execution of those jobs over to an operations team who use a scheduling and automation tool to execute the jobs on a schedule using DTEXEC commands.

    Prior to SQL 2012, when they did execute such projects, the automation tool was able to capture results from the job execution that included results like 'DTSER_SUCCESS'.  

    All we get now seems not to even include a success or failure?

    • Microsoft (R) SQL Server Execute Package Utility
      Version 11.0.2100.60 for 64-bit
      Copyright (C) Microsoft Corporation. All rights reserved.
      Started:  3:15:48 PM
      Execution ID: 20536.
      To view the details for the execution, right-click on the Integration Services Catalog, and open the [All Executions] report
      Started:  3:15:48 PM
      Finished: 3:15:51 PM
      Elapsed:  2.73 seconds
    I've read some on Logging Levels and What Events are Included in the SSIS Catalog Log Levels as well as "/Par "$ServerOption::LOGGING_LEVEL(Int32)";3" but all of those options still make it necessary for the operator to go either query a table like [catalog].[operation_messages] or run the All Executions report?

    Is there some other way to get basic success / failure when using the DTEXEC tool?

    Thanks
    Matt

    Tuesday, December 10, 2013 10:58 PM

Answers

  • Hi Matt.

    Apparently with the advent of the SSISDB there are no more DTSER_SUCCESSs.

    Your process obviously has to change. Perhaps reading the sysssisslog log table makes a better sense.


    Arthur My Blog

    • Marked as answer by matthewf.boi Friday, June 13, 2014 8:07 PM
    Wednesday, December 11, 2013 4:27 PM

All replies

  • Hi Matt.

    Apparently with the advent of the SSISDB there are no more DTSER_SUCCESSs.

    Your process obviously has to change. Perhaps reading the sysssisslog log table makes a better sense.


    Arthur My Blog

    • Marked as answer by matthewf.boi Friday, June 13, 2014 8:07 PM
    Wednesday, December 11, 2013 4:27 PM
  • I wonder if this parameter will work for you: $ServerOption::SYNCHRONIZED

    I can't find what its definition is.

    http://www.mattmasson.com/2013/06/server-execution-parameters-with-dtexec/

    http://blogs.msdn.com/b/mattm/archive/2011/07/24/using-dtexec-with-packages-on-the-is-server.aspx

    http://technet.microsoft.com/en-us/library/hh231187.aspx

    http://amortizes.rssing.com/chan-2090545/all_p4.html


    Russel Loski, MCT, MCSE Data Platform/Business Intelligence. Twitter: @sqlmovers; blog: www.sqlmovers.com

    Wednesday, December 11, 2013 7:01 PM
  • I just find it interesting that on the dtexec pages on technet (http://technet.microsoft.com/en-us/library/hh231187.aspx) they still say "You can use dtexec with third-party scheduling tools to schedule packages that are deployed to the Integration Services server"

    Which i guess is technically true, you just cant use the third-party tool to tell if the job was successful or not.

    Minor details sometimes count.

    Wednesday, December 11, 2013 8:11 PM
  • Russel,

    from what i can tell the $ServerOption::SYNCHRONIZED parameter changes the package execution from asynchronus (default) to synchronous.  In async mode the individual process will return immediately and you won't get results from the whole package.  In Synchronous mode the dtexec process will not return until the whole package has completed.

    Would be important if you want step 2 to not start until step 1 is complete.

    I get that info from the book "SQL Server 2012 Integration Services Design Patterns".  Same book also mentions that "another difference between SSIS Catalog and other forms of DTEXEC execution is that the events that occur while the package is running are not displayed...there is only a single message there... pointing you to the catalog reports.".  THAT is really my issue.

    Thanks!

    Matt

    Wednesday, December 11, 2013 8:26 PM
  • Depends on a tool. One client I had used Tivoli (from IBM) if I log a certain message to the Windows App Event log they pick it up and report on failure.

    Arthur My Blog

    Wednesday, December 11, 2013 8:27 PM