I come from Pentaho PDI.
There, I learned to have scripts load data to a [VariableDB].
That way, I can either initiate that VariableDB at runtime to "ProdDB" or even set the variable to different DBs within the script, temporarily, whatever.
Point is, I don't have to go through tons of files changing database names too often unless some major architecture changes.
Second thing is security. Pentaho's isn't great but you can at least store some of the database credentials is encrypted format, and they are blocked out in GUIs. A 'mastermind' could crack it but they would need a lot of access anyway.
SSIS --- well, it might be the opposite, maybe not. I'd prefer not to store any database passwords in plain text. At the same time, my predecessor left --- and I have no idea where the credentials are. They're "poof" gone -- I don't know. If SSIS scripts and jobs are running on the same SQL DB server, are they stored somewhere? I'm not sure.
Are they encrypted to his username, and now that he's long gone, unrecoverable?
This is not so important, since I can re-enter database credentials, but I'm looking to the future.
I know there are probably a few ways of doing this, but what's a simple way so that if I have 5 packages pointing to "TheFinanceDatabase" I can easily swap out the database host server, name, user, password, etc?
I take it config files and/ or connection managers --- they can be shared between packages within the same "project?". Maybe the "projects" can share connection credentials / pointers within the same "solution"? Just wondering here -- thanks!
I want to avoid re-entering/ losing credentials in the future.