Business Intelligence Tutorial, Data Warehouse guide

You are here: Home SSIS Tutorial

SSIS tutorial

SQL server integration services tutorial

"SQL Server Integration Services (SSIS) is a platform for building high performance data integration solutions, including extraction, transformation, and load (ETL) packages for data warehousing". SQL Server Integration Services is a high performance Extract-Transform-Load (ETL) platform that scales to the most extreme environments.

This section will contain some useful tips and tricks while using SSIS. This includes some of the real time problems or errors and way to troubleshoot them. If you are looking for step by step detailed tutorial, please refer here



SSIS best practices - Use logging only where it is necessary

E-mail Print PDF
When you insert data into your target SQL Server database, use minimally logged operations if possible. When data is inserted into the database in fully logged mode, the log will grow quickly because each row entering the table also goes into the log.

Therefore, when designing Integration Services packages, consider the following:
  • Try to perform your data flows in bulk mode instead of row by row. By doing this in bulk mode, you will minimize the number of entries that are added to the log file. This reduction will improve the underlying disk I/O for other inserts and will minimize the bottleneck created by writing to the log.
  • If you need to perform delete operations, organize your data in a way so that you can TRUNCATE the table instead of running a DELETE. The latter will place an entry for each row deleted into the log. But the former will simply remove all of the data in the table with a small log entry representing the fact that the TRUNCATE occurred. In contrast with popular belief, a TRUNCATE statement can participate in a transaction.
  • Use the SWITCH statement and partitioning. If partitions need to be moved around, you can use the SWITCH statement (to switch in a new partition or switch out the oldest partition), which is a minimally logged statement.
  • Be careful when using DML statements; if you mix in DML statements within your INSERT statements, minimum logging is suppressed.
 
More Articles...
Interview Questions Data minining blog