Search code examples
sql-servertransactionsssisxp-cmdshell

Can a SSIS package called using xp_cmdshell enlist in a SQL Server transaction?

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

Control Flow

Data Flow

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?

Solution

  • 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.