SQL Server SSIS Package Scheduling

Question

We have a package that we created in VS. The package executes perfectly in VS. We then saved and deployed the package to SSIS. Inside SQL Server Management Studio, the SSIS package executes perfectly when done manually. We have 2 packages that we manually execute. We are trying to schedule these 2 packages to run automatically using the SQL Server Agent. When we set up a job and schedule it, the job fails. The first step in the package fails with the error: Failed Validation and returned validation status "VS_ISBROKEN" We think it has to do with the security on SQL Server Agent, but we have checked and all the permissions seem to be set correctly.

Any help is greatly appreciated!

Answer 1

You should be getting additional error messages other than the VS_ISBROKEN, which will be more helpful in figuring out what's going on. It has been my experience that VS_ISBROKEN almost always refers to an invalid query. For example, the query could be referencing an object that doesn't exist on the target server.

Answer 2

I came across a similar problem in an earlier version of SSMS and found the problem was that the owner of the package in SQL server Agent was incorrect and should be sa or dbo

I don't know if it helps but the symptoms for this seem very similar to mine