set up package separately or in a single one.

Question

set up package separately or in a single one.

sqlfriend

SSC Guru

Points: 52794
More actions
December 7, 2011 at 1:33 pm

#391065

I have ssis packages, one is to load domain tables, and others are individual pacakges to load non-domain tables data.
And we have the master package to call those packages.
My questions is: if one of the child pacakge fail, the master package will fail too.
So if a domain table load fails, all the package will fails.
Should I just mark if domain tables load completed, then go to the other pacakges?
or set up if domain tables succeed, then go to other package?
What is the best way to do this, or it's better not to do so for data integrity issue.
Thanks

Viewing 6 posts - 1 through 6 (of 6 total)

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

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 1

Wow that is a lot of questions..

Whether the master package actually fails is somewhat under your control, however, I think if any of the child packages fails it will indicate a failure to the SQL Agent.

However with that said you don't have to stop processing. You can continue processing. You talk about data integrity, it feels like we are talking about a data warehouse ETL here. If thats the case then I probably wouldn't load the fact table until the dimension tables are valid. Another possible case is called late arriving dimensions, in this case the data in the fact table shows some value indicating we don't know yet and when the dimension does arrive the fact table is updated with the correct value.

I'm not sure if I have answered your question exactly. Please let me know.

CEWII

sqlfriend SSC Guru Points: 52794 More actions · Answer 2

Thank you, in this case it is not actually a data warehouse ETL, it is just regular database daily upload.

But we need to load data in domain tables first, then load data to some other columns in the other tables.

Is it a good practice to setup if domain tables package fails, should we fail all the package- master package, or should we go on to load other tables?

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 3

sqlfriends (12/8/2011)
Thank you, in this case it is not actually a data warehouse ETL, it is just regular database daily upload.
But we need to load data in domain tables first, then load data to some other columns in the other tables.
Is it a good practice to setup if domain tables package fails, should we fail all the package- master package, or should we go on to load other tables?

As far as good practice that is a bit subjective. If the packages are restartable meaning if some data loaded in a previous step will not prevent or screw up later data loads OR will damage other data then it is probably ok to try and load them even if one of them fails. You will have to evaluate this to determine what is a proper course. As for the "master" table, I wouldn't load it if the related tables are not 100% valid. There are some cases where you can depart from it BUT your process needs to be able to tell what data has been loaded and what data hasn't. These cases often will not work with last change date delta mechanisms and are more complex to code. So basically I would probably avoid them..

CEWII