SQL SSIS How many Steps is too much

I have an SSIS package that has 27 steps, ranging from table truncating, 开发者_如何学Cto data importation,transformation, and a bunch of sql queries. My question is, is it better practice to keep them all in one package, or to separate the steps into individual packages?

---

My general advice is to make the packages as concise and focused as possible but no smaller. Helpful, I know so here are some of my criteria on deciding if it's a new package or fits the current one.

• If the 27 step package blows up on step 26, are you humped? Can it gracefully restart or will it require multiple hours of cleanup?

If the answer is cleanup required, then I'd decompose the package into a series of smaller packages until they satisfy the restartability criteria.

• Is there logic in the package for an infrequent branch? e.g. I've seen packages that had a date check, if it was the purge weekend (once a year), it executed this special branch of code that was overly convoluted, complex and just sucked. That should have never been in a package that runs daily. Another example of this was a job that daily sales. Another team attempted to add in logic that also computed month end inventory totals. Completely different subject areas and the wrong schedule to boot but they thought it was faster than spinning up a new package.

• Assuming 2008+, do you need to share the results of a lookup cache? If so, the data flows need to be in the same package.