SSIS Tips & Tricks

In this blog I listed the simple tips & tricks which can be used in SQL Server SSIS packages.

• On Error Event:
  On Error event is a cascading event.  So when an error occurs on a task the event is raised for all the level up from the task to package.  So you should not place Error Email Notification tasks in OnError task.  You can log or accumulate the error details to send it across.
• Constraints
  You can implement logical And, Or on task constraints.  You need to set the flat in constraint property.
  
• Store configuration in SQL Server
  You can store the Package configuration in SQL, while doing that it is recommended to store the connection string of the configuration database in Environmental Variable.  While configuring environment variable make sure you are using System Variable and not User Variable.
  
• Source with variables
  In my ETL I had a scenario in which I need to keep the variable as a source data.  Just as work around I have selected dummy data from database and using derived column to add variables as additional column.  It is simple tip to create variable values as a Source Component.
• Event instead of Exception
  In Script task don’t use throw new Exception.  Instead use Dts.Events.FireError method.