June 13, 2014 at 5:45 pm
We want to create several SSIS packages that load data from various systems into the staging database. We are debating two approaches:
1) Have one big project for every SSIS package that loads data into the staging database, regardless of the source system.
2) Have one project per source system. If there are 3 separate source systems, there will be 3 projects even though they have the same destination.
What is the better approach?
June 13, 2014 at 5:50 pm
Why does it matter?
I ask because a project for SSIS is basically just a container to allow for packages to be ran for debugging.
You *can* use shared Data Sources, but in general I find that to be painful later for maintenance and troubleshooting. It's much easier to simply build the Data Source(s) directly into the package, allow them to use a shared configuration, and go from there. The rest of the options really don't matter except for switching between 32 and 64 bit because of ODBC drivers you may need to use.
My project organization is very different than the guy sitting in the cube next to me, and we work basically on the same packages. I yank them into the project that makes sense organizationally to me, he does the same on his system. One of my teammates has the project from heck and has everything in a single one, just for convenience.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
June 16, 2014 at 10:40 am
We are using SSIS 2012, which has the project deployment model. Will having several packages affect the deployment vs. one package?
June 17, 2014 at 12:38 am
imani_technology (6/16/2014)
We are using SSIS 2012, which has the project deployment model. Will having several packages affect the deployment vs. one package?
Yes it will. One project = one ispac to deploy.
In your case, will you have separate development/release cycles for the different sources?
An open question, but other than the destination connection, will the packages have anything in common? Shared connections and parameters are powerful tools.
If you have, or are planning to have, many packages for each source, the time taken to deploy your ispacs may also become a factor - in which case multiple projects could be helpful for you (make sure you tune the default settings on your SSISDB too).
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
June 20, 2014 at 9:29 am
Been there and had to make these decisions.
The short version is that we did a hybrid model.
Projects were split out by source. Daily lookup tables would all be part of the same project. Critical tables got their own projects. Note that I separated variable driven packages into two packages with a parameter table. So even if a project was specific to one table there may be one to multiple packages to process it.
You may end up with a bunch of projects and that's fine. Project level deployment means that for safety only one package can be in development at any given time. Creating projects with fewer packages/business function helps ensure that a critical process change isn't blocked by a small change that isn't ready for deployment.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply