Union All issue

Question

Union All issue

VG_100

SSC Enthusiast

Points: 109
More actions
May 12, 2010 at 8:02 am

#156949

I'm getting stuck at union all in my SSIS package. It's in Yellow for several hours. I attempted to run several times still didnt work. Its not performance issue because it's completes once I remove the all the union all downsteam. This is happnening since I have made changes to the package.
-- I have four union all in my package. I recently added new field in the look up and used that field in the derived transforms to replace existing column which is already in Union All.
SSIS is getting stuck at this union all transforms.
I deleted the union all and created new union all still didnt work.
Anybody has this problem? So far I found one similar issue which says SSIS bug. 🙂 let me know.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 1

The fact that it turns yellow only means that it is running. The real question is whether the pipelines that are going INTO it and OUT of it are doing anything. Are the numbers next to those pipelines changing or are they staying the same for all that time? If they are changing then it is doing something. If the numbers are changing VERY slowly then you are trying to push either a lot of data through OR the source is slow. Either way, are you including ONLY columns that are needed further down the pipeline or did you use a SELECT * FROM dbo.tablename?

CEWII

VG_100 SSC Enthusiast Points: 109 More actions · Answer 2

At the top all the transforms are Green. The numbers are not changing after 69286 records. It's getting stuck and not doing anything. I ran it for several hours still no luck.

I'm not sending the new field downstread as I dont need it.

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 3

I see you deleted and recreated, good, that was going to be a suggestion of mine.

I had another thought, is there activity on any of the downstream tasks, perhaps the pipeline is being held up but downstream tasks?

What is the task immediately following the UNION ALL, better yet can you screen print it while it is running?

CEWII

VG_100 SSC Enthusiast Points: 109 More actions · Answer 4

There's no activity downstream. It's held up at Union All for hours.

VG_100 SSC Enthusiast Points: 109 More actions · Answer 5

I found the solution for this. It's related to DefaultMaxBufferRows and DefaultMaxBufferSize.

I increased these settings and it worked fine.

I used following link - http://technet.microsoft.com/en-us/library/cc966529.aspx

Thanks for your help!

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 6

Great, I have to admit I didn't think about the buffers.. I'll store that away for future use. Thanks.

CEWII