SSIS package running SSIS package on linked server

Posted on 2009-04-21
Last Modified: 2013-11-10
Hi everyone
I have 2 linked servers, each with their own SSIS packages. I wish to 'link' them so effectively I have one 'fire and forget' process as opposed to 3 separate 'parts' (which I'll have to schedule separately);

packet 1 (server 1) runs from a scheduled task,
On completion packet 2 is run (on server 2)
On completion packet 3 is run (on Server 1)

I've spent the morning trying to configure the xp_cmdshell option...but cannot get it working (I've added the permissions under the SA account for the account it's running in on the first server)

IS there an easier way to do this? I assume one of you Experts have encountered a similar issue before.

Any assistance would be gratefully received

Many thanks
Question by:DerekRoberts
    LVL 11

    Expert Comment

    You can do one of two things...

    1. Create a new package that has 3 Execute Package tasks in it, one for each of the 3 packages on both servers.


    2. In the scheduled job on Server 1, create 3 steps and choose SQL Server Integrated Services Package as the type (instead of the default Transact-SQL Script type).  Have each step call one of the packages on either server.

    Accepted Solution

    Hey Aaronkin

    thanks for your suggestions but I managed to solve it myself.

    The way I eventually solved this was to create a 'trigger table' on one of the servers. I gave permissions to the other to read/write to this.

    At the end of each SSIS package, I got it to add GETDATE() into an update query to report back to the trigger table.
    The next package then has a Count query (which writes its result to a variable) and a check constraint against it (the constraint then looks at the value of the variable) to check to see the previous line in has a date filled, if it does  then the constraint = true and the packet continues, if false then it stops.

    Each package is on a schedule to run every 5 mins between certain times

    Thats how I solved it
    LVL 11

    Expert Comment

    That seems like a lot more work than the solutions I provided and not as reliable since the packages are still independent, which might cause probles in the future, but as long as it works for you.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    A quick step-by-step overview of installing and configuring Carbonite Server Backup.
    Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
    Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
    Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now