0

I have the following Data Flow Task setup (see image).

It takes the correct amount of rows from the OLE DB Source and passes everything through the Data Conversion item. However, the process then gets stuck on 10,104 out of the 29,379 rows at the Sort and Excel Destination item (I'm sorting alphabetically by one column only).

Why is it getting stuck and what can I do to move it out of this rut?

Thanks

Data Flow Task SQL DB to Excel Destination

Shaye
  • 179
  • 13

2 Answers2

0

Would need to see the properties on your sort transformation but maybe this could be the issue, make sure the following isn't checked:

enter image description here

Thanks.

Gav

GavB841
  • 23
  • 2
  • Thanks Gav! It's definitely not this. When I check the debug output I see this curious message: "The program '[10332] DtsDebugHost.exe: DTS' has exited with code 0 (0x0)." But also, in the OLE DB Source SQL query, if I Select Top 10 the whole process completes without getting stuck. – Shaye Dec 17 '19 at 16:47
  • Hi Shaye, does it work without the data conversion? I've known in the past if the data isn't correctly converted it may impact the sort. If it works without the data conversion then you know that's the problem not the sort. Try it and let me know. Thanks Gav – GavB841 Dec 18 '19 at 14:29
  • Hi Gav! I was being an idiot. Because I incorrectly configured the Event Handler to handle failed task i.e. script task to send an email somehow this didn't work properly so it was getting stuck at the Event handler and I couldn't see the real error. So I disabled the script in the Event handler and the problem was actually one of the Input columns to the Excel destination i.e. more than 255 size long. I've configured it to just truncate this column rather than fail if more than 255 size – Shaye Dec 18 '19 at 15:14
0

The issue was that when inserting into an Excel Data Source the maximum size for each column is 255 but the size of the values from the mapped SQL Server column was on average greater than 700.

So it was necessary to set the maximum size in the Data Conversion to 255 (of the large column) to correspond to the Excel maximum column size. SSIS naturally truncates the column.

Shaye
  • 179
  • 13