可以使用xp_cmdshell调用的SSIS包可以加入SQL Server事务中吗? [英] Can a SSIS package called using xp_cmdshell enlist in a SQL Server transaction?

问题描述

我有一个非常基本的SSIS包，其中包含一个数据流任务(从OLE DB源到平面文件).

I have a very basic SSIS package with one data flow task (from an OLE DB Source to a Flat File).

TransactionOption属性设置为Required，我已经尝试将IsolationLevel选项设置为ReadCommitted，ReadUncommitted和Serializable.

The TransactionOption property is set to Required and I have tried the IsolationLevel option set to ReadCommitted, ReadUncommitted and Serializable.

该程序包将表[TestTable]中的所有行导出到平面文件.

The package exports all rows from a table [TestTable] to the flat file.

我有以下SQL脚本(目前我正在Management Studio中运行):

I have the following SQL script (that I'm running in Management Studio for the moment):

BEGIN TRANSACTION

DELETE FROM [dbo].[TestTable]

DECLARE @SsisString VARCHAR(8000)
DECLARE @PackageName VARCHAR(200)
DECLARE @ServerName VARCHAR(100)
DECLARE @ReturnCode INT

SET @PackageName = 'TransactionalTestPackage'
SET @ServerName = 'SERVERNAME'
SET @SsisString = 'dtexec /sq ' + @PackageName + ' /ser ' + @ServerName + ' '

EXEC @ReturnCode = xp_cmdshell @SsisString

SELECT @ReturnCode

--COMMIT TRANSACTION
ROLLBACK TRANSACTION

请注意，在运行包之前，我将从表中删除所有行，因此从理论上讲，包应将零行导出到文件中，但实际上发生的是包挂起了(我认为是因为未提交)在TestTable上删除).问题是:以这种方式调用的SSIS包是否真正加入了从SQL块的顶部开始的事务中?如果没有，可以吗?

Note that I'm deleting all the rows from the table before running the package, so in theory the package should export zero rows to the file, but what is actually happening is the package is hanging (I think because of the uncommitted delete on the TestTable). Question is: Does the SSIS package called in this way actually enlist in the transaction started at the top of the SQL block, and if not, can it?

推荐答案

xp_cmdshell中的操作将在事务之外，无论是SSIS还是其他查询都无关紧要.您可以轻松地将@ssisstring替换为'sqlcmd -S myserver -d mydatabase -Q"SELECT TOP 1 FROM dbo.TestTable"

The actions in the xp_cmdshell are going to be outside of the transaction, doesn't matter if it's SSIS or another query. You could just as easily replaced the @ssisstring with 'sqlcmd -S myserver -d mydatabase -Q "SELECT TOP 1 FROM dbo.TestTable"

如果需要交易，请在SSIS中进行.将您的DELETE语句作为执行SQL任务.将其连接到您的数据流任务.在包级别(右键单击控制流的背景并选择属性)，将包的事务级别从支持"更改为必需".这将开始交易.除非您通过将默认交易级别从支持的"更改为不支持的"来明确选择退出该交易，否则其中包含的所有内容都会加入父交易.

If you need transactions, do it in SSIS. Put your DELETE statement as an Execute SQL Task. Wire that up to your data flow task. At the package level (right click on the background of the control flow and select properties) change the package's transaction level from Supported to Required. This will start a transaction. Everything contained within it will enlist in the parent transaction unless you explicitly opt out of the transaction by changing the default transaction level from Supported to NotSupported.

这篇关于可以使用xp_cmdshell调用的SSIS包可以加入SQL Server事务中吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！