[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


SSIS package running SSIS package on linked server

Posted on 2009-04-21
Medium Priority
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
  • 2
LVL 11

Expert Comment

ID: 24193348
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

DerekRoberts earned 0 total points
ID: 24267757
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

ID: 24269327
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 Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

834 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