0

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!

Phteven
  • 139
  • 1
  • 13
  • 1
    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 Aug 29 '18 at 14:18
  • 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 Aug 29 '18 at 15:40

1 Answers1

0

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.

Tab Alleman
  • 31,483
  • 7
  • 36
  • 52
  • 1
    Devil's advocate here. That is one solution; however, the solution addresses the symptom but not the cause and is likely to show up again if the job is re-deployed. I suggest that Phteven work to find what is causing the the problem upstream and fix it there - this will also provide beneficial experience. Said differently, an ounce of prevention beats a pound of cure. I generally agree there is not enough information to diagnose the problem other than to provide troubleshooting tips. – J Weezy Aug 29 '18 at 16:03
  • 1
    @JWeezy I agree, but the thing is I suspect that Phteven isn't really updating the job at all and is expecting the job to somehow "detect" that the package has been changed, which isn't the way it works. However, there isn't enough information in the question yet to even suggest that theory. : ) However, if my suspicion is correct, then this answer will fix the problem permanently. – Tab Alleman Aug 29 '18 at 17:12
  • That's correct @TabAlleman I've only just gotten to grips with SSIS package deployment via the SSISDB and as J-weezy suggested above, I hadn't sorted out the deployment model and rebuilt it before deploying! – Phteven Sep 05 '18 at 13:41