First published on MSDN on Feb 27, 2007
You can see all of this information on
MSDN in the SP2 Readme
, but I thought I'd highlight a couple of things.
Diagnostic Logging
A lot of trace logging was added in SP2 to help with debugging connectivity issues. These messages are logged using the "Diagnostic" event. A side effect of this is that if you had Diagnostic level logging enabled before, you are now getting
a lot
more messages. In SP1 there was one Diagnostic message per run – a package level event about the max concurrent threads. In SP2, you typically get two messages per database API call (pre and post call messages). If you're seeing any performance decrease in package execution after upgrading to SP2, you should check your logging settings to make sure that you don't have Diagnostics enabled.
It is recommended you only enable Diagnostic logging for debugging purposes – specifically when you're hitting connectivity issues.
BypassPrepare
This property in the Execute SQL Task is now set to True by default, which should prevent a lot of confusion when using parameterized queries. Preparing the statement can give a minor performance increase for certain statements, but should only be set to false when you're executing a statement without any parameters.
ParameterSize
A new column was added to the Parameter Mapping tab of the Execute SQL Task for the new ParameterSize field.
This was a fairly big change to address a number of issues with variable length output parameters (the main issue being they didn't work). I had
previously posted
about using output parameters as an alternative to a result set, and provider syntax issues aside, they work fairly well with certain types. However, differences in the way each provider handles output parameters led to a number of problems with the original implementation when dealing with variable length types (strings, mostly).
With this change you are able to specify a length, or use the default value (-1) to let SSIS guess at the correct size. When the default is used, we retrieve the size information directly from the provider if supported; otherwise we use a reasonable default. In most cases the default value should be good enough, but there are some situations where you might want to use a specific value – 1) our "reasonable default" isn't reasonable enough, 2) you want to truncate the returned value to a certain size/length. It should be noted that some providers (ADO for example) don't support truncation, and will fail if the parameter size isn't large enough.
