Row Insert from SSIS package Vs Transact-SQL Statements

SQL Server writes transaction logs sequentially. The size of transaction logs when using sql INSERT statement depends on the Recovery Model of the database and the type Transact-SQL statements used. Database recovery model can be – Simple (min), Bulk-Logged (intermediate) or Full (max). While this is true for Transact-SQL INSERT statements, records inserted through an SSIS Data Flow Destination (like SQL Server Destination) exhibit a different logging behaviour. This is because the INSERT statements issues from Integration Services engine is always minimally logged and it bypasses the recovery model of the database engine. This results in significant performance improvement by reducing the sequential disk writes, especially when dealing with huge recordsets which is typical in a data warehouse ETL scenario. That’s another reason why SSIS packages perform better over Transact-SQL Stored Procedures for ETL purposes.

Here is Proof

I performed a simple test by inserting 19,815 records from AdventureWorks Customer table to a copy table using a Transact-SQL Stored Procedure (shown below) and an SSIS Package (Screen Capture 1). The database was set to Simple recovery mode.

CREATE PROCEDURE spMinimal
AS
BEGIN

DECLARE  curCustomer  CURSOR FOR
SELECT [TerritoryID]
       ,[CustomerType]
      ,[rowguid]
      ,[ModifiedDate]
  FROM [AdventureWorks].[Sales].[Customer]

DECLARE @intTerritoryID AS int
DECLARE @charCustomerType AS nchar(1)
DECLARE @rowguid AS sysname
DECLARE @ModifiedDate as datetime

truncate table Sales.Customer_TSQL1

OPEN curCustomer
FETCH curCustomer INTO @intTerritoryID,@charCustomerType,@rowguid,@ModifiedDate

WHILE @@FETCH_STATUS=0
	BEGIN
			INSERT INTO [AdventureWorks].[Sales].[Customer_TSQL1]
			   ([TerritoryID]
			   ,[CustomerType]
			   ,[rowguid]
			   ,[ModifiedDate])
			VALUES
				(@intTerritoryID,
				@charCustomerType,
				@rowguid,
				@ModifiedDate
				)
		FETCH curCustomer INTO @intTerritoryID,@charCustomerType,@rowguid,@ModifiedDate

	END

	CLOSE curCustomer
	DEALLOCATE curCustomer
END

Screen Capture 1 - SSIS Package

 The log information was captured after execution of the Transact-SQL Stored Procedure and SSIS Package using fn_dblog() as shown below:

SELECT DISTINCT operation,[log record length],count(1) [log count]
FROM fn_dblog(null, null)
WHERE allocunitname like '%Sales.Customer_TSQL1%'
GROUP BY operation,[log record length]
ORDER BY count(1) DESC, [log record length] DESC,operation

The results showed the transaction log size and the number of transaction log entries were significantly less for the inserts from SSIS packages

Screen Capture 2 - Comparison SSIS Vs Transact-SQL Logging

 Benny Austin