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. Continue Reading