Blogs From The Inside Series.
Reproduced with kind permission from the blog of Kirk Haselden(MSFT)
Integration Services is of all things a platform. While some platforms are pretty useless until you actually build something with them, SDKs and APIs like DirectX etc (Yes, definitions of "platform" differ) others are extremely functional out of the box, Windows, Office etc.
Integration Services as a Platform
Integration Services is a platform in every sense of the word of the latter type. You can use it out of the box to generate functionality. You can build components that plug into it. You can build them in any COM conformant language or .NET language as well. For lack of better nomenclature, or ignorance thereof, I call this "Building Into".
You can also fill components with code that gets executed (Script Task, SQL Task, Script Transform). "Building up"
You can build applications with SSIS embedded in the typical object model sense. "Building in"
You can just use SSIS to create packages and put together packages for building a solution. "Building on"
There's a cost associated with this. SSIS needs to keep track of all this "stuff" that's going on. Where are the components. What kind are they, what are their capabilities and how do those components wish to be instantiated or persisted etc. It's surprising how much code in SSIS is devoted to managing, enumerating and tracking all this information.
Start up time for SSIS
We regularly get complaints that the startup time for packages is really long etc. It's true. In some cases, it's avoidable. We're looking at ways to make this better. In other cases, there are some things you can do to make it better as well.
What's one got to do with the other? Well, all those components need to be enumerated at some point. DTS's answer was to cache the component information in registry. Integration Services has many more component types and also supports both COM and managed components which include Logging Providers, Transforms, Source and Destination Adapters, Tasks, Connection Managers, and ForEach Enumerators. Managed components aren't really registry friendly. It is after all a different paradigm.
Integration Services uses the service to enumerate these components. It's running all the time. (It should be. If you're doing work in SSIS, you should have the service running.), and in case of reboot etc., it refreshes the cache automatically,
So, what happens when we enumerate components?
The runtime does a few things. It checks the registry for the following registry entry.
SOFTWARE\\Microsoft\\MSDTS\\Setup\\DtsPath
This tells IS where SSIS is installed. Typically, that value is something like:
C:\Program Files\Microsoft SQL Server\90\DTS
Yes, yes, I know. DTS is all over the place. Trust me, we would have changed it if we could, but that's a whole 'nuther blog entry.
If you go to the folder specified in that registry entry, you should see some subfolders named Connections, ForEachEnumerators, PipelineComponents, Tasks, and LogProviders. There are some others there as well. That's another blog too.
Now, the runtime looks in those folders for the managed components. If it finds the managed component there, it reflects on it. Interrogates it for things like it's component type, properties, Icon etc. This is how all the Infos collections on the Application object get populated. Or at least, this is part of how it works, for managed components. .Net has no notion of "component categories". It's a COM way of marking components as being in a certain class of components that supports certain interfaces. So, we've resorted to dropping the managed components into these folders. The different folders are really just to make it easier to organize more than a technical necessity.
Once all the managed components have been enumerated, the runtime looks for native components. This is a little easier because the search is assisted by "Component categories". All we have to do is enumerate the components that are DTS components.
The service communicates with the runtime to retrieve this information and stores it for the next time you load a package.
Summary and tips
Bottom line is, there's a lot happening behind the scenes. To make startup better, keep the service running. Use the designer to design packages only. If you want to gauge performance or startup time, use DTExec.exe. There is very little overhead when running DTExec with the service running. If you don't need breakpoints while executing a package in the designer, use the execute without debugging feature found in the debug menu. That launches the package without attaching the debugger etc. If you're debugging in the designer, shut down as many of the windows as you can. Having 4 watches, the memory and output window all open at the same time along with the tools and project windows isn't really necessary while debugging. In fact, the VS environment will remember which windows you have open during execution vs. design time. Only open the windows you need during execution, that has a profound effect on perf.