Hadi Fadlallah
Script editor

SSIS Script task vs. Script Component

November 19, 2021 by

This article explains the SSIS Script task and Script component, the scripting tools available in the SQL Server Integration Services (SSIS).

This article is the seventeenth of the SSIS features face-to-face series that aims to compare similar SSIS components to remove any confusion for the data integration developers that use this technology.

Introduction

SQL Server Integration Services is a set of tools for data integration operations. It is used to facilitate the data warehousing process and to solve complex business problems. Integration Services contains features (tasks and components) that allow developers to extract and transform data from various sources such as XML data files, flat files, and relational data sources and then load the data into one or more destinations. The SSIS tasks and components almost support most of the well-known operations needed in the data integration operations, such as:

  1. Reading from popular data sources: text files, XML, CSV, relational databases, Excel sheets…
  2. Export data to different data formats
  3. Insert data into different data repositories
  4. Operating system tasks
  5. Performing maintenance tasks (backup, restore, cleanup)
  6. Data Warehousing operations (Data Quality tasks, Analytical tasks)
  7. A wide variety of data transformation operations

Besides all these features, one of the main strengths of Integration Services is that it allows the .NET developers to implement any complex logic and utilize libraries from the robust .NET framework or third parties. This capability is available using two main objects: the SSIS Script task and the SSIS Script Component.

In the following sections, we will briefly explain both objects and illustrate some of the use cases for each one.

  • Side Note: In SSIS, tasks are executable at the package control flow level, while components are executable within data flows

SSIS Script Task

Based on the official SSIS documentation, SSIS tasks are categorized as the following: Data Flow Task, data preparation tasks, workflow tasks, SQL Server tasks, Analysis Services tasks, maintenance tasks, custom tasks, and scripting tasks.

Scripting Tasks (Mainly the SSIS Script Task) extends the package functionality by using scripts. It performs functions that are not provided by the standard Integration services tasks.

SSIS Script task description in the SSIS toolbox

Figure 1 – Script task description in the SSIS toolbox

SSIS Script Task is an effortless task where not much configuration is needed. The initialize a Script task, the user must specify the programming language to be used, the read-only and read-write variables.

SSIS Script task editor

Figure 2 – Script task editor

Two programming languages are supported in the SSIS Script task: Visual Basic .NET and C#.

Choosing the scripting programming language

Figure 3 – Choosing the scripting programming language

As mentioned in figure 2, we should click on the “Edit Script” button to open the script editor. The Script task uses Microsoft Visual Studio Tools for Applications (VSTA) as the development environment for the script itself.

    Side Note: Microsoft Visual Studio Tools for Applications lets you add and run VSTA customizations in applications that are integrated with VSTA

Script editor

Figure 4 – Script editor

As shown in the image above, there is one class called ScriptMain.cs within the VSTA project. This is the class where we will implement our script. Within the ScriptMain class, we should write our code within the Main function.

Reading and writing to variables

To use a variable within a script, we should ensure that this variable is selected in one of the ReadOnlyVariables and ReadWriteVariables properties, depending on if our code needs to write to the variable.

To read an integer value stored within an SSIS variable we should use the following code:

Besides, to write an integer value within an SSIS variable we should use the following code:

As shown in the examples above, variables are accessed from the “Dts” namespace, which allows the script to interact with the external package objects.

Raising events

An SSIS Script task can raise three types of events for logging purposes: Errors, warnings, and information.

To raise an error, we should use the FireError() method:

To raise a warning, we should use the FireWarning() method:

To raise information, we should use the FireInformation() method:

Using connection managers

Some types of connection managers can be used within the SSIS Script Task in order to read some of their configuration. As an example, to read a file path from a File Connection Manager, we can use the following code:

SSIS Script Task use cases

There are several use cases for the use of script tasks, such as listing files, manipulating Excel files, checking for empty files. For more information, Microsoft provides a set of walkthroughs for several examples of using Script Task.

SSIS Script Component

As we mentioned in the introduction, tasks are not intended to run within data flow tasks. They are only used at the control flow level. To extend the data flow tasks functionalities, we should use the SSIS Script Component.

SSIS Script Component configuration is more complex than the SSIS Script task. As a part of the data flow task pipeline, input and output columns metadata must be configured for components.

SSIS Script Component columns configuration

Figure 5 – Script Component columns configuration

SSIS Script component has three supported functionalities:

  • Script as a source: We can use a script component to generate data rows from sources that are not supported in SSIS or if we need to implement a complex logic while loading the data. If used as a source, the Script component supports multiple outputs
  • Script as a transformation: We can use a script component to perform complex data transformation. If used as a transformation, the Script component supports one input and multiple outputs
  • Script as a destination: We can use a script component as a destination in case we need to insert data into an unsupported destination type in SSIS. If used as a destination, the Script component supports one input

This article will talk briefly about the SSIS Script Component since we already published a detailed article on the SQL Shack website where the component usage and configuration were illustrated.

Reading and writing to variables

Unlike the Script Task, working with variables within a Script Component does not require the “Dts” namespace. The variables will be available as strongly-typed properties of the Variables object:

string filePath = Variables.FilePath;

Besides writing to variables should only do within the PostExecute() method, since the SSIS variables values cannot be modified during the script execution.

Another approach to access variables within a Script Component is using the VariableDispenser property, accessed by calling Me.VariableDispenser. In this case, we are not using the typed and named accessor properties for variables but accessing the variables directly.

SSIS Script Component use cases

In the Microsoft official documentation, they mentioned four purposes to use an SSIS Script Component:

  1. Apply multiple transformations to data instead of using multiple transformation components in the data flow
  2. Access business rules in an existing .NET assembly
  3. Use custom formulas and functions
  4. Validate column data and skip records that contain invalid data

Besides, to learn more about using an SSIS Script Component as a source, you can refer to the following SQL Shack article: Using the SSIS Script Component as a Data Source.

Summary

Having published a detailed article about the SSIS Script component, this article focused on the SSIS Script task and its use within our Integration Services packages. Besides, we briefly mentioned the Script Component. Besides, we illustrated some of the use cases for both scripting features.

We should keep in mind that the main difference between Script tasks and Script components is that each one is executable at different levels in the SSIS package and each one of them has its use cases even if their names look similar.

Table of contents

SSIS OLE DB Source: SQL Command vs Table or View
SSIS Expression Tasks vs Evaluating variables as expressions
SSIS OLE DB Destination vs SQL Server Destination
Execute SQL Task in SSIS: SqlStatementSource Expressions vs Variable Source Types
Execute SQL Task in SSIS: Output Parameters vs Result Sets
SSIS Derived Columns with Multiple Expressions vs Multiple Transformations
SSIS Data types: Change from the Advanced Editor vs Data Conversion Transformations
SSIS Connection Managers: OLE DB vs ODBC vs ADO.NET
SSIS Flat Files vs Raw Files
SSIS Foreach Loop vs For Loop Container
SSIS: Execute T-SQL Statement Task vs Execute SQL Task
SSIS Lookup transformation vs. Fuzzy Lookup transformation
SSIS Pivot transformation vs. Unpivot transformation
SSIS Merge Join vs. Merge Transformation
Data Access Modes in SSIS OLE DB Destination: SQL Command vs. Table or View
SSIS XML Source vs XML task
SSIS Script task vs. Script Component
SSIS term extraction vs. term lookup

Hadi Fadlallah
Latest posts by Hadi Fadlallah (see all)
ETL, Integration Services (SSIS)

About Hadi Fadlallah

Hadi is an SQL Server professional with more than 10 years of experience. His main expertise is in data integration. He's one of the top ETL and SQL Server Integration Services contributors at Stackoverflow.com . Also, he published several article series about Biml, SSIS features, Search engines, Hadoop, and many other technologies. Besides working with SQL Server, he worked with different data technologies such as NoSQL databases, Hadoop, Apache Spark. He is a 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

245 Views