SSIS package hangs while running

Question

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 have tried until now.

The need:
Import data from an Excel 2007 file (1.000.000 rows) to a SQL table.
The file is uploaded with the help of UI so the package must receive file path as a parameter
The import must not block the UI
Extra business validations applied at SQL level
The possibility of knowing if the package failed
The possibility of rollback on fail

The approach:
I've created a SSIS package and test it successfully in BIDS
Created a store procedure and called dtexec with the help of xp_cmdshell
Created a job to not block the UI (and also to be able to identify if the import/business need is still running
Populated a table to store the parameters for the package (in the research i've done i found out i cannot pass parameters directly to the job or job step)

The code that builds the call to dtexec looks like this

DECLARE @SSIS NVARCHAR(4000) = '';
DECLARE @Params NVARCHAR(4000) = '/set \package.variables[FileName].Value;"\"' + @FileName + '\"" /set \package.variables[ConnectionString].Value;"\"' + @ConnectionString + '\""';
DECLARE @ExePath NVARCHAR(4000) = 'C:\"Program Files (x86)"\"Microsoft SQL Server"\100\DTS\Binn\'

SET @SSIS = @ExePath + 'dtexec /f "' + @PackageName + '" '
SET @SSIS = @SSIS + @Params

DECLARE @ReturnCode int
EXEC @ReturnCode = master..xp_cmdshell @SSIS

and the line that resulted and was ran by xp_cmdshell

C:\"Program Files (x86)"\"Microsoft SQL Server"\100\DTS\Binn\dtexec /f "C:\inetpub\wwwroot\pwc\\Import.dtsx" /set \package.variables[FileName].Value;"\"\\<server_name>\upload\Import.xlsx\"" /set \package.variables[ConnectionString].Value;"\"<connection_string>\""

The problem:
The package seems to hang and do nothing after a very lengthy run (1h+) while holding the memory occupied (watched the process in task manager), although it should take roughly 25 minutes.
So, my questions are:
1. what could make the package hang and fail to finish
2. why is dtexec taking ~2GB of memory when the file is 220mb (this is out of curiosity; i can live with the memory issue)

What i have tried:
Running the line in cmd. The package ran successfully which made me think it is a problem with xp_cmdshell
I've read that there might be problems with permissions for xp_cmdshell so i am currently running the job as a SQL administrator
I've found a some that might explain what is happening but it relates to running the package from an application; if this is the problem i would appreciate some help with the syntax in SQL

I would greatly appreciate your help in solving this problem

Update
While the matter at hand still remains unsolved, i've managed to find a different way of solving the need.
In my previous statement i said that i do not know how to pass parameters to a job step. Meanwhile i found a way. It is not really a direct one, but it does solve my problems. Using job step update and permissions needed for update, i managed to modify the comment field of a job step

EXEC msdb.dbo.sp_update_jobstep
@job_name = N'StartImportFlow',
@step_id = 1,
@command  = <my command> ;

Being able to modify a job step, i moved the call for the package from stored procedure to a job step.
One thing needs to be reminded: to execute a DTS packaged from Server Agent the step must run under a sysadmin account or it needs a proxy to allow the execution.

I'd appreciate some tips on what to do with the current question: should i mark it as answered or should i let it like this for the initial questions to be answered?

Answer 1

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.

Answer 2

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;

string fileName = @"\\network\path\file.dtsx";
Application app = new Application();
Package p = app.LoadPackage(fileName, null);
p.Variables["FileName"].Value = @"\\network\path\file.xlsx";
p.Variables["ConnectionString"].Value = @"whatever works";
DTSExecResult results = currentPackage.Execute();