Hadi Fadlallah
this image shows the script tab page in the ssis script component editor

SSIS Script Component Overview

October 29, 2019 by

SSIS Script component is a prominent strength of SQL Server Integration Services since it allows developers to implement any complex logic and utilize libraries from the powerful .NET framework or third-parties.

In this article, we will give an overview of SSIS Script component, and illustrate the benefits of using it. In addition, we will answer some of the most common questions asked concerning writing scripts in SSIS, such as the difference between a derived column transformation and a Script component.

this image shows the ssis script component description from the toolbox

Figure 1 – SSIS Script Component description from toolbox

SSIS Script component can be used in three ways:

  1. As a source component
  2. As a destination component
  3. As a transformation component

When adding a Script component to the data flow task, you will be asked how the script will be used, as shown in the image below:

this image shows the form used to select the ssis script component type

Figure 2 – Selecting SSIS Script component type

SSIS Script component editor

Before describing each approach, we will illustrate the options that can be configured in the SSIS script component editor. If you open the SSIS Script component editor, you will see four tabs:

  • Script: Here, you can open the script editor where you can write your script, select the script programming language (C# or VB.NET) and select the SSIS variables you need within the script (for read or write purposes)
  • Input Columns: Here, you can select the input columns for use within the script
  • Inputs and Outputs: Here, you can configure the outputs generated by the script (number of outputs, type of each output, output columns, etc.)
  • Connection Managers: Here, you can select the connection managers for use within the script

this image shows the script tab page in the ssis script component editor

Figure 3 – SSIS Script component editor

Note, that when using the SSIS script component as a source, there are no input columns to select. In addition, when using this component as a destination, no output configuration is needed.

Additional information can be found in the following documentation: Configuring the Script Component in the Script Component Editor.

SSIS Script component as source

The first way to use a script component is as a source. This approach is useful when reading from a data source that doesn’t have its own source component (Flat File, Excel, OLE DB, etc.,) such as when reading JSON data from a Web API or reading an unstructured Flat File that can’t be handled using a Flat File Connection Manager.

Refer to the following links for more information and some examples:

SSIS Script component as destination

The second way to use an SSIS Script component is as a destination. This approach is useful when exporting the data into a file that is not supported by other destination components, or for applying some processing without the need for a destination component.

Refer to the following links for additional information:

SSIS Script component transformation

The third and most popular way to use a script component is for transformations. In this approach, the script component reads input values from the data flow and generates outputs. There are two types of outputs that can be generated:

  • Synchronous output
  • Asynchronous output

In this section, we will briefly describe these two output types, and you can refer to the following link for more information: Understanding Synchronous and Asynchronous Transformations.

Synchronous output

With this output type, there is an output row for each input row. All output columns defined in the output buffer are added to the input row. As shown in the image below, in the Inputs and Outputs tab page, you must select the synchronous input property for the output buffer created:

this image shows how to add a synchronous output in the ssis script component transformation

Figure 4 – Define synchronous Input for the output buffer

When performing a synchronous transformation, all input and output columns are accessed using the Row class. As an example:

Row.outColumn = Row.InColumn + “_1”;

For more information, refer to the following official documentation:

Asynchronous transformation

With this transformation type, each input row may have 0 or multiple output buffers. You can set the output type as asynchronous by setting the SynchronousInput property to None:

this image shows how to add a asynchronous output in the ssis script component transformation

Figure 5 – Set the output buffer as asynchronous

You must use the output buffer wrapper class to add an output row and to assign values to columns. As an example, if the output buffer name is “Output1,” then you must use the following code:

Output1Buffer.AddRow();
Output1Buffer.outColumn = “abc”;

For more information, refer to the following official documentation:

Single/multiple outputs

From the Inputs and Outputs tab, you can add multiple output buffers, and you can configure each one as synchronous or asynchronous based on the logic you want to implement.

Derived Column vs. Script Component

One of the most common questions asked is when to use a derived column transformation or a script component. A derived column is used to apply simple transformations using SSIS expressions and doesn’t require programming language knowledge. An SSIS Script component is more advanced and is used to implement more complex logic than the derived column.

Also, the script component can use .NET Framework libraries and functions, as well as third-party assemblies, which is not possible in derived columns.

More details about the differences between the SSIS script component and derived columns are mentioned in the following article:

If you are new to SSIS, refer to the following link to learn more about SSIS Derived column transformation:

Exception has been thrown by the target of an invocation

One of the most common errors related to writing scripts in SSIS is that the Script component or Script Task has encountered an error at runtime but the exception message does not provide further information: “Exception has been thrown by the target of an invocation”.

This is a generic error message that is shown when a script fails. To retrieve the original error message, you have to implement a try/catch logic and use ComponentMetadata class to fire the real error message:

Additional information can be found on: SSIS – Script Task error: Exception has been thrown by the target of an invocation. (just note that in a script task, DTS namespace is used instead of ComponentMetadata).

Conclusion

In this article, we have provided an overview of the SSIS script component, and showed how it can be used within the data flow task. In addition, we briefly described the differences between Script and derived columns. Finally, we showed how to read the error messages thrown in runtime.

Hadi Fadlallah

Hadi Fadlallah

Hadi is a Lebanese Researcher, Data Engineer and Business Intelligence Developer.

He has been working with SQL Server for more than 10 years. Also, he's one of the top ETL and SQL Server Integration Services contributors at Stackoverflow.com

Hadi really enjoys learning new things everyday and sharing his knowledge.

View all posts by Hadi Fadlallah
Hadi Fadlallah
117 Views