Little Known SSIS Features

Nick, our intrepid server and runtime tester, among other things, has gotten the blog bug and has written up some excellent information. I've posted it here for you folks. Please let us know if this is interesting to you.

----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------

Here I will discuss several features that most likely won’t be documented in book online nevertheless may be interesting and useful.

SSIS Server Logging

May be the least known feature is SSIS Server logging. You can turn it on by modifying %ProgramFiles%\Microsoft SQL Server\90\DTS\Binn\msdtssrvr.exe.config configuration file. Don’t confuse it with MsDtsSrvr.ini.xml configuration file.

It contains the following node:

  <system.diagnostics>

    <switches>

      <add name="TraceClientConnections" value="0" />

      <add name="TraceManagementCalls" value="0" />

      <add name="ServerTraceSwitch" value="0" />

    switches>  

  system.diagnostics>

We need to change it to:

  <system.diagnostics>

    <switches>

      <add name="TraceClientConnections" value="4" />

      <add name="TraceManagementCalls" value="4" />

      <add name="ServerTraceSwitch" value="4" />

    switches>

      <trace autoflush="true">

        <listeners>

              <add name="FileLog" type="System.Diagnostics.TextWriterTraceListener" initializeData="c:\myListener.log"/>

        listeners>

      trace>

  system.diagnostics>

Numbers have the following meaning:

0 – Don’t log

1 – Log only errors

2 – Log errors and warnings

3 – Log more detailed error information

4 – Log verbose trace information

Now, once SSIS Server is restarted log records similar to the following are written to c:\myListener.log log file as specified in the configuration file:

Reading configuration file D:\Program Files\Microsoft SQL Server\90\DTS\Binn\MsD

tsSrvr.ini.xml

Config processed: 2 root folders

RegisterComObject: Enter

DllGetClassObject: 0x0, System.__ComObject

CoRegisterClassObject: 0

DllGetClassObject: 0x0, System.__ComObject

CoRegisterClassObject: 0

RegisterComObject: Leave

Version query

Version query

GetRunningPackages

Opening client's process to get Exited event.

Registering package Package (2554c9d6-c3eb-4a86-8d18-3aaae2b9f56e) for REDMOND\usr, assigned ID f8d6ea1d-dcca-4dc0-a07a-82482102bafd

Package Package (2554c9d6-c3eb-4a86-8d18-3aaae2b9f56e) registered by REDMOND\usr, assigned ID f8d6ea1d-dcca-4dc0-a07a-82482102bafd

Unregister package request: f8d6ea1d-dcca-4dc0-a07a-82482102bafd

Package unregistered: f8d6ea1d-dcca-4dc0-a07a-82482102bafd

SSIS Server Caching

Beside package execution monitoring and storage management SSIS Server also acts as a system wide cache for several metadata collections.  

Let me illustrate how it works:

1. Assume we need to load a package P
2. To load the package we need to create an Application object
3. When application object created, it populates a metadata collection of all installed tasks, connections, log providers, for-each enumerators and pipeline components.

Step 3 usually takes significant time. The price needs to be paid every time an application object gets created. 

SSIS Server designed to cache this information. Once it's done, clients get this information directly from the server. The following scenarios benefit from the caching the most:

·        You execute a package with several Execute Package Tasks. Unless SSIS Server running all metadata collections populated for every Execute Package Task.

·        It can also speed up startup time when SSIS solution loaded into BI Dev Studio.

Note that if you have your server running and install or uninstall a custom task, connection manager, log provider or pipeline component – your changes won’t be visible until SSIS Server restarts.

Kirk : I also blogged about this a while back here

Shared MSDB Store

In many scenarios it makes sense to have several central servers that store all packages need to be run.

You can run a package named Package saved on CentralStore machine using this command:

DTEXEC /Server CentralStore /DTS "\"/MSDB/Package\""

Here are disadvantages of this method:

• Every user who runs the package need to know if SSIS Server running on CentralStore machine otherwise he/she may need to load the package directly from a SQL Server.
• Every user who runs the package need to remember and type every time the server name and in some case the named instance of the SQL Server.

It makes more sense to work against local server and let it look up a corresponding server on the network. This way we can leave the configuration for an admin and let regular users to run packages without detailed knowledge where it stored.

Here is how it can be achieved. By default, MsDtsSrvr.ini.xml configuration file defines MSDB store to be located on the local machine. To create a shared MSDB store the configuration file need to be changed to point to a SQL Server that will be used to implement the shared store. Similarly the configuration file can be customized to have several shared stores. The file needs to be changed on every machine you use running SSIS packages. For example:

Default node

      MSDB

      .

Need to be changed to:

      MSDB1

      CentralStore\sql2005

      MSDB2

      CentralStore\sql2005_backup

Once it’s done the following command will execute the same package regardless the machine it runs form.

DTEXEC /DTS "\"/MSDB/Package\""

----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------

These are some great tips Nick, thanks.

Hope this helps,

Nick Berezansky

Universe.Earth.Software.Microsoft.SQLServer.IS.KirkHaselden