Ssis Package Hangs While Running

As the title might suggest, i am having problems with a SSIS package during data import. I will try to describe the best i can the needs, the taken approach, the problem and what i

Solution 1:

Root cause

There is a known issue with xp_cmdshell that only allows one set of double quoted parameters to be handled.

Resolution

1. You can go the job step route. The downside to this approach is that you'd only be able to have one instance running. I don't know how the UI implements things but concurrent uploads might be ugly.

2. Create a batch file that runs packages. It would take the file name and the connection parameter and that might get you down to only a single set of double quoted parameters being passed in.

3. I'm a touch confused on the requirement to not block the UI but it needs to know about package failure. One option would be to have your UI write all the start up parameters to a table. Then have a scheduled process to run every N intervals and it starts those packages with said parameters and writes the results back to that or another table. You could also start the package directly from the UI. While you can use dtexec, at this point since you're writing custom code, just use the object model and be done with it.

Pseudocode approximately

using Microsoft.SqlServer.Dts;

stringfileName= @"\\network\path\file.dtsx";
Applicationapp=newApplication();
Packagep= app.LoadPackage(fileName, null);
p.Variables["FileName"].Value = @"\\network\path\file.xlsx";
p.Variables["ConnectionString"].Value = @"whatever works";
DTSExecResultresults= currentPackage.Execute();

Solution 2:

If you are running this package on a 64-bit machine, using the 32-bit version of dtexec.exe will cause the package to hang. Use C:\Program Files, not C:\Program Files (x86) to get the 64-bit version. Or just execute the package from a SQL Agent job step (that uses the 64-bit version. Also, don't use the execute package utility on a 64-bit machine. It uses a 32-bit exe named dtexecUI.