Ashvini Sharma

As you may know, the SSIS runtime forces use of encryption when loading or saving packages from/to SQL Server. As part of that, certificates have to be present and validated by the transport layer. If certificates are not properly setup, users get errors during deployment to SQL Server that say something like this:

The SaveToSQLServer method has encountered OLE DB error code 0x80004005 ([DBNETLIB][ConnectionOpen (PreLoginHandshake()).]Encryption not supported on SQL Server.). The SQL statement that was issued has failed.

In the current CTP builds, if you get this error message, you'll need to get a provisioned certificate and install it. A couple of links that describe this well are http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q276553 and http://support.microsoft.com/kb/318605.

We've been working with several customers in the past who ran into this issue and we weren't able to figure out what was going on due to the infrastructure used here in Redmond. Thanks for your patience, guys!

Hopefully we'll take care of this during setup so that those of you who did run into this issue don't anymore.

Zdravo

Runying, our data flow performance guru, just dropped a note with some information she thought would be generally interesting. I'm hoping she can start her own blog so you can directly appreciate her dedication and diligence, but in the meantime I continue to get the limelight.

Adjusting the following properties can have impact on parallelization during execution:

1. MaxConcurrentExecutables

This is a property on the Package. It defines how many tasks can be run concurrently. A value of -1 means the number of processors plus 2. When hyper threading is turned on, it counts the number of logical processors, not physically present ones. In the little testing that I (Ash) have done, I've not noticed significant difference (<5%) in performance between turning on hyperthreading or leaving it off. It'd be interesting to see if you see better results.

2. EngineThreads

This is a property on the Data Flow task. It defines how many worker threads the engine scheduler will create. Note that this is a hint to the engine and does not enforce the total number of threads in the thread pool so that if the scheduler predicts scheduling different components on the same thread might lead to a deadlock, we'd create more threads. As written on the previous thread, assume each source gets one thread, each destination gets another thread, and each component that has an output that's not in sync with the input (sort, agg, merge join, etc.) would get its own thread by the scheduler. Of course certain components (like sort and merge join) might create their own threads to manage their workload.

On topic that I've been meaning to blog about for a long time now is the execution plan that the pipeline uses for scheduling components and how you can read it to understand whether more EngineThreads could be used for improving the performance. Until then, the rule of thumb listed in the previous paragraph would have to be combined with empirical results.

3. MaxConcurrent

This is a property on the ForEachLoop which says how many instances of the loop contents can be run in parallel.