2

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

Bal
  • 81
  • 1
  • 1
  • 6
  • We'll need more information. Post the SSIS step from your agent job. What type of connection manager is failing. Your title indicates you are trying to "read" them - is that something specific you are doing or does it reflect your assumption that the project connections are not honored. Do you know that the SQL Agent Account, or the delegated proxy, have connect and read permissions to the server which hosts the SSIS packages as well as the destination for the project connection managers? – billinkc Aug 05 '14 at 13:42
  • The Connection is an OLE DB Connection. Yes the SQL Agent Job is failing to read the connection (In Visual Studio when I change the OLE DB Connection to a Package connection it executes all the way through successfully just not when it's a Project Connection.) The server has the correct permissions as it can run the package when the connection is a Package connection. – Bal Aug 05 '14 at 14:43
  • Please post the definition of the SSIS step from your agent job. Scrub as you need to obfuscate sensitive information. As the question stands, there isn't enough tangible facts for someone to help diagnose the problem. I have not experienced this challenge with project level connections in the 2+ years I've been working working with the project deployment model. – billinkc Aug 05 '14 at 14:53
  • How is your SSIS project deployed??? I am guessing your SQL Server Agent job is just going into the file system and calling the package. This would obviously cause an error because your SSIS Project is not visible to the job agent. You should try deploying the project on the Server for it to work. – TMNT2014 Aug 05 '14 at 16:50
  • The SSIS Package is held in the C Drive on the server so is visible to SQL Server Agent. In Visual Studio I have changed the Project Connection to Package Connection and SQL Server Agent Successfully completes the job however this is not a practical way of maintaining the connection as I now have 1 connection per package - the issue arises when the OLE DB connection is a project connection SQL Server Agent cannot find the connection. I would try to post screenshots but I don't have enough reputation points yet. The Package Source is "File System". – Bal Aug 06 '14 at 06:59
  • The command line reads: /FILE "\"C:\SSIS\Integration Services Project1\Integration Services Project1\Package_Name.dtsx\"" /CHECKPOINTING OFF /REPORTING E – Bal Aug 06 '14 at 06:59

2 Answers2

2

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: Package Connection Manager

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: Deployment Wizard

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: Job Step Properties to Execute Package from SSIS Catalog

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

lightmotive
  • 520
  • 5
  • 17
0

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.

kyurthich
  • 100
  • 1
  • 11