使用 xp_cmdshell 调用的 SSIS 包是否可以加入 SQL Server 事务？
我有一个非常基本的 SSIS 包，其中包含一个数据流任务（从 OLE DB 源到平面文件）。
TransactionOption 属性设置为“Required”，并且我尝试将 IsolationLevel 选项设置为“ReadCommited”、“ReadUncommissed”和“Serializable”。
该包将表 [TestTable] 中的所有行导出到平面文件。
我有以下 SQL 脚本（目前在 Management Studio 中运行）：
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 块顶部启动的事务？如果没有，可以吗？
I have a very basic SSIS package with one data flow task (from an OLE DB Source to a Flat File).
The TransactionOption property is set to Required and I have tried the IsolationLevel option set to ReadCommitted, ReadUncommitted and Serializable.
The package exports all rows from a table [TestTable] to the flat file.
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
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?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
xp_cmdshell 中的操作将在事务之外，无论它是 SSIS 还是其他查询。您可以轻松地将 @ssisstring 替换为 'sqlcmd -S myserver -d mydatabase -Q "SELECT TOP 1 FROM dbo.TestTable"
如果您需要事务，请在 SSIS 中进行。将 DELETE 语句作为执行 SQL 任务。将其连接到您的数据流任务。在包级别（右键单击控制流的背景并选择属性）将包的事务级别从“支持”更改为“必需”。这将启动一个事务。其中包含的所有内容都将登记在父事务中，除非您通过将默认事务级别从“支持”更改为“不支持”来明确选择退出该事务。
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"
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.