Continuing the coverage of debugging features in SQL Server 2005 Integration Services Data Flow, which we started in the previous installment of this series, we will now focus on the Script Component. As we have already pointed out, some of the methods presented when discussing this subject in the context of Control Flow (in particular, the ones relying on event-generated breakpoints) are not applicable to source, destination, and transformation Data Flow components. This disparity extends even further when comparing Script Task with its Data Flow counterpart - Script Component - and takes the form of syntactical and functional differences between them.
As you might recall, the main purpose of the Script Task is to execute custom Visual Basic .NET code, which performs actions not available via the built-in tasks and containers. This activity-oriented focus reflects its Control Flow characteristics. Script Component, on the other hand, while using a similar implementation approach (based on the same programming language), was designed to satisfy the need for extending the range of Data Flow features beyond an out-of-the-box selection of data sources, transformations, and destinations. While SQL Server 2005 Integration Services already includes a variety of predefined, ready to use components, they are simply not suitable for every type of data and its modifications. Flexibility of the Script Component allows you to address the challenges of processing unusually formatted data or applying complex business rules.
You need to decide which Script Component type to create as soon as you add it to the Data Flow area of SSIS designer - by selecting one of three option buttons in the Select Script Component Type dialog box:
- Source Script Component - intended for access to non-standard data sources, includes Connection Manager and Script sections, allowing you to specify a data store (in the form of a connection manager) from which the component extracts the information and to write code responsible for performing this activity, respectively. Its distinguishing feature is the content of Inputs and Outputs section, where you can define multiple outputs, but no inputs. We will discuss the usage of these settings in more detail in the next article of our series.
- Destination Script Component - like the Source Script Component, it contains identically structured Connection Manager (for designating target data stores) and Script sections. What makes it different is the content of the Inputs and Outputs section (with a single, already present by default, input entry and the possibility of creating multiple outputs), as well as the Input Columns section, where you specify data columns that you want to be accessible from the scripting environment. Our next article will also include more information on this subject.
- Transformation Script Component - obtains its data via one input and, after its modifications are applied, feeds it to one or more outputs. Similar to Destination Script Component, it contains Connection Manager, Script, Input Columns, as well as Inputs and Outputs sections. The last one contains a single Input and a single Output entry (although you have an option of adding extra outputs, if necessary).
When working with Input Columns (in the case of Destination and Transformation Scipt Components), for each one you select, you need to assign a Usage Type parameter (which takes on one of two possible values - ReadOnly or ReadWrite depending on whether you intend to alter their content). To configure output columns, switch to the Input and Outputs section of the Script Transformation Editor dialog box. Depending on our initial choice of the script component type, you might see there one (Source and Transformation Script Component) or none (Destination Component) output nodes. To add new ones, simply click on the Add Output command button and assign a descriptive name to it. Once the desired output node is present in the Inputs and Outputs area, expand it to display its Output Columns subnode. At this point, you will be able to add output columns using the Add Column command button located underneath, and modify their properties using the Properties area to the right.
The Script section of the Script Component Editor (for source, transformation, and destination types) resembles the one appearing in the Control Flow Script Task. The two also share similar sets of properties (such as ReadOnlyVariables and ReadWriteVariables, which make possible the referencing of SSIS variables from the script). As with Script Task, clicking on the Design Script button brings up the Microsoft Visual Studio for Applications interface with auto generated code of the ScriptMain class template in it (which, in turn, contains a sub, responsible for processing individual input or output rows, depending on the component type).
Even though, as we mentioned before, standard breakpoint functionality implemented in Control Flow tasks and containers is not present in Data Flow components (as you can easily find out, the Edit Breakpoints item does not appear in their context sensitive menu), which makes it more difficult to follow the execution in real time, there are a few workarounds, which can be applied when working with the Script Component:
It is possible to invoke and log individual events at arbitrarily chosen points of Script Component execution. This is accomplished via a two step process, consisting of enabling SSIS logging and modifying the component code by adding calls to those methods of the ComponentMetaData object, which generate (or "fire", in the programming nomenclature) a desired event type (such as FireCustomEvent, FireError, FireInformation, FireProgress, or FireWarning). For more information on SSIS logging, refer to one of our earlier articles of this series. In this case, you would need to configure it on the Data Flow Task level (from the Configure SSIS Logs:Package dialog box in the SSIS designer) and ensure that the type of event you selected to be logged matches the event fired by the method you applied. (Note also that you have several additional Data Flow task-specific events for which you can enable logging, such as BufferSizeTuning, PipelineBufferLeak, PipelineExecutionPlan, PipelineExecutionTrees, PipelineInitialization, or ScriptComponentLogEntry). Events can be invoked via code in both Script Task and Script Component, however, syntax varies between them. In the case of Script Task, this is done using:
Dts.Events.EventMethod(informationCode, subComponent, description, helpFile, helpContext, fireAgain)
where, EventMethod is the name of the event method (from the list provided above) followed by its parameters (within parenthesis) describing this event in more detail, such as informationCode (an integer identifier of the event), subComponent (designating an object which raised the event), description (providing more information about the nature of the event), helpFile (referencing name of a Help file, where documentation relating to the event can be found), helpContext (pointing to the location within the Help file), and fireAgain (determining whether the event should be fired on subsequent invocations of this method). When dealing with the Script Component, the same can be accomplished by calling the above listed event methods via the ComponentMetaData property of the ScriptMain class (this is done by appending the method's name to "Me.ComponentMetaData." prefix and specifying the same set of parameters following the method name and enclosed in the set of parenthesis:
Me.ComponentMetaData.EventMethod(informationCode, subComponent, description, helpFile, helpContext, fireAgain)
Following the execution, you should be able to find information about events invoked during execution of the Script Component in a log, whose location and format would depend on the configuration parameters specified in the Configure SSIS Logs:Package dialog box. The event notifications (such as FireInformation, FireWarning, FireError, or FireProgress) will also be displayed in real time in the Progress (or Execution Results, once the package run completes) tab of the SSIS Designer interface as well as the Output window (with the exception of the FireProgress method).
If the logging is enabled on the Data Flow task level for the ScriptComponentLogEntry event on the Details tab of the Configure SSIS Logs:Package dialog box in the SSIS designer, you can also record any custom messages by applying Log method of the ScriptMain class (without the need for raising events). This will create entries in all destinations defined via log providers, which can then be reviewed after the execution completes to collect troubleshooting information. The syntax of the statement that implements this approach is as follows:
Me.Log(messageText, dataCode, dataBytes)
where messageText, dataCode, and dataBytes are intended for text, numeric, and Byte (one-dimensional array of Byte values) data to be logged.
You can apply the traditional method of displaying desired information via MessageBox.Show method of the Systems.Windows.Forms namespace. If you include the Imports directive, then you can call the method using class.method notation, otherwise, you will also need to type in the name of the relevant namespace, as in the following example:
System.Windows.Forms.MessageBox.Show("Kilroy was here")
Most of the debugging methods that we described in the previous article of this series when discussing general ways of debugging Data Flow components also apply to the Script Component. In particular, you can take advantage of the color-coded status indicators, pipeline path data record counter, Output window, or Progress (as well as Execution Results) pane of the SSIS Designer interface. While the Script Component does not contain a designated default Error Output, it is straightforward to create one (using the Inputs and Outputs section of the Script Transformation Editor) and redirect faulty rows to it (with custom code which implements the logic distinguishing them from valid ones). By connecting the output to a Data Flow destination component, errors can be separately stored and analyzed. Furthermore, it is also possible to use this ability to provide additional debugging information about errors generated by other Data Flow components (by including within Script Transformation Component code translating an errorCode into equivalent, but easier to interpret, errorDescription). We will demonstrate this functionality in our next article.