SSIS Transactions without MSDTC - PART 2 - Excluding Error Logging Event Handler From Transaction

In my previous post, I implemented explicit transaction logic into a SSIS package when the MSDTC service was not available.

One issue this may raise is around the default SSIS event handlers. My package has an 'OnError' event handler, which is called when any error occurs within the package. The event handler simply logs the error to my SQL Error Logging table. This handler was using the same data connection as my main package. Unfortunately this meant that if an error occurred within the package, everything was rolled back, including the logged error message! I needed to find some way of maintaining the logged error but rolling back everything else.

The solution is simple - create a new datasource for the error logging handler:

All other properties should be the same as the data connection used in the main package.
This creates separation between the datasource within the transaction logic and the event handler's datasource.

SQL Server Integration Services SSIS Transactions without MSDTC - PART 1

Sometimes you might required transaction logic within your SSIS packages, in the case of a failure during the process which requires a rollback. SSIS has transaction logic built in, provided the Distributed Transaction Coordinator (MSDTC) is running on the deployment environment. Our deployment servers do not have this process running, therefore I had to apply my transaction logic manually.

Luckily the solution is pretty straightforward: for every task using a database connection, we can use database transactions. See the following example:

We have a very basic SSIS package. It reads employee data from a source and stores the new employees in the employee dimension.



After the data flow has finished, the Execute SQL Task issues an update statement against the dimension that will update the parent-child relationship. In other words, it will set the foreign key relationship from a child pointing to the primary key of the parent. The update statement also allows employees to change who their manager is for example.

The requirement is that when the update statement fails, the inserts from the data flow are rolled back. Since we cannot use MSDTC, we need an alternative.


Using SQL Server Database Transactions

If the tasks that need transactions all use the same connection manager, we can leverage database transactions to achieve our requirement. Let's start by putting an Execute SQL task before the data flow and connect both of them with a Success precedence constraint (the green arrow). This task uses the same connection manager as the data flow and the Execute SQL Task. The task specifies one simple SQL statement:

BEGIN TRANSACTION;

This statement will start an explicit transaction on the current connection. After the Execute SQL Task we put another Execute SQL Task and we connect both of them again with a Success precedence constraint. Again, this task uses the same connection manager as the other tasks. The tasks has the following SQL statement:

COMMIT TRANSACTION;

This statement will commit the explicit transaction on the current connection if the Execute SQL Task with the update statement has finished successfully.




But what if the update statement fails? In that case, the SSIS package will fail because an error occurred and it will stop running. Any remaining open connections will be closed when the SSIS package is shut down. The SQL Server database engine will roll back the transaction when the connection is closed. If you prefer to have more control over when the transaction is rolled back, another Execute SQL Task can be added to the control flow. This task uses a Failure precedence constraint (the red arrow). This task explicitly rolls back the transaction with the following statement:

ROLLBACK TRANSACTION;

This approach is more robust and it allows you to add some possible clean-up code.




SQL Server Integration Services Connection Manager

However, when we run the package, the following error is returned by SSIS:

Executing the query "COMMIT TRAN;" failed with the following error: "The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION."




This is caused by the fact that the connection manager actually doesn't create one single connection. When running the package, multiple connections are created for the different tasks. You can look at the connection manager as if it is a little factory, creating connections for all the tasks. This means that when SSIS reaches the final Execute SQL Task, the connection from the first Execute SQL Task - the one that starts the transaction - is already closed. Hence, SQL Server doesn't find the corresponding BEGIN TRAN, so it returns an error. How can we solve this issue? The connection manager has a very useful property called RetainSameConnection. By default this property is set to false. When we change it to true, we tell the connection manager to create only a single connection which will be shared amongst the different tasks.



This will now run successfully.

Using SQL Common Table Expressions (CTE)

A common table expression (CTE) can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query.

A CTE can be used to:

•Create a recursive query.
•Substitute for a view when the general use of a view is not required; that is, you do not have to store the definition in metadata.
•Reference the resulting table multiple times in the same statement.

Using a CTE offers the advantages of improved readability and ease in maintenance of complex queries. The query can be divided into separate, simple, logical building blocks. These simple blocks can then be used to build more complex, interim CTEs until the final result set is generated.

CTEs can be defined in user-defined routines, such as functions, stored procedures, triggers, or views.

A CTE is made up of an expression name representing the CTE, an optional column list, and a query defining the CTE. After a CTE is defined, it can be referenced like a table or view can in a SELECT, INSERT, UPDATE, or DELETE statement. A CTE can also be used in a CREATE VIEW statement as part of its defining SELECT statement.

The basic syntax structure for a CTE is:


WITH expression_name [ ( column_name [,...n] ) ]
AS
( CTE_query_definition )


The list of column names is optional only if distinct names for all resulting columns are supplied in the query definition.

The statement to run the CTE is:


SELECT
FROM expression_name;


Heres a full example:

USE AdventureWorks2008R2;
GO
-- Define the CTE expression name and column list.
WITH Sales_CTE (SalesPersonID, SalesOrderID, SalesYear)
AS
-- Define the CTE query.
(
    SELECT SalesPersonID, SalesOrderID, YEAR(OrderDate) AS SalesYear
    FROM Sales.SalesOrderHeader
    WHERE SalesPersonID IS NOT NULL
)
-- Define the outer query referencing the CTE name.
SELECT SalesPersonID, COUNT(SalesOrderID) AS TotalSales, SalesYear
FROM Sales_CTE
GROUP BY SalesYear, SalesPersonID
ORDER BY SalesPersonID, SalesYear;
GO

Validate SQL Syntax without Committing

Previously, when I had written a SQL query and needed to validate the syntax without actually executing the query, I would wrap the query in a transaction and omit the COMMIT statement (I would use a ROLLBACK instead). This worked fine, but then I discovered the NOEXEC setting in SQL.

The NOEXEC setting allows you to set the context of your execute to On or Off. For example:

USE AdventureWorks2012
GO
-- Change Setting of NoEXEC to ON
SET NOEXEC ON;

-- INSERT Statement
INSERT INTO HumanResources.Shift
(Name,StartTime, EndTime,ModifiedDate)
VALUES  ('Test', GETDATE(), GETDATE()+1, GETDATE());

-- Change Setting of NoEXEC to OFF
SET NOEXEC OFF;
GO

This statement will insert zero rows into the Shift table.

Even though we have an INSERT statement right before SELECT statement, there is no impact of the INSERT statement because we have executed SET NOEXEC ON before the INSERT. When Setting NOEXEC is set to ON, it will validate the syntax of the statement but will not execute it. If there is an error it will display the error in the console.

Remember to set the value of NOEXEC statement to OFF right after your test or otherwise all of your statements will not execute on SQL Server.

Simple SQL Server Search

Here is a quick way of searching your SQL Server database objects for a specific string. This statement will search stored procedures, functions, triggers and views for a string you provide in the tag.

DECLARE @SEARCHSTRING VARCHAR(255)
SELECT @SEARCHSTRING = '<search string>'
SELECT DISTINCT sysobjects.name AS [Object Name] ,
case when sysobjects.xtype = 'P' then 'Stored Proc'
when sysobjects.xtype = 'TF' then 'Function'
when sysobjects.xtype = 'TR' then 'Trigger'
when sysobjects.xtype = 'V' then 'View'
end as [Object Type]
FROM sysobjects,syscomments
WHERE sysobjects.id = syscomments.id
AND sysobjects.type in ('P','TF','TR', 'V')
AND sysobjects.category = 0
AND CHARINDEX(@SEARCHSTRING,syscomments.text)>0

Note that the statement doesn't search through cells, just object definitions (excluding tables). Happy debugging!

Flipping a Sql Server Bit value

In a language like C# you can use ! for a bool to make it become true if it was false and vice versa, for example:

bool a = true;
a = !a;
Console.WriteLine(a.ToString());
a = !a;
Console.WriteLine(a.ToString());
Console.ReadLine();

The output will be of the above statement will be:

False
True

How can you do this in SQL Server? It is pretty easy and there are several ways. Lets take a look...

Method 1 (most efficient & recommended) - Bitwise NOT Operator:

SELECT ~CONVERT(bit,0)
SELECT ~CONVERT(bit,1)

The ~ symbol is the Bitwise NOT operator, here is what books on line has to say about the Bitwise NOT operator:

The ~ bitwise operator performs a bitwise logical NOT for the expression, taking each bit in turn. If expression has a value of 0, the bits in the result set are set to 1; otherwise, the bit in the result is cleared to a value of 0. In other words, ones are changed to zeros and zeros are changed to ones.

Method 2 - Brute Force method:

There is the brute force method. I like this method primarily because it is somewhat self-documenting and there is absolutely no question what the coder intends to do here.

USE Northwind
UPDATE products SET discontinued = CASE WHEN discontinued = 1 THEN 0 ELSE 1 END

Method 3 - Bitwise XOR Operator:

This option is to use the bitwise XOR operator with either a ’0′ or a ’1′. What does this do? The XOR operator returns ’0′ if the two arguments match and ’1′ if they don’t.

USE Northwind
UPDATE products SET discontinued = discontined ^ 1

Method 4 - Use MOD:

Finally, one may use MOD:

USE Northwind
UPDATE products SET discontinued = ((discontinued + 1) % 2)

SQL OUTPUT Clause

Technology: Microsoft SQL Server 2008 R2

Problem: I have a query which inserts multiple new records into an existing table. I need to retrieve the primary key IDs for these new records once they are inserted. I cannot use SCOPE_IDENTITY() as this returns only the last identity value inserted into an identity column.

The solution is to use the SQL OUTPUT clause. This clause has access to the INSERTED and DELETED virtual tables. In my scenario, I used OUTPUT with the INSERT clause, but it can also be used with UPDATE, or DELETE to identify the rows affected by these statements.

Firstly, I created a table variable to store off the new IDs for the inserted records. Note that you could also use a standard table or a temp table. Table variable worked best in my case.

DECLARE @NewIDs TABLE (Id int, CustomerName varchar(50))

I can then proceed to do my INSERTS via some SQL...

INSERT MyDestTable (CustomerName, Description, CreatedBy, CreatedDate)
OUTPUT Inserted.ID, Inserted.CustomerName INTO @NewIDs 
SELECT CustomerName, Description, SYSTEM_USER, GETDATE()
FROM MySourceTable 

The above query will select all records from 'MySourceTable' and insert them into 'MyDestTable.' (I've kept the select simple here, but in a real world scenario we would probably want to add a where clause or some joins to pull back only the records we need to insert, instead of the whole dataset). By adding one line between the INSERT and SELECT, I now have access to all the newly inserted records and their auto-assigned IDs:

SELECT Id, CustomerName FROM @NewIDs