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.
