September 8, 2008 at 5:01 am
Another question regarding development.
reading various material, it's clear that the best way to develop SSIS packages, is to develop seperate packages for each logical process rather than big packages.
I need to create extract routines for several data tables to keep a trace of records at a given point in point, that would no longer be available.
The extract would also rely upon some date and time variable to extract the data.
Could I have some opions on what you think would be best:
Option a) Create a seperate package for each data extract, which would mean running a stored procedure to set up the date variables for each package, prior to the data flow in each package
or
option b) Set up 1 data extract package which runs the stored procedure to set the date variables and then have several data flow tasks all in the same package
I have no one else with experience of this in my department, so it would be useful to get a experienced persons view on this.
Thanks in advance.
_____________________________________________________________________________MCITP: Business Intelligence Developer (2005)
September 8, 2008 at 6:20 am
You have another option - use a parent package to call of the child packages and pass the parameters from the parent package to each of the child packages (this can be done through package configuations).
I would say this all depends on the complexity of the packages. One package per logical process is not a rule you want to live by. SSIS is designed to multi-thread and run several processes at the same time. SSIS 2008 expands on this and does a better job. I tend to break up packages into bite sized pieces just to make them easier to work on, but they can get pretty large and still work just fine. There is an upper limit to the size of the packages because Visual Studio runs out of memory validating them when you have too many components (so you cannot open it in the IDE) but that is really the only limiting factor on package complexity.
Go with your gut - if the package will be eaiser to develop, maintain, and run in one package, leave it all in one package. If it is easier for you to break it up, do so.
September 8, 2008 at 9:39 am
Certainly one solution with common datasources and I'd probably do one package and split up with sequence containers for related tasks eg clear table then fill it.
Don't forget script tasks can set variables and connections too in a more readable way.
SSIS is capable of much more than single data flow tasks. Use the force!:)
September 9, 2008 at 2:44 am
ok,
I think I might take the approach of a packages for the data extracts where I need store the data for historical data, with multiple data flows.
I'm new to this as I mentioned, so I like to get into good habits from the start! Always useful to get an experienced view on it!
Thanks
_____________________________________________________________________________MCITP: Business Intelligence Developer (2005)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply