I'm using SQL 2016 and we're converting over a bunch of SSIS packages (from way back in 2005). In our old architecture we had development and production. We're moving now to source control in VSO and we're staging our deployments. We have local development on developer machines, then we post to Dev, then to QA, then Staging, then finally production.
We've figured out how to use SSIS Environment Variables (AWESOME!) and we're able to run the files on local dev machines from inside Visual Studio using SDT. Then we deploy as project to an ispac file which we copy to the Dev server and import into our SSIS Catalog in SSMS. Then in SSMS we are trying to change the variables for each environment.
The problem is the Data Connection. I was passing the connection string and the authentication password as a parameter into a shared connection. So the connection read those values in from the project parameter when executing. Then we were going to change those values for each environment. It turns out on the server we need to execute using Integrated Security. Since we're testing remotely we can't use Integrated Security on our local machines. So basically local dev is SQL Authentication but Dev, QA, Staging, and Production environments will all be tested on servers using Integrated Security.
I can't seem to get this to work right. I have two Project Parameters DB_ConnectionString and DB_Password. I also have a shared Connection (OLEDB SQL) which in the package is parameterized. We use the Project Parameters for the connection so at execution it's using the project parameters to plug in the string and password.
When I post to live I need Integrated. So I tried putting an Integrated security connection string into the Environment Password fro DB_ConnectionString and then it requires a Password. But that isn't really working right. I'm getting a connection error.
SSIS Error Code DTS_OLEDBERROR "Invalid authorization specification"
