We're using Visual Studio 15.7.2 and using SSIS on SQL 2017 (14.0.3029.16).
We built a connection manager to a 3rd-party, vendor managed SQL Server 2008 R2 (10.50.6529.0). Our only allowed connections to this server are via SQL Login, so Windows Authentication is a no-go.
We can run the package inside of Visual Studio/SSDT just fine, the package executes and completes successfully as we'd expect.
When we open the package in SSDT we're met with error immediately:
Upon looking at Connection Managers we're met with the following icon:
When we open the connection manager, this is what we see (password blank, should be *********):
The package is configured to run (via job) at 8:45 pm Eastern and it fails every time. We get a plethora of errors that basically state we can't connect to the 3rd-party server. Investigation shows that the password isn't being saved so we get denied at the front door!
Has anyone ran into this before? What options do I have to rectify? I'm somewhat new to SSIS, so I'm learning a lot of hard lessons fast.
Best Answer
The password for a SQL Login is Sensitive so it will be omitted from a connection string and thus not saved. If you deploy your package to the SSIS catalog you can use an environment variable with the
Sensitiveproperty for your password, then map this to the corresponding property in the connection manager. See the following article from Andy Leonard for more details on how to implement this for a SQL Login. If you want to configure the SSIS catalog see Level 18 of this series. You also mentioned that you're relatively new to SSIS, so I'd recommend working through this stairway as well.http://www.sqlservercentral.com/articles/Stairway+Series/121490/