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.

  • Yes, there is such a thing as package vs project connections. Start here And those connections can be stored in files, a table, etc. Also consider that the packages are run using the SQL Server Agent service account (or a proxy), which can be an extra security layer here. – Jacob H Mar 1 '19 at 16:05
  • I guess I'll have to poke around. I guess the current situation is --- all Job Agent/ Saved packaged on the server? They run just fine. If I copy them offline, all credentials are gone (probably security practice). I mean I need a way to "pass it off" to another person or developer and not say "oh yeah the password is Potato go re-enter the passwords for every database connection in the project" – user45867 Mar 1 '19 at 16:47

When the packages are deploy to and executed from the SSIS Catalog variables from an Environment can be used to store credentials such as those you mentioned, with these then used by the connection managers of packages. Once the value of an environment variable is changed, this is propagated to all packages using this variable. For instance, if you had a variable called "Server" that's used as the ServerName property of a connection manager, and you updated it to hold production server name instead of the test one all packages with this would now be pointed to the prod server. Additionally, environments can also be created for test, development, prod, etc., with the referenced environment on the package then updated to point to a different environment. The variables can also be marked as sensitive to protect information such as passwords. If you haven't installed the catalog (SSISDB), I'd recommend doing so. You can learn more about the SSIS Catalog here. A high level overview of configuring a connection manager to reference an environment variable is below.

  • Right click the package (or project) and select Configure. Then on the References pane press Add and choose the environment you created.
  • Still on the Configure page, go back to the Parameters pane, then the Connection Managers tab. For the connection manager that will be configured, select the property that will be set from an environment variable by clicking the ellipsis next to the property and select the appropriate environment variables on the next window that is displayed.
  • When running a package that references an environment from SQL Agent make sure to update the job step that calls the package with an environment reference. This can be done on the Job Step by going to the Configuration tab, checking the Environment check-box and selecting the referenced environment.
| improve this answer | |
  • Okay I have a lot of reading to do regarding parameters, package variables and expression, and environments and environmental variables. Sadly my predecessor coded like a monkey on PCP and I see we have an SSISDB set up but with nothing whatsoever under 'Environments'. Thanks for the help! – user45867 Mar 4 '19 at 16:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.