SSIS package execution stops before finishing

user_6265140 0
Viewed 4175

Today I came across a bug that I'd like to share with everyone.

When trying to execute an SSIS package in Visual Studio (2015 and 2017 the following can happen (note this package was executed sucessfully before).

enter image description here

While on the bottom it clearly says the SSIS package is finished, the data flow task is still in progress (and will never finish). What also happens (don't know if consistently) a CMD window pops open with "SQLDUMPER.EXE".

This is not due to the way the steps are configured, since executing them individually might still lead to the proper results.

Note as well that in my case this problem makes Visual Studio incredibly unstable. It is not uncommon that after or during every execution Visual Studio crashes completely and is automatically shut down by Windows.

There are no error messages and I had no idea what the reason is this happened untill... (answer below)

etl ssis visual-studio sql-server
4 Answers

After a lot of googling i've found a reason whya multitude of people seem to have this problem.

  • Right click the project and then click on properties
  • Click "Debugging"
  • Set "Run64BitRunTime" to "True"

Apparently for most people this fixes the problem.

For me however I had to come up with another solution.

  • Right click the project and click on properties
  • Click "General"
  • Set "TargetServerVersion" to either the target SQL server and run the package again OR set it to any SQL server version, run the package to see if it works, and if it doesn't set it to another version.

These solutions are counter intuitive because this problem seems to arrise at a random moment and the target sql server version might have been the same throughout the entire development.

I have not tried to see what happens when I deploy the bugged project to a server and see if it runs there, so any and all extra information on this problem would be appreciated.

user_62651400

I spent a good bit of time googling this problem. It looks like it is being reported by many people around the world. It is usually down to either x32/x64 compatibility or some miniscule issue with sorting or data sizes or compiling a custom C# script (or combination thereof).

None of these things worked for me so I rebuilt the entire package from scratch and the issue was gone.

It is far from perfect, especially if your package is large and/or complex but if nothing else works, this is your last resort.

user_26107010

"Run64BitRunTime" was already set to "True", but I was getting this error. so I restarted the visual studio and problem resolved.

user_25241370

None of the proposed solutions worked for me. I had to rebuild both the solution and the project a couple of times and this fixed it. Of course, I opened and closed the VS a couple of times in between. I guess this is a bug and you have to tinker with it to get it to work.

user_187826010
Related