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.
- SSDT for Business Intelligence (with SSIS 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:
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:
Double click the Flat File Source and press the new button:
Specify the path where the dates.txt file is stored. This file contains the date with the dd/mm/yyyy option.
Click on Columns to detect the columns of the file:
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:
Finally, we will send the converted column into a flat file destination:
In the flat file destination, press the new button:
Select the delimited option:
Select the name of the destination file and press OK:
Go to the mappings page to map the source columns and destination columns and press OK:
You are almost ready. Go to the control flow and execute the Data Flow (I renamed the dataflow task to Derived column):
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:
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:
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:
In the Inputs and Outputs, expand the Output 0 and click the Output Columns. Press Add Column to add an output 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):
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.
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
public override void Input0_ProcessInputRow(Input0Buffer Row) { /* * Add your code here */ try { Row.fixedDate = DateTime.ParseExact(Row.salesdate, "dd/MM/yyyy", CultureInfo.InvariantCulture); } catch (Exception ex) { bool pbCancel = false; this.ComponentMetaData.FireError(5,"The component failed to convert the date","Component error",string.Empty,5,out pbCancel); } } |
Basically, the important part of the code is the following:
|
1 2 |
Row.fixedDate = DateTime.ParseExact(Row.salesdate, "dd/MM/yyyy", CultureInfo.InvariantCulture); |
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:
Select delimited in the Flat File Format selection:
In the file name, write the path:
In the Advanced page, select the date column and press Delete. This action will remove the date with the old format
Go to mappings to create the mappings between source and columns:
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
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 writes SQL Server training materials for certification exams.
He also helps with translating SQLShack articles to Spanish
View all posts by Daniel Calbimonte
- PostgreSQL tutorial to create a user - November 12, 2023
- PostgreSQL Tutorial for beginners - April 6, 2023
- PSQL stored procedures overview and examples - February 14, 2023