SSIS package running SSIS package on linked server

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
DerekRobertsAsked:
Who is Participating?
 
DerekRobertsConnect With a Mentor Author Commented:
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
0
 
aaronakinCommented:
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.

or

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.
0
 
aaronakinCommented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.