Templates in SSIS provide a great way to create standards across your company or enterprise. For example, you may want to enforce a standard annotation set of notes at the top of each package or have each package come with a standard set of connections or error handlers to speed up development. To use a template, first create a package just as you would want to see it as a template. Add all the connections, tasks, comments or log providers. After the package meets your needs, copy it to the following directory (of course replace %Program Files%) : %ProgramFiles%\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies\ProjectItems\DataTransformationProject\DataTransformationItems
For a developer to consume the template, he must only right-click at the project-level node in BIDS and select New Item. You'll see the package you copied over there and once selected, all your information you created earlier is ported over.
-- Brian Knight
SSIS has the ability to do very detailed logging that puts DTS Package Logs to shame. In the past, you would have to had to write customized logging routines to do what SSIS does out-of-the-box. SSIS offers a number of logging providers that you can select by going to SSIS menu and selecting Logging. You must then chose what type of provider you wish to use for logging. Which provider you chose depends on your goals:
- SQL Server Profiler create a profiler trace file - This option gives you some interesting ties in with Profiler if you're examining performance issues like long-running packages. With this seamless integration, you can quickly tie the log to a System Monitor trace to see performance counters.
- SQL Server Table writes events to a SQL Server table. - This option is my personal favorite since it allows for rich reporting using Reporting Services or the reporting tool of your choice. This is a must-have for most environments that need operational reports in real-time. I like to create reports against the table that refresh so you can see all the running packages and their progress.
- Windows Event Log writes each event to the Windows Application log - What makes this choice a nice one is it creates hooks for monitoring products like Tivoli or Microsoft Operations Manager (MOM) to use for alerts.
- XML File format writes each event to a structured XML file - This can also be used for reporting on a website if you use an XSLT file for formatting. It works well if you need to share the information with a 3rd party especially.
- Text file writes to a simple flat file - This option is the easiest to configure and is the most understood by traditional tech-heads.
What's especially nice about the new logging mechanisms is you don't have to chose just one. You can use for example the table for relational reporting and the Windows Event log for operational monitoring tools.
-- Brian Knight