dts package not triggering next package 1

[kirstyn]

Hi All

I'm hoping someone can help as this is driving me mad.
I have a number of sql server 2000 DTS packages (20) which i have moved to a new server.
All work perfectly on this server when scheduled.

I have nested these packages into 4 master packages
e.g. master package A contains packages 1 to 5
etc
All these master packages work perfectly on the server when scheduled.

I have further nested the packages into one overall package.
Within this A should start B on completion, B should start C on completion and C should start D on completion.

When i run this A and B will complete but package C does not run.
As far as i can see B completes and suceeds. I have even put a sql task on the end of B to see if it suceeds - and it does. But i can not get package C to run after A and B.

I can get B then C to run if A is missed out.

This overall package runs on the development server - why won't it on the production server.

I can see no reason for this. Error and package logging does not show any problems.
I am completely stuck. I dont want to schedule them separately as some need to complete before others. I dont want to put all 20 into 1 package as, in time, others will be added in the chain, so splitting will make maintenance easier.

Suggestions please

krn.

 

[mutley1]

Hi Kirstyn

Not sure as to why the packages are not executing one after the other if you have an "on completion" set. I have seen the problem before but don't think we ever got to the bottom of nested packages not calling each other.

Only one suggestion if you can't get them to run through nesting:

1. Create a job for each of the 5 packages (i.e. schedule each one).
2. Don't put a time on jobs 2 - 5.
3. Use job steps in each job to call the next package (as a job).

I.E. if you have 5 packages, create a job for each one, called Job 1, Job 2, Job 3 etc.....
Schedule job 1 for when you want it to start, then use another jobstep to call sp_start_job on success of job 1.

USE msdb
EXEC sp_start_job @job_name = 'Job 2'

within job 2, add a step to start job 3....etc.etc.

HTH,

M.

 

[kirstyn]

Thanks Mutley1

That worked a treat.

If anyone knows why it was doing this i'd still love to know.

krn

 

[mutley1]

Hi Kirstyn,

You're welcome.

As I said, I've had this before but left the company before we got to the bottom of it. You've listed all the main stuff I would pointout as in it's all set to "on completion" as opposed to "on success" (which is a bummer if you need all parts to succeed anyway!!) but should make it run through.

Nothing in the error logs - strange so no point in going there (actually, if you could post 1 then might be worth a look). Also, I assume the account on live has all the relevant permissions to everything (including directories if you're grabbing or writing flat files etc.

Only other thing I can think of is that you said the Dev box process works fine. How did you get the package on the live box? Did you copy it over from Dev or create them side by side?

Apologies if this is how you did it, but just worth trying if it isn't:

Might be worth opening each of the 20 packages on Dev and saving them to the live server. Then open each of the bundled packages that calls the individual ones and save them to the live server. Finally save your master master (!) using the same method. Don't know if you'll need to change any passwords or anything if there are any specifics that are different from Dev to live.

As I say, sorry if thats the way you got it from Dev to Live but just something to try.

Cheers,

M.

 

[kirstyn]

Hi Mutley1

The account on live does have all the relevant permissions - individually the 20 packages will all schedule and complete. Have had the odd Mapi error - but have those sorted now.

One problem is that i have full admin rights on the dev server but not on the production server - so spent a bit of time thinking it was permissions.

To get it on the production server i did open and save each package. Then i went in and changed any connections pointing to the dev server to the production server (probably a better way of doing this but i didnt think about it).

Have tried recreating the master packages with no joy.
Am beginning to suspect there might be a limit on the number of subpackages that can be run (9).
When i change the order of packages, ones at the start run, but ones later in the order fail.
Even tried just creating a master package with just one package in multiple times. That ran up until the 10th instance - then stopped.

Have looked everywhere i can think of to see if there is a setting that limits the number of subpackages but cant see one.

The package log gives a run status of 0 for the failing package.

Thanks for your help

krn

 

[mutley1]

Kirstyn,

Do the packages need to be run in order or can the execute in parallel? I.e. are any reliant on the others having completed?

Cheers,

M.

 

[kirstyn]

Hi Mutley

The first 2 need to run in order and the 3rd and 4th after them on successful completion. So 3 and 4 can run in parallel, but havent been scheduled that way (mostly cos not sure the dev server would cope).

Package 1 creates some snapshots of tables.
package 2 imports data for lookup tables
package 3 import telephony data
package 4 imports resourcing info.

The plan is that other data will be added as later packages or as additions to existing master packages.

cheers
krn

 

[mutley1]

Another quick question - if you open the MASTER master in live using DTS designer and run it manually, what happens? It should show the 5 steps:

Execute mast pack A - shows running / completed
Execute mast pack B - shows running / completed
Execute mast pack C - shows running / completed
Execute mast pack D - shows running / completed
Execute mast pack E - shows running / completed

Does it just stop after B ans shoe C, D and E as not run (the black circle with the X in it)?

 

[mutley1]

How many tasks are in each of the original 20?

When you go into the package, click on properties and in the bottom right of the "General" tab there is a limit as to the number of tasks executed in parallel. I wouldn't think this is a problem for each of the 20, but you might want to change this to 5 in each of the master packages (A, B, C, D and E), then reduce it in the MASTER master to 1 so it ensures it runs 1 at a time and see if that forces it handle them 1 at a time.

Let me know if that has any effect.

Cheers,

M.

 

[kirstyn]

It did when first migrated,
but since then the mapi logins were changed to the one sqlagent uses, so when i run it now it doesnt complete. (My login on the live is not the same as the dba's so the email will work for one but not the other - getting full permission on the server is not an option)
So its not running through all the packages in full.

One of the attempts at getting it running meant i changed it all to execute on main thread - to no effect.


any help?

krn

 

[kirstyn]

Hi

The tasks in the original 20 vary. One of the larger ones has 9 transform data task, 18 sql tasks and 9 vb script tasks.

I've justtried altering the number of tasks executing in parallel - no joy i'm afraid.

krn

 

[mutley1]

HHHmmmm........strange one. I'll have to think some more unless some kind soul pops an answer up. If I don't get back to you, apologies, but at least the job steps will get you running!!

Cheers,

M.

 

[kirstyn]

Thanks for all your help.
I'm chuffed i can get it running at all - as was at the point of giving up.

happy holidays!

kirsty

 

[mutley1]

You're welcome - happy hols to you too!