Best Practices – SSIS

This article is usefull for SQL Server Integration Services(SSIS) developers. Here, I explained 5 best practices that I followed in my project.

Best Practice 1 – Use Bulk Insert Query:

Bulk insert is used to load data from flat file to DB. This helps to insert the data faster with the help of “Format File” (created using bcp utility) and “Error File” (to capture the error records during this transformation). Bulk insert will faster enough, if the columns of tables should be of varchar, nchar, varnchar or char. The disadvantage of Bulk Insert Task is error file can not be specified in that. Hence, use bulk insert query inside Execute SQL Task.

The syntax of Bulk Insert Query as follows:

bulk insert <Table_Name> from <Data_File>

with(FORMATFILE =‘<Format_File>’,ERRORFILE=‘<Error_File>’)

---

Best Practice 2 – Use SQL Command:

In sources, use SQL Command instead of selecting tables from the list. Since, the former is faster than the latter.

---

Best Practice 3 – Avoid Lookup, Aggregate and Sort Transforms:
These transforms invariably degrade data flow performance. If possible, eliminate them from your data flows. Use Sort and aggregate data at the source, and MergeJoin Transform instead of the Lookup, if possible.

---

Best Practice 4 – Implement Parallel Execution:
Both the execution engine for the Data Flow elements and the execution engine for the Control Flow elements are multithreaded. We can specify the maximum concurrent thread as an integer value in “MaxConcurrentExecution” property of the package.

---

Best Practice 5 – Avoid Script Component:
The Script Component hosts script and enables a package to include and run custom script code. The Script component provides a VSA project that includes an auto-generated class, named ScriptMain, which represents the component metadata. But, it will definitely consume time. Hence, try to avoid Script Component. Some of us may use it to get “ErrorDescription” for any “ErrorCode”, while capturing the error flow from any components. Look at the following sites, you could find Error Descriptions along with Error Code in decimal as well as in Hexadecimal.

1. http://msdn2.microsoft.com/en-us/library/ms345163.aspx

2. http://msdn2.microsoft.com/en-us/library/ms345164.aspx

Create a table that will store Error Code (Decimal) and Error Description (varchar). And make use of that.

---