SSIS ForEach Loop never ends, next task executes

Question

SSIS ForEach Loop never ends, next task executes

alicesql

SSCrazy

Points: 2649
More actions
January 20, 2022 at 3:09 pm

#3975261
I have built an SSIS package that imports files with xml data into an SQL table. There are 16K files. The package consists of a ForEach file enumerator followed by a Send Mail task. I have very little experience with xml, this has been a learn-as-I-go process. Within the ForEach, The xml data is inserted into a column in the table. I increment a file counter. I load the file name into a 2nd SQL table. Then a stored procedure is executed to shred the xml to retrieve 2 elements that are then also inserted into 2 columns in the SQL table. Then the file is copied to a different folder with the date/time appended to the filename (ultimately this will be an sftp location). Lastly, I update a datetime column in the SQL table. After the ForEach, I send an email.
This ran perfectly for a small sample of 10 files. Yesterday, I ran it for the full 16k and it seemed as though the ForEach just stalled. I received an email (so the last step in the package executed, which should only have happened of the ForEach finished with success), but the ForEach still had the amber circle showing the rotating dial. All 16K files were copied to the destination folder. I waited for it to complete, over an hour but I ended up stopping/canceling execution.
SQL Server 2016, VS Professional 2017, version 15.8.7.
Any suggestions for investigating this issue? TIA
- This topic was modified 2 years, 3 months ago by alicesql. Reason: typo correction

Viewing 5 posts - 1 through 4 (of 4 total)

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

TangoVictor SSChampion Points: 10969 More actions · Answer 1

I've had this happen on multiple occasions. VS and SSIS are just full of bugs. They have been since I first started using them 15 years ago. Don't waste your time trying to troubleshoot this.

We use master packages that execute all the other packages and I've seen a data flow just sit and spin but the master package shows everything completed execution. That's just the way it is. It's happened enough that I don't even question it anymore when it appears to be stalled I check the master or just cancel it and start again or check the data/files to see if it actually did complete.

The best you can do is keep it updated and hope. I'm running the VS 2019 and I keep it and the SSIS extension updated, I have a good fast laptop with plenty of memory and it doesn't matter I still run into this kind of weird stuff.

Ed B Hall of Fame Points: 3616 More actions · Answer 2

Can you tell whether it loaded all 16K files?

It sounds like it did all the work, but never reported to the screen that it had finished. Did you look at the output window when you stopped it?

Have you repeated it and experienced the same problem?

I agree that SSIS just does things like this sometimes, but it shouldn't do it every time. Can you deploy the package and run it from a job? I know not everyone has that privilege, but running packages in debug is very buggy.

alicesql SSCrazy Points: 2649 More actions · Answer 3

Yes, the files loaded. I could deploy and run it from an Agent job...I may try that to see if anything changes. Thanks!

alicesql SSCrazy Points: 2649 More actions · Answer 4

VS has always seemed a little glitchy to me, but I attributed that to my lack of understanding. Given that the files loaded, I may not need to run it again. We're importing data from a physician's private practice EHR at the same time we are approaching a go live to convert our EHR to another one. So, this is just a mechanism to bring the files in, keep track of them and send them out for further shredding by the new EHR conversion team. If any fine-tuning is needed, I'll see if the issue replicates.

Thanks!