How do I read project connections in ssis from SQL Server Agent Job?

Question

I am trying to run my SSIS Package via SQL Server Agent Jobs. In the SSIS Project I have Connections to My SQL Database as PROJECT Connections. I have set them up like this as all Packages call this connection. However when running it in SQL Server Agent I get error saying:

The connection "{}" is not found. This error is thrown by Connections collection when the specific connection element is not found.

It obviously can't locate the connection so how do I do this ? - The package executes successfully when executed via Visual Studio.

Many Thanks In Advance ! Bal

Answer 1

First, you need to create an Integration Services Catalog on your server instance. You will deploy your packages to the catalog.

A best practice for specifying the server in your connection managers is to use a single period, which references the local machine. That way, when you deploy your packages, the local machine will always be used:

After you've created your package(s) with project-level connections, you need to deploy the project. Right-click the project folder in SQL Server Data Tools/BIDS, then click Deploy. In the deployment wizard, specify the destination server (or just use "." again to deploy to the local instance) and the Integration Services (IS) Catalog folder:

Once deployed to your SQL Server instance's IS Catalog, you can set the Package Source in the Job Step Properties to "SSIS Catalog" and select the package that you deployed.

If you're exporting/importing to/from files, you'll want to ensure that the SQL Server Agent Service Account has appropriate rights to the folder where files are imported/exported. The easiest way to do that is to create a credential (usually a Windows user account), then create a SQL Server Agent Proxy that uses the credential, and then specify that proxy in the job step's Run as field.

Here's what your Job Step Properties window would look like after following all of the steps above:

The job should then run successfully.

FYI, you can also execute the package directly from the SSIS Catalog. Simply drill down into the Integration Services Catalog node of your server | right-click the package | click Execute....

Helpful Links

• Schedule a Package by using SQL Server Agent
• SSIS Tutorial: Deploying Packages

Answer 2

If you setup your configurations on the configurations tab in the Step properties of the SQL Server Job that should allow you to accomplish what you asked.