4

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

kkuilla
  • 2,226
  • 3
  • 34
  • 37
Kim
  • 393
  • 2
  • 8
  • 18
  • 3
    What does your Output panel say? – Mike Aug 12 '14 at 22:44
  • Most probably, the Success status of your Execute Job Task is determined not by the job outcome, but by the result of the attempt to start it. Success result probably means that the job has been started successfully, but what happens later... Btw, have you checked the job execution history? – Roger Wolf Aug 13 '14 at 06:34
  • Hi Mike, the output says (after how many rows it has written and that the cleanup phase is beginning): SSIS package "c:\Users\Sally\Documents\Visual Studio 2012\Projects\POC\POC\Archive.dtsx" finished: Failure. – Kim Aug 13 '14 at 06:51
  • Hi Ennor, thanks for your explanation. The job execution history doesn't show errors either :( So it does show that the job is being called correctly by SSIS but then I don't understand why it says there's an error. – Kim Aug 13 '14 at 06:55

5 Answers5

1

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

Kim
  • 393
  • 2
  • 8
  • 18
  • I saw this as one of the first posts when I had this happen to me. I should have just bucked up and recreated everything when I saw it. I still don't know what was causing my errors, but simply creating a new package and copying over the transforms and configurations worked. I've worked with SSIS for years and seen many of its quirks, but this was a first for me. I should have heeded your warning earlier. I beat my head against a wall for a while and finally came back and did this. Sigh! – Catchops Oct 20 '15 at 15:14
  • I'm a bit late to the discussion here, but this same problem happened with me twice in the last weeks. When I clicked to see the logs, it also says "The script executed successfully, but the object did not report an end of the rowset". Somehow there was a reference to another package, which is not referenced at all. Anyway, I did the same: I created a new SSIS package, and after a few Ctrl+C's and Ctrl+V's everything was working fine. I don't have a clue of what was happening before. – Rick Dec 15 '15 at 15:28
  • I've run into what @rvphx suggested, but what it ended up being for my current project was that the package was migrated from 2005 and had several "Execute Package" tasks that were still using the external reference rather than the project reference. I took over from someone else so once I changed everything to project reference the failures went away. Even though the everything ran fine, my guess is it was an ID or password issue. – gwgeller Feb 24 '16 at 20:48
1

Did you look at the Event Handlers? Also, any other failed configurations or Loggings in the background? These three could be possible culprits. Any other Tasks that are disabled and are using an old connection string that no longer resides on the package?

rvphx
  • 2,324
  • 6
  • 40
  • 69
  • Thanks for your reply. Event handler is empty and I didn't enable loggings. The last one could be possible since I had copied some tasks from another package. Later on I deleted all the copied stuff but possibly it was still reading some of those old copied tasks? – Kim Aug 14 '14 at 07:15
1

I got the same issue. Try going through each event handler tab, data flow tab for Evey executable while in the execution / run mode. You would be able to see red Cross marks where the error is.

Cranberrypie
  • 7
  • 1
0

Run the workflow > click the "Progress" button to see red X's.

JustBeingHelpful
  • 18,332
  • 38
  • 160
  • 245
0

click on the blank space of the project and check how "forceExecutionResult" property is set. I had the same issue and when I was about to rewrite the code I checked the properties of the project and realized that it was set as "failure" instead of "None"

Gabriele
  • 23
  • 4