2

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?

Cioby
  • 221
  • 1
  • 3
  • 7
  • 1
    Try to enable Logging which might give u an insight why it hangs – praveen Apr 10 '13 at 08:51
  • @praveen Thank you, but i've enabled a log for text files but as far as i can tell there is no error but, frankly, the log output is slightly beyond my understanding – Cioby Apr 10 '13 at 08:59
  • Why do u need xp_cmdshell to execute . Just create a batch file and run the SSIS package using `dtexec` utility. I'm not sure but as stated in ur link may be due to `xp_xmdshell` the package is getting hanged.check this link out http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/39eb76d1-51b4-488d-870b-a753073ced07/ – praveen Apr 10 '13 at 09:04
  • i need to know if the import was successfully done to continue with the business needs and to rollback everything if there was any error at any point. – Cioby Apr 10 '13 at 09:13
  • You need to use Transactions in order to do `Rollback` in case of failure . Try to wrap your components in a `Sequence Container` and make the `transaction option=Required` and for all the tasks inside the container keep `transaction option=Supported`. Check this article for understanding Transaction in SSIS http://msdn.microsoft.com/en-us/library/ms141144.aspx – praveen Apr 10 '13 at 09:17
  • Agree, but a transaction inside the package is not sufficient. I need the transaction on all the SQL script, including the package so if it fails, let's say 2 business validations, i need to rollback everything – Cioby Apr 10 '13 at 09:26
  • `Sql Scripts` ? Are you performing any business logic in stored procedure as well as calling an `SSIS` package from there ? – praveen Apr 10 '13 at 09:32
  • Yes, i am. it is listed in the needs section – Cioby Apr 10 '13 at 09:44
  • Not Tested but use `Transactions` in your `Stored Procedure` along with the transactions mentioneed above in `SSIS package` for your needs – praveen Apr 10 '13 at 09:51
  • @praveen thank you for your help. i've managed to find a different approach for my needs. i've updated the post – Cioby Apr 10 '13 at 13:30
  • +1 for sp_update_jobstep. I've done the stored-procedure and xp_cmdshell, and doing it via a job looks infinitely better. – criticalfix Apr 10 '13 at 14:20
  • I'd be suprised if a SQL Transaction wrapped around a xp_cmdshell calling a SSIS would actually rollback any data operations inside the SSIS. To troubleshoot your frozen pacakge you need to enable logging everywhere you can, analyse the logs and identify at which step it is failing. – Nick.Mc Apr 11 '13 at 04:27

2 Answers2

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();
billinkc
  • 59,250
  • 9
  • 102
  • 159
  • Indeed, as i said in the update, i went the job route and managed to get my needs done. I guess some clarifications are in need(sorry for lacks in initila post): running one instance is not a problem, actually is a desired business need. UI is restricted for concurrent uploads. Since i need to run only a job at a time, i decide if i block the UI controls based on the job status. Regarding the double quoted problem, i know about it. I ran into it at the initial tests of xp_cmdshell. I don't think this is the problem since it manages reads from Excel (~2GB memory used - same as BIDS) – Cioby Apr 11 '13 at 08:21
0

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.

Chris
  • 1