January 19, 2016 at 7:31 am
Afternoon All,
I am just trying to get in line a great many SSIS packages that either might be used, might not be used, are duplicated in terms of purpose, and so forth. The usual kind of thing one does when taking over a new development environment that previously had no DBA.
I've not done massive amounts of work with managing SSIS, mostly because I've not been at places that have much more than 20 or so packages that can be kept in line fairly easily.
Even if I manage to whittle the count down to half we're looking at around 350 packages.
So my question, really is, as we are going through a migration of a couple of the production servers (where these packages read/write) is there such a thing as a global connection, much like you have in SSRS where a connection can be defined and is visible to all reports, should it wish to use it. That way it will be a single point to edit the connection string for all the packages.
Or should I use a config files instead? Or package variables?
Any thoughts?
Cheers
Alex
January 19, 2016 at 7:50 am
alex.sqldba (1/19/2016)
Afternoon All,I am just trying to get in line a great many SSIS packages that either might be used, might not be used, are duplicated in terms of purpose, and so forth. The usual kind of thing one does when taking over a new development environment that previously had no DBA.
I've not done massive amounts of work with managing SSIS, mostly because I've not been at places that have much more than 20 or so packages that can be kept in line fairly easily.
Even if I manage to whittle the count down to half we're looking at around 350 packages.
So my question, really is, as we are going through a migration of a couple of the production servers (where these packages read/write) is there such a thing as a global connection, much like you have in SSRS where a connection can be defined and is visible to all reports, should it wish to use it. That way it will be a single point to edit the connection string for all the packages.
Or should I use a config files instead? Or package variables?
Any thoughts?
Cheers
Alex
Are you using SSIS 2012+?
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
January 19, 2016 at 11:06 am
I am on 2014 Enterprise.
Thanks
Alex
January 19, 2016 at 12:00 pm
alex.sqldba (1/19/2016)
I am on 2014 Enterprise.Thanks
Alex
Great. A connection in SSIS can be defined at either the package level (which you know about) or the project level.
All packages within an SSIS project can share a single project-level connection. You must use the project-deployment model[/url] to take advantage of this.
Having said that, I do not recommend putting 350 packages into a single project (as each one of the projects needs to be redeployed, as a unit, whenever a package is changed). Redeployment of large projects can take a long time.
Multiple projects can share SSISDB Environments though. Here is what I mean:
Projects P1, P2, P3 and P4 all have a connection C1 to server S1.
Environment E contains a variable 'Conn1' containing the connection string for C1.
Each of the projects can be configured to 'reference' environment E.
Each of the C1 connections (4 occurrences) can be mapped to 'Conn1'.
In future, should connection C1 ever need to be changed, a change to environment variable Conn1 would be enough to effect the change in all of the projects.
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply