This sounds similar to SSIS package SQL job not using new environment variable configuration, but when I did a test on my dev SQL Server box, I didn't have to restart the SQL Server agent. And honestly, if that is what it takes to prevent someone from 'hurting' themselves with this product, then. . . that is absolutely unacceptable.
UPDATE: And now, to follow up on that comment about SSIS package SQL job not using new environment variable configuration possibly being a solution.
It is not. I restarted the agent. No dice. Job still runs successfully. I restarted the SQL Server Agent and Service. Again, no failure.
This also sounds peripherally related to SSIS 2012 SQL Agent Job ConnectionString vs InitialCatalog, but I've been working with SSIS since 2005 and have never seen a connection string configure from it's individual components. We have ALWAYS put the connecting string in the .config files and had success.
I've got a problem that I haven't been able to resolve. Appears to be a bug, honestly, but I don't want to be so presumptuous.
I'm running SQL Server 2012 SP3 on Windows Server 2012 R2 in my test environment.
On my dev box, I'm running VS2012 + SSDT-BI.
I built a solution with a single SSIS 2012 project.
- I deployed the SSIS Project to my Test SQL Server box.
- I configured a new Environment (let's call it Fred).
- I add variables to the Fred environment.
- Some of these variables are connection strings.
- I go back to the SSIS Project in the SSISDB tree. Right-click and configure.
- I set all of my project's connection manager's connection string properties (only) to configure from one of Fred's connection string variables.
Now I have everything, connection-wise at least, wired to configure from my Fred Environment.
- I created a SQL Server Agent job (of type SSIS) to execute a package.
- I check the box to say Configure from environment .\Fred
- I complete job configuration.
- I right-click the job and choose 'Start Job at Step. . .'
The job executes. Yay! Everything works. Packages all execute. (Everyone is doing the dance of joy!)
Now here comes the part I can't resolve.
As part of my system testing (I'm new and cautious to the 2012+ Project Deployment model) I change a connection string value stored in one of my Fred Environment variables. This connection string was one that my SSIS project's connection manager's connection string (I triple-checked this) was configured to use.
The connection string is now invalid/incomplete and, now if I was to execute the same SQL Agent Job, the expectation is that the executed Package should fail (validation), right?
Well guess what. The job succeeds. (Dance of joy stops.) The packages all execute successfully. I check my test database environment and rows have been added to a table in a DB whose connection string is completed wrong (in the Fred environment).
So, now I think hmm, well, maybe I missed something. I go back in and I delete the environment variable that contains the invalid/incomplete connection string.
I execute the job again. Job succeeds. Uh, What?
- I launch my dev instance of SQL Server 2012 (RTM, not SP3, incidentally)
- I deploy a test project with the same project connection manager/environment configuration mapping
- I go through the same steps. . .
BUT now this time, the job fails as expected. Note: No SQL Server agent restart made/required.
- I go back to my Test box
- I right click the SQL Agent (SSIS) job
- I go to Properties > Steps (page) > Edit (first step. There's only one), and now I'm seeing the warning:
The parameter "SomeOtherSSISProjectParameter" is configured to use an environment variable, but no environment variable has been selected. Check the "Environment" checkbox and specify the environment to use, or specify a literal value for the parameter. (Microsoft.DataTransformationServices.DTSExecUI.Controls)
Uh, what!?
- First off, the Environment check box in this job step is already checked.
- Secondly, this parameter is not related or mapped to the the Fred enviro. variable that I changed and then removed.
- Thirdly the Environment variable value is not null or empty. It's still there, un-touched from when I first added it to the environment.
(At this point, I'm thinking the SSISDB is corrupt.)
I click passed the warning. Now that I am here in the SSIS Package SQL Server Agent config page, as I try to click OK (without making any changes) I NOW get an expected error message related to the orphaned Environment config.
Property "ConnectionString" of connection manager "MySSISProjConnManagerName" is configured to receive a value from an environment variable named "MyFredEnviroVariableName" but there is not environment variable named "MyFredEnviroVariableName" in environment ".\Fred". Select a different environment, or use a literal value for the property. (Microsoft.DataTransformationServices.DTSExecUI.Controls)
So the good news is that I can't click OK to save this corrupt job/step, but the bad news is that I CAN click cancel, and I can right click the job and choose 'Start Job at Step. . .' and have it complete successfully when it absolutely shouldn't.
Yes, I restarted the agent. Yes, I restarted to server.
I tested the scenario on my dev instance (RTM) of SQL Server 2012, and I can't reproduce it. Can anyone else? Better yet, is there a fix solution? SP4?
Any help with this would be MUCH appreciated. As far as I'm concerned this is a show-stopper for using the Project Deployment model with 2012.