Logging


SSIS has extensive logging built into its architecture to log into a variety of destinations, but typically you would log into a SQL Server table or a text file. If you were to log into a SQL Server table, you could have multiple packages all logging into the central table.

The standard table that SSIS will use for logging is called sysdtslog90. A big weakness of this logging provider for SQL Server is that you cannot log into a custom table, and some of the columns that would be real useful for ease of use aren’t there. That’s typically why you should use event handlers for logging. Regardless, logging using the logging providers is extremely easy to configure, and it does contain more information in some cases than can be obtained through an event handler like the pipeline events.

This section walks you through some common scenarios and shows you how to get over some of the obstacles with the logging providers. Let’s start by creating a new package called Logging.dtsx. Drag over a single Script Task and name it Write to Log. Inside the script of the Script Task, add the following code inside the Main subroutine:

 Public Sub Main()       Dim emptyBytes(0) As Byte       Dts.Log("Script error has occurred.", 10003, emptyBytes)       Dts.TaskResult = Dts.Results.Success End Sub

Close the Script Task to save the code. This code will write to whatever log provider you eventually select. This type of logic could be used to conditionally write data to your logging provider.

Your next step is to create the logging provider by selectingLogging from the SSIS menu. From the Providers and Logs tab, select the Text File provider and click Add. Select New Connection from the Configuration drop-down box and type C:\ExpertSSIS\LogFile.txt (make sure you have a C:\ExpertSSIS folder). Check the provider and all the containers in tasks on the left Containers tree, as shown in Figure 7-4.

image from book
Figure 7-4: Checking the provider and all the containers in tasks on the left Containers tree

After you check the containers, go to the Details tab. Click the Write to Log Task in the left Containers box. Note that you have an additional event that you can check now. The ScriptTaskLogEntry traps any type of log event that the script designer has built into the task. Select that event and click OK to exit.

You’re now ready to run the package. Execute the package and then view the C:\ExpertSSIS\ LogFile.txt file in your favorite text editor. If everything executed successfully, you’ll see an additional new event with the log entry the script created, as shown in this sample message:

 User:ScriptTaskLogEntry,BRIANKNIGHT,BRIANKNIGHT\bknight,Write to Log,{8212956D- A1FC-438C-9E64-D974190E3A7F},{},10/14/2006 1:07:37 AM,10/14/2006 1:07:37 AM,10003,0x00,Script error has occured.

For regulatory reasons, oftentimes you must monitor the data flow to determine how many rows are transformed from step to step in the data flow. The logging providers offer such a feature to help you meet that requirement. To experiment with the feature, drag over a Data Flow Task onto the control flow of the current package. Optionally, you can create a simple data flow to move data from a random table in the AdventureWorks database.

With the package now redesigned, go back to the Logging dialog box and to the Details tab. Notice now that the Data Flow Task is in the Logging tree on the left (shown in Figure 7-5). In the Details tab, click OnPipelineRowsSent and click OK. You’re ready to execute the package again and look at the results.

image from book
Figure 7-5: Data Flow Task in the Logging tree on the left

The OnPipeline events that you see here give you a view into the data flow pipeline. The main one you’ll use is the OnPipelineRowsSent, which tells you how many rows go from step to step in the Data Flow Task. These events are not visible in the event handlers, and the most useful way to use them is to output the results into a table for parsing. Parsing of this output may be a little complex, since it outputs each step to a single row in your text file or table. The row count is shown in the Message column and is delimited by a colon, as shown in the following log file row:

 OnPipelineRowsSent,BRIANKNIGHT,BRIANKNIGHT\bknight,Data Flow Task,{63B525E0-D4AB- 4878-BCC8-1A55918D5B50},{},11/8/2006 8:07:12 AM,11/8/2006 8:07:12 AM,0,0x,Rows were provided to a data flow component as input. :  : 21 : OLE DB Source Output : 16 : FFD Employees File : 17 : Flat File Destination Input : 290

What you see in this log snippet is that there is an OLE DB source that sends data to a Flat File Destination called FFD Employees File. The number you see before each name is the object’s lineage ID, which may prove useful in a later discussion. The last number you see (290) is how many rows moved between both the source and destination. If you had transformations in the middle of the source and destinations, you’d see multiple rows in the log file.

On the Wiley web site (www.wiley.com), you can download ParsePipeline.sql to parse the row if it’s in the standard sysdtslog90 table to something more usable. The function enables you to write a query like the following one to read data out of the message column into a report. This query is also available in the same script file as the function at the end.

 SELECT     source, sourceid, executionid, dbo.ParsePipeline(message, 1) AS PathID, dbo.ParsePipeline(message, 2) AS PathIDName,                       dbo.ParsePipeline(message, 3) AS ComponentID, dbo.ParsePipeline(message, 4) AS ComponentIDName, dbo.ParsePipeline(message, 5) AS InputID,                       dbo.ParsePipeline(message, 6) AS InputIDName, CONVERT(int, dbo.ParsePipeline(message, 7)) AS RowsSent, starttime FROM         dbo.sysdtslog90 WHERE     (event LIKE 'onpipelinerowssent%')

Creating a Unique Log File

Currently, in this example, the package log will always be called LogFile.txt. This is probably not going to be acceptable, since you may have 50 packages all running and writing to LogFile.txt. Instead, a common request for log files is to name the file based on the package name and timestamp the file. For example, a package named LoadDW.dtsx running on 1/24/2006 would be named LoadDW01242006.log.

Let’s expand the previous example slightly to achieve this technical goal. To do this, you want to make the connection manager you created in the last example for the log file to be dynamic using expressions. Select the connection manager for the log file and create a new expression in the Properties window. Select the Connection String property, then click the ellipse button to enter the Expression Builder window. In the Expression box, type the following expression to fulfill your requirements. This code can also be downloaded at www.wiley.com.

 "C:\\ExpertSSIS\\" +  @[System::PackageName] + "_" + (DT_STR,4,1252)DATEPART( "yyyy" , @[System::StartTime]   ) + "_" + RIGHT("0" + (DT_STR,4,1252)DATEPART( "mm" ,  @[System::StartTime]  ), 2) + "_" + RIGHT("0" + (DT_STR,4,1252)DATEPART( "dd" , @[System::StartTime]   ), 2) + "_" + RIGHT("0" + (DT_STR,4,1252)DATEPART( "hh" , @[System::StartTime]   ), 2) + RIGHT("0" + (DT_STR,4,1252)DATEPART( "mi" ,  @[System::StartTime]  ), 2) + RIGHT("0" + (DT_STR,4,1252)DATEPART( "ss" , @[System::StartTime]   ), 2) + ".log"

This code will name the file based on the package name, and then append a timestamp at the end separated by underscores. The double backslashes are required in the path, since a single backslash is an escape character. The RIGHT function enables you to easily guarantee that you will always have two characters for the month and day, for example. Otherwise, months like January would be trimmed to only a single digit (1) instead of two digits (01). When you run the package again, you’ll now see that the log file is timestamped.

Reporting on the Logging Providers

Ideally, you’ll want to send all the package logs to a single database that is used for auditing of your entire environment. You can create a nice flexible data structure as shown in the previous section and log through event handlers, or you can log into the sysdtslog90 table for all the packages in your environment. If you do the latter option, you must create reports to slice the events by package and drill into the events.

Luckily, though, Microsoft has already created such a series of reports called the SQL Server Integration Services Report Pack. If you search the site for this (or go to www.microsoft.com/downloads/ details.aspx?FamilyID=526e1fce-7ad5-4a54-b62c-13ffcd114a73&DisplayLang=en), you’ll be able to download a series of reports to help you slice the newly created mountains of diagnostic data. If nothing else, it will carry you 90 percent to your goal of enterprise ETL reporting.

One such report is shown in Figure 7-6. In this report, you can see which packages are being logged, then you can drill into each execution instance of the package and see whether it succeeded or failed. Then, you can drill into the package and see how long each step took. Some SQL Server Reporting Services reports give you graphs of the execution times, and others show you the OnPipelineRowsSent data. The Reporting Services project includes a series of reports, documentation, and a backup of a database that’s already populated. You’ll be able to customize it from there.

image from book
Figure 7-6: Sample report



Expert SQL Server 2005 Integration Services
Expert SQL Server 2005 Integration Services (Programmer to Programmer)
ISBN: 0470134119
EAN: 2147483647
Year: 2004
Pages: 111

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net