SQL Agent detects a connection manager in SSIS package that has been deleted

0
votes

We've been working to get some SSIS packages deployed to our SSISDB catalog in the sql server. After fixing umpteen issues with permission in proxy accounts the latest issue is this;

After deploying the project to the SSISDB (SQL Server 2012) then either updating the existing SQL Agent job to use the deployed SSIS package OR deleting it and creating a fresh agent job, the sql agent is detecting a connection manager to the dev SQL Server that I deleted from the package. This is causing a failure when the job runs with some sort of permissions issue however the connection manager doesn't exist in the package. I even checked the XML code for the connection managers and there is nothing in there for the one I deleted.

Agent connection configuration

Package connection managers

I can't understand how the SQL agent is detecting a connection manager in the configuration tab of the job step when that connection manager isn't visible in the package dev files anywhere.

I've deleted and re-deployed the project after saving it in Visual Studio again and again.

Is there some deep, hidden setting somewhere that contains more connection data? It feels like the problem lies more with SSMS than SSIS but at this point I'm lost!

1
sql-serverssisssis-connection-manager
Just to be clear, you saved the package after deleting the connection, but did you build it before re-deploying it? Failure to build the project will keep any changes out of the *.ispac file that is deployed to the SQL Server, which would explain why it still shows up in SQL Agent. – J Weezy
What are the steps you are following to re-create the SQL Agent Job after you remove the connection manager from the package? – Tab Alleman

1 Answers

0
votes

You haven't given enough information for me to tell you WHY that connection manager is showing up, but I can tell you what you can do about it.

You can script DROP and CREATE JOB scripts in SSMS by right-clicking the job in the object explorer. In the CREATE JOB script, find the connection manager that you don't want any more, remove it from the script, and then run the scripts.