A Custom Execution Method – Level 19 of the Stairway to Integration Services

  • Andy Leonard

    SSChampion

    Points: 10024

    Comments posted to this topic are about the item A Custom Execution Method – Level 19 of the Stairway to Integration Services

    Andy Leonard, Chief Data Engineer, Enterprise Data & Analytics

  • Phil Parkin

    SSC Guru

    Points: 244733

    Nice article Andy. I have recently written a stored proc which looks very like yours.

    The differences:

    1) It lives in a separate library-type database (I want it clean and fully source-controlled)

    2) It uses sp_executeSQL to execute procs in SSISDB

    (To avoid cross-database warnings in my database project. SSISDB is, no doubt for good reason, classed as a 'user' database and cannot therefore be added as a system database reference in a database project. I don't want to add SSISDB to source control, so we came up with this workaround.)

    3) It runs the package in SYNCHRONIZED mode, then grabs the execution status from catalog.executions and THROWs an error if the status is not 'Succeeded' (7)

    That's because I want the proc to fail if the package fails. Running the proc asynchronously does not capture that.

    This is important to us because the proc will be executing multiple packages to run ETL jobs.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • Peter Schott

    SSCrazy Eights

    Points: 9613

    Phil, that sounds like an interesting workaround. It may be possible to edit the files in the SSISDB so that they show as a system database, but as it shows as a "User" database normally, I don't know that this would be ideal. I like the idea of having a separate project to store these and can see the usefulness of running in Synchronized mode for much of this.

    I appreciate the original article as well - much food for thought there and some good ideas for future development efforts.

  • Jack Corbett

    SSC Guru

    Points: 184381

    Good article Andy. My only nitpick, is that while you can't use foreign keys to enforce referential integrity when using an additional database, technically you could create triggers in the additional database to partially enforce RI. You couldn't fully enforce RI with triggers without adding triggers to the SSISDB database. I'm not recommending you do this, just making the comment.

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply