2 days ago Donald Farmer from the SSIS development team presented a webcast from TechEd entitled "SQL Server 2005 Integration Services: Performance and Scale (Level 400)"
He went over the OVAL (Operations, Volume, Application, Location) approach to building highly performant and highly scalable SSIS solutions (as he has done before) but this time there was a little more substance to it and also some fantastic demos showing how to determine where the bottlenecks in your packages may be. And of course it was delivered with Donald's usual alacrity and passion for the material.
He also answered a few questions that are persistently getting asked on the SSIS forum such as:
- Why is the SSIS expression language a C-style language?
- Why does SSIS enforce explicit type casting to a greater degree than DTS did?
Some people have a hard time making the jump from DTS to SSIS and these are two of the more common complaints so I was delighted to see them covered them off.
One other thing I picked up is that selecting a table/view from the drop-down list in OLE DB Source will produce different behaviour than writing a SQL query. The former uses the OPENROWSET command whilst the latter issues straight SQL.
Another important point that Donald made is around the issue of when and where to cast values to DT_DBTIMESTAMP or any of the other datetime types in SSIS. In fact - do you even need to do it at all? Donald provides some insightful stats around this that everyone needs to be aware of.
A really great education for anyone that wants to build highly performant SSIS solutions. Click here to go see it.
-Jamie