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