Best Practices – SSIS II

I wrote the best practices based on my experience in my first project. Now, I’ve worked a little more than that in my second project. Here, I give you some best practices in Bulk export, and FTP.

---

1. Bulk Export – In order to export the records from one or more tables or views, we can use bcp command. The syntax goes like 

bcp 

<query along with the database name> queryout <file name> -c –S <DBserver name> –U <username of the db server> -P <password of the db server

> 

 
Include the options that you need.

Write this bcp command in a bat file and with the help command argument variables %1, %2, %3, etc. So that you can execute this bat file using Execute Process Task (EPT) and make use of expression tab to pass arguments to this bat file.

---

2. FTP – If you like to capture the error occurred in FTP server explicitly then you can go for EPT. I’ll give you some examples. Errors like login failed, invalid remote directory cannot be distinguishable in FTP task.

Pre-requisites to understand: Should know dos commands such as “echo”, “>”, “>>” and how to pass arguments to a bat file, standard output file and standard output error file.

Pass arguments to the bat file with the ftp credentials and “echo” those credentials to a file and execute the file having ftp command using “ftp –s:<file_name> <server_name>”

If you want to know more, write to ramjip.ramji@gmail.com

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.

---