I’ve been developing SSIS for quite sometime now and below are some tips and tricks I’ve used to make solution to be more consistent, fast and efficient.

1. Shape names (tranform shapes), use prefix, I use a 3 letter acronym. For ex. Execute SQL Task name it like EST Update Status.

2. Do use  Sequence Container to group related task/shapes.

3. Variables 

3.1 Limit the scope, create the variables closer to the shape that would use it.

3.2 Use Pascal casing for variables that will be configurable in dtsConfig and camel casing for the rest. This will make your life easier, by the time you’re configuring the dtsConfig it will be easy to identify which ones are to be included.

3.3 Variable access – instead of manually adding ReadOnlyVariables and ReadWrite Variables on the Script Task, you can use the script below to access and set the values of the variables without having to encounter the nasty variable is locked error.

Private Function GetValue(ByVal variableName As String) As Object
Dim var As Variables
Dim objVal As Object
Dim varName As String = "User::" & variableName
Dts.VariableDispenser.LockForRead(varName)
Dts.VariableDispenser.GetVariables(var)
objVal = var(varName).Value
var.Unlock()
GetValue = objVal
End Function

Private Sub SetValue(ByVal variableName As String, ByVal value As Object)
Dim var As Variables
Dim varName As String = "User::" & variableName
Dts.VariableDispenser.LockForWrite(varName)
Dts.VariableDispenser.GetVariables(var)
var(varName).Value = value
var.Unlock()
End Sub
End Class

4. Error Handling –  Use precedance constraint to handle failure instead of using Event Handlers.  The reason for this is that, it’s more simple, clean and easy to spot. 🙂 Also, sometimes using event handler will give you weird cryptic error that is both waste of time and unnerving.

5. Package properties–  Set the Protectionlevel to DontSaveSensitive and SuppressConfigurationWarning to True.

Setting the protection level will save you from:

Failed to decrypt protected XML node “DTS:Password” with error 0x8009000B

this is assuming that you are using Integrated Security or will going to use SQL Configuration Table see Item# 8.

SuppressConfigurationWarning, just like what the property indicates. This maybe because the location of dtsConfig in your production is different from your virtual or dev machine.

6. Bulk Update – You might be tempted to use a OLEDB Command to perform an update, but this is the slowest possible way since it means that this command will be executed multiple times based on the total number of rows.

Faster way is to create a temporay table on the target database, populate that table then do an execute sql task performs the update. Aside from the normal db_datareader/writer permission you’ll need a db_ddladmin permission to drop and create tables.

UPDATE d
d.Column1 = t.Column1,
d.Column2 = t.Column2
FROM DestinationTable d
INNER JOIN TmpTable t ON (d.Key = t.Key)

7. For parallel data processing you can use the Balanced Data Distributor for SSIS, this component can be downloaded seperately from
Microsoft.

For the quick demo click here.

To download click here

8. If the number of SSIS projects will grow overtime especially when connecting to different sources maintaining the connectionString will be hard if not centralized. To implement centralize configuration for SSIS see this article:  https://randypaulo.wordpress.com/2011/12/02/ssis-centralize-connection-repository/

Lastly, make sure that connecting lines between task is straight :), it just look more professional and nice if it is.

Happy coding..