Hey Harvey,

Well, I guess you could say I was lucky. I had designed the package and it was working without issue for about a month when I had to revisit the package since i forgot a condition on one of my select statements (good thing it was only in test at the time) and I decided to add an addition data reader, merge join and sort to drop a significant amount of data that I was carrying in memory that I didn't need. After I made those change sis when the package started to "blow up" when I ran it in SSIS but was fine in BIDS (it would even occasionally work in SSIS).

It was pretty easy for me to identify the change that was causing the issue so I just deleted them and left the extra data in the process since it would drop out latter on regardless.

The best suggestion I have for you is to do the following:

-Log into the SSIS Server via Remote Desktop or some other tool

-Open SSMS and find the job that you have for the SSIS package that is fail

-Open the step for the SSIS Package

-Go to the command line tab and copy the text out. It should begin like /DTS "\MSDB\FCPA\DSD Delivery Costs...

-Open a command line prompt

-Type DTEXEC and paste the command from above

-Run the command and it should then run your package

-Depending on several factors the package should eventual complete in error status (kind of ironic that we want it to fail)

-1 or 2 things should happen: 1) The package may just suddenly end with no info but you will find a mini dump in the SQL dump area or 2)If you scroll up in the command window you should come to the error step with some detailed info which might lead you to what tasks are causing the issue

My understanding is that this issue is very random and tough to pinpoint.

1 suggestion when it comes to sorts...if possible avoid them. They can take forever since they need to wait for everything proceeding it to complete first. The other suggest is to put the order by command in your select statements and then manually set the isSorted property and sort key properties for the pipeline. Then you can do you merge with out a sort command.

Hope this helps.

-Mike