SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
Andy Leonard
Andy Leonard
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2917 Visits: 1105
Comments posted to this topic are about the item A Custom Execution Method – Level 19 of the Stairway to Integration Services

Andy Leonard
Data Philosopher, Enterprise Data & Analytics
Phil Parkin
Phil Parkin
SSC Guru
SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)

Group: General Forum Members
Points: 69315 Visits: 21434
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.


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

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.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
Peter Schott
Peter Schott
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3360 Visits: 1941
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
  Jack Corbett
SSC Guru
SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)

Group: General Forum Members
Points: 57781 Visits: 14927
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

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search