Search code examples
sql-serverssispackagejobs

Why did my SSIS package complete with an error while there seems to be no errors?

I have a package with one data flow task. In the data flow task it copies data from one database to another archive database.

I linked two precedence constraints. If it's successful it should go on and start a certain job in SQL Server (delete records from the original database). If the task fails, it should return a script task saying that it failed.

When I run this, the data flow task is successful (every record gets copied). The data flow task gets a green tick. The "execute SQL Server Agent Job Task" also gets a green tick. Yet after completing the package it says

"Package execution completed with error. Click here to switch to design mode, or select Stop Debugging from the Debug menu."

I included a screenshot of it:

enter image description here

The output basically only says:

SSIS package "c:\Users\Kim\Documents\Visual Studio 2012\Projects\POC\POC\Archive.dtsx" finished: Failure.

So:

  1. Where can I find the error? There is no indication at all what went wrong. Both show green ticks and the migration of data did went well indeed. The SQL Server job didn't do its job. The records are still there. So I have a feeling that the error has to do with the job. I have to mention it is the Change Data Capture cleanup job which was automatically made when I turned on CDC on this table. I did this because I only want this job to happen when the data flow task is successful (instead of running the schedule by default).

  2. If it failed, why didn't it follow the precedence constraint for failure (showing the script)?

SQL Server agent is turned on by the way.

Can someone please help me? I googled "Package execution completed with error" and I literally get only 68 results which are not helping.

Kim

Solution

  • I recreated the entire package and it completed with success. I'm still wondering what the difference is with my original package, but I'm guessing it might have something to do with non-corresponding meta-data. When making the original package I had copied a few tasks and then made new tasks and deleted the copied ones (because it was easier to look between them instead of switching between SSIS projects). I deleted all the old copied tasks, but possibly something went wrong there and that something is still linked to old metadata. All the tasks are performing though.

    Mike en Ennor thanks for looking into the problem. If anyone has any clue what it could be, please reply anyway, because my solution to recreate the package again was not a satisfying solution.

    Kim