Daniel Calbimonte
Creating a new SSIS Project

SSIS Script component vs derived column

March 1, 2019 by

Introduction

In this article, we will show how to convert dates from dd/mm/yyyy to mm/dd/yyyy using the Script component and also derived columns in SSIS. We will also explain when to use a derived column (DC) and when to use the Script Component (SC).

If you have no experience with the script component or a derived column task, this article is for you. We will do a step by step article.

We will also learn how to handle and fire errors using the script component.

Requirements

The following requirements needs to be installed.

Getting started

In this example, we will have a file named dates.txt with the following data:

id, salesdate

1, 13/07/2018

2, 23/06/2019

3, 22/09/2019

This file contains the dates in the format dd/mm/yyyy and we want to convert the date to the format mm/dd/yyyy.

Depending on the country, you will find different formats. Here you have a link as a reference related to the format and country:

Derived Column

We will first use the DC to convert data from the dd/mm/yyyy format to mm/dd/yyyy.

In SSDT, create a new SSIS Project. In the SSIS project, drag and drop the data flow:

Creating a new SSIS Project

Double click the data flow in the design pane and add a Flat file source, flat file destination and the DC task. Join the tasks with an arrow like this:

SSIS data flow diagram

Double click the Flat File Source and press the new button:

SSIS Flat File Source Editor

Specify the path where the dates.txt file is stored. This file contains the date with the dd/mm/yyyy option.

SSIS Flat File Connection Manager Editor - General

Click on Columns to detect the columns of the file:

SSIS Flat File Connection Manager Editor - Columns

Double click the DC task. The derived column will replace salesdate which is the column with the date in the dd/mm/yyyy format and we will replace with the mm/dd/yyyy format. To do it, we will use the following expression:

(DT_DATE)salesdate

The expression will convert the string into the date in the expected format:

SSIS - Derived Column Transformation Editor

Finally, we will send the converted column into a flat file destination:

In the flat file destination, press the new button:

SSIS Flat File Source Editor

Select the delimited option:

SSIS - Flat File Format

Select the name of the destination file and press OK:

SSIS - SSIS Flat File Connection Manager Editor - destination file

Go to the mappings page to map the source columns and destination columns and press OK:

SSIS - Flat File Destination Editor - Mappings

You are almost ready. Go to the control flow and execute the Data Flow (I renamed the dataflow task to Derived column):

SSIS - Control flow - execute task

If everything is OK, you will have your data in the right format:

1, 7/13/2018

2, 6/23/2019

3, 9/22/2019

Script Component or Derived Column

In some scenarios, it is not possible to solve the problem with SSIS DC and you need to use the SC. The SC is a script similar to the script task in the data flow.

If you are not familiar with the script task, you can check our article related to the script task here:

The SC is used in the Data Flow to convert and work with columns, while the script task (ST) can be used to simply write whatever code you want in C# or VB.

Both tasks support C# and VB. In this article, we will work with C#.

A common question is when to use the SC and when to use DC. In general, the SC can do any DC action. It is more powerful because it supports multiple lines of code, comments, functions, libraries, Object Oriented Programming, error handling, and all the advantages that a programming language has.

By the other hand, DC uses SSIS expressions which is simpler to use. I would recommend to use DC if the expression required is simple. If it is something more complex, SC is the solution. If SC cannot help you, then there might not be any other options.

Example with the Script Component

We are now going to show how to convert the format of the column using the SC. To do it, we will use the Flat file source, the SC Task and the Flat File destination in a new data flow like this:

SSIS - Script component file conversion

The Flat File Source requires the same configuration than in our DC example. You can copy and paste from the DC example.

Double click the Script Component. It will ask you to specify how to use the script. Choose Transformation:

SSIS - Select Script Component Type

As you can see, the SC can be used as a source, transformation or destination.

In Input Columns, select the columns that you want to include in the script task:

SSIS - Script Transformation Editor - Input Columns

In the Inputs and Outputs, expand the Output 0 and click the Output Columns. Press Add Column to add an output column:

SSIS - Script Transformation Editor - Inputs and Outputs - Add Column

We will create a new column named FixedDate and the datatype will be date. This column will contain the column in the expected format (mm/dd/yyyy):

SSIS - Script Transformation Editor - Inputs and Outputs - column properties

By default, the default language is C#, but you can change to Visual Basic if you prefer. In this example, we will use the default value. Also, you can include SSIS variables in the script. In this example, we will not use them. Press Edit script to create code.

SSIS - Script Transformation Editor - Inputs and Outputs - Script

In the Namespaces add the System.Globalization (using System.Globalization; ). This namespace is used to handle dates, currency, calendars, numbers and other information from different cultures. In this case, we will use to handle the date:

SSIS - Script Transformation Editor - Inputs and Outputs - Script source


Basically, the important part of the code is the following:

This code converts the row salesdate of the dates.txt with the format dd/MM/yyyy to a date with the expected format.

Try and catch is used to handle errors. This will help us in case that the code fails. Inside the try we run the code just explained and inside the catch, we Fire an error message. We can configure the error message that we want using the ComponentMetadata which will fire the error message.

The error handling is slightly different from the Script task.

Finally, in the Flat destination, press the new button:

SSIS - Fat File Destination Editor - new connection

Select delimited in the Flat File Format selection:

SSIS - Flat file format - Delimited

In the file name, write the path:

SSIS - Flat File Connection Manager Editor - File name

In the Advanced page, select the date column and press Delete. This action will remove the date with the old format

SSIS - Flat File Connection Manager Editor - Delete column

Go to mappings to create the mappings between source and columns:

SSIS - Flat File Connection Manager Editor - Mappings

If you run the package, you will have the new file with the expected format.

Conclusions

In this article, we learned 2 ways to convert columns. The Derived Column (DC) and the Script Component (SC).

  • If the transformation is simple (like the example in this article), the derived column is a good option. However, if the expression is too complex or hard to understand, the SC can be the best choice
  • SC accept comments and accept functions. It is easier to reuse the code for different situations and there is more documentation on the web because it is code in C# or VB
  • SC uses C# or VB. It is a programming language, then it is easier to understand, maintain and reuse the code (if it is good programmer and follows the best practices)
  • You can use third party libraries, existing libraries to solve very complex problems that cannot be solved in any way using SSIS expression in derived columns
  • In other words, DC is used in simpler situations, but SC can be used in any situation and is recommended when it is not possible with DC or the solution in DC is complex
  • Finally, if you use SC, we recommend to use a try and a catch to handle errors if the code fails for some reason. It increases a little bit the lines of code, but in the long run it will save time if some error messages appear

Daniel Calbimonte

Daniel Calbimonte

Daniel Calbimonte is a Microsoft Most Valuable Professional, Microsoft Certified Trainer and Microsoft Certified IT Professional for SQL Server. He is an accomplished SSIS author, teacher at IT Academies and has over 13 years of experience working with different databases.

He has worked for the government, oil companies, web sites, magazines and universities around the world. Daniel also regularly speaks at SQL Servers conferences and blogs. He is also a writer for SQL Server training material for certification exams.

View all posts by Daniel Calbimonte
Daniel Calbimonte
Importing, exporting, Integration Services (SSIS)

About Daniel Calbimonte

Daniel Calbimonte is a Microsoft Most Valuable Professional, Microsoft Certified Trainer and Microsoft Certified IT Professional for SQL Server. He is an accomplished SSIS author, teacher at IT Academies and has over 13 years of experience working with different databases. He has worked for the government, oil companies, web sites, magazines and universities around the world. Daniel also regularly speaks at SQL Servers conferences and blogs. He is also a writer for SQL Server training material for certification exams. View all posts by Daniel Calbimonte

121 Views