SSIS 包在运行时挂起 [英] SSIS package hangs while running

问题描述

正如标题所暗示的，我在数据导入过程中遇到了 SSIS 包的问题.我将尽力描述我的需求、所采取的方法、问题以及我迄今为止所尝试的内容.

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.

需求:
将数据从 Excel 2007 文件(1.000.000 行)导入 SQL 表.
文件是在 UI 的帮助下上传的，因此包必须接收文件路径作为参数
导入不能阻塞 UI
在 SQL 级别应用的额外业务验证
知道包是否失败的可能性
失败回滚的可能性

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

方法:
我已经创建了一个 SSIS 包并在 BIDS 中成功测试了它
在xp_cmdshell的帮助下创建了一个存储过程并调用了dtexec
创建了一个不阻塞 UI 的作业(并且还能够识别导入/业务需求是否仍在运行
填充一个表来存储包的参数(在我所做的研究中，我发现我不能将参数直接传递给作业或作业步骤)

构建对 dtexec 的调用的代码如下所示

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

以及生成并由 xp_cmdshell 运行的行

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>\""

问题:
在保持内存占用(在任务管理器中观察过程)的情况下，该包似乎在长时间运行 (1h+) 后挂起并且什么也不做，尽管它应该需要大约 25 分钟.
所以，我的问题是:
1. 什么可能使包挂起并无法完成
2. 当文件为 220mb 时，为什么 dtexec 占用 ~2GB 的内存(这是出于好奇；我可以忍受内存问题)

我试过的:
在 cmd 中运行该行.包运行成功，这让我觉得是 xp_cmdshell 的问题
我已经读到 xp_cmdshell 的权限可能存在问题，因此我目前正在以 SQL 管理员身份运行该作业
我发现了一些可以解释正在发生的事情，但它与从应用程序运行包有关；如果这是问题，我将不胜感激有关 SQL 语法的一些帮助

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> ;

能够修改作业步骤，我将包的调用从存储过程转移到作业步骤.
需要提醒一件事:要执行从服务器代理打包的 DTS，该步骤必须在 sysadmin 帐户下运行，或者需要代理才能执行.

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?

推荐答案

根本原因

xp_cmdshell 存在一个已知问题，它只允许一组双引号参数处理.

Root cause

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

1. 您可以走工作步骤路线.这种方法的缺点是您只能运行一个实例.我不知道 UI 是如何实现的，但并发上传可能很难看.

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.

创建一个运行包的批处理文件.它将需要文件名和连接参数，这可能会让您只传入一组双引号参数.

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.

我对不阻塞 UI 的要求有点困惑，但它需要知道包失败.一种选择是让您的 UI 将所有启动参数写入表格.然后有一个计划的进程每 N 次间隔运行一次，它用所述参数启动那些包，并将结果写回该表或另一个表.您也可以直接从 UI 启动包.虽然您可以使用 dtexec，但此时您正在编写自定义代码，只需使用对象模型即可.

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.

伪代码近似

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();

这篇关于SSIS 包在运行时挂起的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！