SSIS SQL Agent Job Fails After Deployment Due to Project-Level Connection Manager

Tips SQL Server Tips
,
1

Overview

In some scenarios, an SSIS package may execute successfully in Visual Studio or even when run manually, but fails when executed via SQL Server Agent Job after deployment. This can be confusing—especially when the job step reports success initially, but the job ultimately fails with a connection manager–related error.

This community article explains a real-world issue where the root cause was an accidental conversion of a Package-level Connection Manager to a Project-level Connection Manager , and how correcting it resolved the SQL Agent job failure.

Symptoms

  • SSIS package runs successfully in Visual Studio (SSDT)
  • Package executes fine when run manually
  • SQL Server Agent Job fails after deployment , even after replacing the package
  • SQL Agent Job History shows an error related to a missing connection manager

Error Message from SQL Server Agent Job History

CampaignReportError: Cannot find the connection manager with ID

"{9494D1A7-A5ED-42D8-904D-3AF6E426DF12}" in the connection manager collection

That connection manager is needed by

"Upsert Destination (Insert Update, Delete). Connections[DB-CONNECTION]".

Verify that a connection manager in the connection manager collection

has been created with that ID.

Root Cause Analysis: What Was Actually Happening?

After reviewing the execution logs and SSIS package configuration, we discovered that:

  • The Connection Manager was unintentionally converted from Package-level to Project-level
  • The package still referenced the old Connection Manager ID
  • During deployment, SSIS Catalog (SSISDB) could not resolve the connection manager mapping
  • As a result, SQL Server Agent failed during execution—even though the package itself looked valid

This typically happens when:

  • A connection manager is edited and accidentally promoted to Project level
  • The package is redeployed without realigning internal references
  • SQL Agent executes the deployed package, not the local SSDT version

Why Did It Work in Visual Studio but Fail in SQL Agent?

Visual Studio may still retain cached references or local metadata, allowing the package to run successfully.

However, when executed via SQL Server Agent:

  • The job runs the deployed version from SSISDB
  • SSIS strictly validates Connection Manager IDs
  • Any mismatch between expected and available connection managers causes runtime failure

Root Cause

:white_check_mark: Connection Manager scope mismatch

  • Package expected a Package-level Connection Manager
  • Actual deployment contained a Project-level Connection Manager with a different internal ID

Resolution Steps

Step 1: Review Execution Logs

  • Open SQL Server Agent Job History
  • Identify the missing Connection Manager ID
  • Note which component is referencing it (e.g., Upsert Destination)

Step 2: Convert Connection Manager Back to Package Level

Convert from Project to Package Connection Manager
Convert from Project to Package Connection Manager370×359 23 KB

In the screenshot above, the Connection Manager was accidentally promoted to a Project-level Connection Manager (highlighted at the bottom of SSDT). This caused the deployed package to reference a different internal Connection Manager ID, which SQL Agent could not resolve at runtime.

In SSDT:

  1. Open the SSIS package
  2. Locate the Connection Manager
  3. Right-click the Connection Manager
  4. Convert it from Project Connection Manager to Package Connection Manager

This ensures the connection manager exists within the package scope and uses the correct ID.

Step 3: Redeploy the Package

  • Redeploy the updated SSIS project/package to SSISDB
  • Ensure the old version is replaced

Step 4: Validate via SQL Agent Job

  • Execute the SQL Server Agent Job again
  • Confirm the job completes successfully

:+1: In our case, once the connection manager scope was corrected and the package redeployed, the job executed successfully without any errors.

Key Takeaways

  • Always verify Connection Manager scope (Package vs Project) before deployment
  • A package running successfully in SSDT does not guarantee SQL Agent success
  • SQL Agent strictly validates Connection Manager IDs during runtime
  • Accidental promotion to Project-level can silently break deployed jobs

Best Practices to Avoid SSIS Deployment Failures

  • Avoid unnecessary conversion of Connection Managers to Project level
  • After modifying Connection Managers, always:
    • Redeploy the package
    • Test execution via SQL Agent
  • Use clear naming and documentation for shared vs package-specific connections

Conclusion (Quick Checklist Before You Deploy)

Before deploying SSIS packages to production and scheduling them via SQL Server Agent, always verify:

  • :check_mark: Connection Manager scope (Package vs Project)
  • :check_mark: No accidental promotion to Project-level connections
  • :check_mark: Package is tested from SSISDB / SQL Agent , not only from SSDT

Following these checks can save hours of troubleshooting connection-related job failures.

If your SSIS package runs fine locally but fails in SQL Server Agent with a “Cannot find the connection manager” error, check the Connection Manager scope first . In many cases, reverting it back to a Package-level Connection Manager and redeploying resolves the issue immediately.

Have you faced a similar deployment issue? Feel free to share your experience or questions in the comments below :backhand_index_pointing_down: