Post reply

Need Urgent Report : Script to prevent SSIS package from running on multiple servers

  • July 8, 2011 at 9:35 am

    #241145

    Hello,

    I am looking for a script or event handler that will prevent SSIS package I created from running across servers when executed on other servers when executed

    I am looking at a pre execute event handler to do this...Any ideas please..

    urgent reply

    Regards

  • July 8, 2011 at 1:00 pm

    #1350599

    A little more detail.

    You want this package to not run on any server but one particular one?

    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
    Twitter: @AnyWayDBA

  • July 8, 2011 at 1:40 pm

    #1350623

    yes just one server not on any other server ...

    trying to create a template for my org's development team

    thks

  • July 10, 2011 at 11:26 am

    #1350835

    Couldn't you add a task with @@SERVERNAME.

    If the correct server is not returned then exit the package, otherwise continue or execute a child package.

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • July 11, 2011 at 12:20 am

    #1350888

    Welsh Corgi (7/10/2011)

    Couldn't you add a task with @@SERVERNAME.

    If the correct server is not returned then exit the package, otherwise continue or execute a child package.

    That won't work unless you set the connection manager with an expression. The connection is pointed to a specific server, so @@SERVERNAME won't get you necessarily the server you want to run on, unless you configure it to point at the machine the package is currently running on.

    You can use the system variable @MachineName to find out the server the package is running on.

    Place your entire package logic in a single container. Before that container, put an empty script task. Connect the script task to the container and configure the precedence constraint to use the following expression:

    @MachineName == "MyServerName"

    Now the package will only run on the server MyServerName.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • July 11, 2011 at 3:28 am

    #1350963

    Thanks for your reply ..

    Should i give the script task a pre-execute event handler....

    Can u help out with steps please...

    Thanks

  • July 11, 2011 at 3:35 am

    #1350968

    No, not in an event handler. The pre-execute event handler is fired more than once and it doesn't control the control flow.

    Follow the steps as I described earlier:

    Place your entire package logic in a single container. Before that container, put an empty script task. Connect the script task to the container and configure the precedence constraint ...

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • July 11, 2011 at 8:56 am

    #1351133

    I have worked on the package like u saod

    placed it in a container.....

    I guess I am still on the wrong road ...

    Can u be kind enough to guide me through the steps ...

    Thanks

  • July 11, 2011 at 11:35 pm

    #1351421

    Setting Precedence Constraints on Tasks and Containers:

    http://msdn.microsoft.com/en-us/library/ms141221.aspx

    Adding Expressions to Precedence Constraints:

    http://msdn.microsoft.com/en-us/library/ms140153.aspx

    1. Place your entire package logic in a single container

    2. Before that container, put an empty script task.

    3. Connect the script task to the container using the green arrow

    4. Configure the precedence constraint with the expression by double clicking on the green arrow

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

Viewing 9 posts - 1 through 8 (of 8 total)

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