Simple Tips To Speed Up Your SSIS Data Flow

There are a few design choices that can make your SSIS packages run super fast when loading data from table A to table B.

SQL Command at Source

OLE DB source editor allows you to choose either a table or a view from a drop down if data access mode is Table or View. This will execute a SELECT * command on the data source. As a best practice, it is always better to list the columns even when you are selecting all of them. Choose SQL Command and type in the query.

Table or View – Fast Load at Destination

As the name suggests, Table or View – Fast Load is the fastest way to load data to destination. It applies a table lock on the destination table and performs bulk insert. It is in arguably the fastest way to insert data.

My package on a machine with 8GB RAM and 4 processors ran for 58 seconds to load 7.9 million records. Compare this to 50 minutes when using Table or View as the data access mode. This option works similar to a cursor – inserts one record at a time. Clear evidence for it’s poor performance.

50 minutes? hell No! But 58 seconds to load about 8 million records isn’t bad. Right?

Let’s dig a little deeper to see if we can tune this even more.

Default Buffer

SSIS relies heavily on buffer. A set of records are read into the buffer, and from buffer they are written to the destination. This process continues until all rows are written to destination. For instance, in this example, buffer is carrying 9,216 rows at a time. You can see that by using data viewers.

This number is controlled by two properties DefaultMaxBufferRows and DefaultMaxBufferSize. The values are 10,000 rows and 10MB by default, whichever comes first. MaxBufferSize is 100MB – it’s the maximum size of rows that can be fitted in buffer.

EstimatedRowSize is another property that is calculated based on the metadata of our result. This is 32 bytes for my data set. (BIGINT = 8B + INT = 4B + DATETIME = 8B + INT = 4B + MONEY = 8B) = 32 Bytes.

Buffer reached the default maximum allowed with 9,216 rows, that amounts to a size of 9,216 (rows) * 32 (bytes for each row) = 294912 Bytes which is less than 1MB, remember maximum is 100MB. There is a lot of free space left on the buffer that can be used. Filling this will result in shorten trips thus increasing performance.

For this test, I left DefaultMaxBufferSize as 10MB, but increased DefaultMaxBufferRows to 30,000 rows. The package now runs in less than 30 seconds.

Changing the settings to DefaultMaxBufferSize = 90MB and DefaultMaxBufferRows = 60,000 rows resulted in the package to execute in 15 seconds.

Wrap Up…

Understanding the internals sometimes will yield great performance without the need for additional hardware. This package is running on my local machine and data is being moved within the same database. Off course results might vary depending on your environment.

~Sam.

Write a Variable To a File Using SSIS

Problem

I’ve a SSIS package. When it fails execution, I want to write a custom message including the package name and error description to a text file.

Solution

One way to accomplish this is to declare a variable, populate it with the message you want, and write it’s value to a file using a script task. Then add this script task to an OnError event handler so it executes in response to an error in the SSIS package.

1) Declare a variable: I’ve declared a package scoped variable called vFullErrorMsg.

2) Configure an OnError event handler for the package. So, the tasks in the event handler will execute when the package fails.

3) Add a script task: I renamed the task to Write To Log On Failure.

4) Edit Script Task: Add ErrorDescription,PackageName as a read only variable and vFullErrorMsg as a read write variable.

5) Use a script similar to the one below:

Public Sub Main()

‘declare a variable for error description
‘write DTS variable ErrorDescription to local variable vErrorDescription
Dim vErrorDescription As String = Dts.Variables(“ErrorDescription”).Value.ToString

‘declare a variable for package name
‘write DTS variable PackageName to local variable vPackageName
Dim vPackageName As String = Dts.Variables(“PackageName”).Value.ToString

‘Write error string to DTS variable
Me.Dts.Variables(“vFullErrorMsg”).Value =
“ERROR: ” & ” Package [” & vPackageName & “] Failed” & “. Full Description: ” & vErrorDescription

‘ write DTS variable to a file using stream writer
Using sw As StreamWriter = New StreamWriter(“D:\Documents\ErrorLog.Txt”, True)
sw.WriteLine(Dts.Variables(“vFullErrorMsg”).Value.ToString)

End Using

Dts.TaskResult = ScriptResults.Success
End Sub

With this set up, you should see the error message written to D:\Documents\ErrorLog.Txt on your local drive. You can use variables or expressions to make it work all dynamically.

:<)

Related Post: Read a flat file to a variable.

Here is my Twitter feed (@SamuelVanga) if you would like to follow me.

Read contents of a file to variable – SSIS

Sometimes you will find the need to import a file to a variable using SSIS. In this post, I’ll use Script Task to read contents of a flat file into a variable.

I’ve created a file called ‘myfile.txt’ with some random text. I’ll configure script task to read this file into a SSIS variable.

• First create a variable with string data type. I’ll call mine vText.
• Drag a script task onto the control flow. As you would imagine double clicking the script task will open the editor.
• Specify the variable you just created as a ReadWriteVariable.
• Click edit script and use code similar to what you see below:

'initialize StreamReader class for text file
Dim streamReader As New StreamReader("D:\Documents\myfile.txt")
' Read the StreamReader To End and assign to local variable
Dim StreamText As String = streamReader.ReadToEnd()
' assign SSIS variable with value of StreamText local variable.
Me.Dts.Variables("vText").Value = StreamText

To test, I’ll add another script task. This task will show a message box with the contents of our variable. Following is the script i use for message box:

MsgBox(Dts.Variables("vText").Value.ToString)

As you can see I’m reading the file from a static location on my D drive. Most people, however, will use variables or expressions to read from any locations dynamically.

@SamuelVanga.