System variables, logging and the Execute SQL Task...(zz)

原文地址http://sqljunkies.com/WebLog/knight_reign/archive/2005/02/27/8187.aspx
Here's something useful you can do with system variables and the SQL Task. Logging in SSIS is more flexible and there are more options for logging destinations and formats then with DTS. However, it may not always be as surgical as you'd like it to be, and you can only create log events that the components support.

There are a few ways around this as follows:

• You can use a script component or task and log custom log events “on the fly“. This has value in some cases.
• You can create a custom task in which you can create custom log events that you may actually filter. I think there is someone writing one of these now. :)
• You can build a pipeline to output to various formats.
• You can use the SQL Task to log custom data using parameter mapping and variables.

I've created a very simple package run log that illustrates the last option. Granted, the package already logs a similar set information, but it's a good example because it's easy to compare the two approaches. Using the SQL Task, I have created a simple log table that logs the username, packagename, machinename, packageID and starttime for each time a package gets executed.

First, I created a table as follows.

use Playland
CREATE TABLE PackageRun (
username varchar(50),
packagename varchar(50),
machinename varchar(50),
packageID varchar(50),
starttime datetime
);

Then in the SQL Task, I have the following insert statement:

INSERT INTO PackageRun VALUES(?,?,?,?,?);
/* username    varchar(50),
packagename varchar(50),
machinename varchar(50),
packageID   varchar(50),
starttime   date */

Here's where the system variables come in handy. The Parameter Mapping tab looks like this:

I've put this SQL Task in the package at package scope to log simple information about the run of the package. Now, whenever I run the package, it enters a log into the PackageRun table as shown below.

Existing log events cover a lot of ground. The logging infrastucture generously handles the general case for logging, but it's a broad brush and doesn't always get as detailed as you'd like. There are cases where there is no way to log specific information. For example, parameters to event handlers, the LocaleID of a specific container and task execution values. This logging mechanism can be used for just about any type of custom logging you'd like at anytime during the execution of the package and is great for one off, surgical logging of custom data that the existing log infrastructure doesn't support. If you ever run into the situation where you wished something got logged, but it doesn't, this method may fill in the gaps.

Let me know if this was useful to you.

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