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.
Figure 1 – SSIS Script Component description from toolbox
SSIS Script component can be used in three ways:
- As a source component
- As a destination component
- 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:
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
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:
- Creating a Source with the Script Component
- How to create a SSIS package to ETL JSON from Python REST API request into MSSQL server?
- How to connect TIBO EMS using SSIS
- SSIS – USING A SCRIPT COMPONENT AS A SOURCE
- Using REST API in SSIS to Extract Top 100 User Tweets
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:
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:
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:
|
1 2 3 4 5 6 7 8 9 10 11 |
try{ //...write your code here Dts.TaskResult = (int)ScriptResult.Success; }catch(Exception ex){ ComponentMetaData.FireError(0,"An error occured", ex.Message,String.Empty, 0); } |
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.
Besides working with SQL Server, he worked with different data technologies such as NoSQL databases, Hadoop, Apache Spark. He is a MongoDB, Neo4j, and ArangoDB certified professional.
On the academic level, Hadi holds two master's degrees in computer science and business computing. Currently, he is a Ph.D. candidate in data science focusing on Big Data quality assessment techniques.
Hadi really enjoys learning new things everyday and sharing his knowledge. You can reach him on his personal website.
View all posts by Hadi Fadlallah
- An overview of SQL Server monitoring tools - December 12, 2023
- Different methods for monitoring MongoDB databases - June 14, 2023
- Learn SQL: Insert multiple rows commands - March 6, 2023