CODEWITHSUNDEEP
sql-serverssisetl
Raj Jayaswal
Raj Jayaswal

Reputation: 478

Data Conversion In ETL SSIS

I'm working in ETL process where I have a column in a table varchar with data as 28JUN1952. This will always be in this format i.e. 2 digits for date, 3 letters for months and 4 numbers for years.

I was able to convert this into a date column by I need this to come in yyyyMMdd format i.e. 28JUN1952 become 19520628. I was able split the years months and date, but unable to add a padding in the month if it's a 1 digit month for e.g. Jan or Feb.

Is there any other way to convert the data format or any other alternative to add a padding?

I need the conversion in SSIS only.

Upvotes: 1

Views: 1066

Answers (2)

Joe G Joseph
Joe G Joseph

Reputation: 24046

Try this:

declare @d varchar(20)='28JAN1952';

SELECT replace(CONVERT(date,RIGHT(@d,4)+SUBSTRING(@d,3,3)+LEFT(@d,2)),'-','')

Upvotes: 0

billinkc
billinkc

Reputation: 61211

The easiest route will be to add a Script Transformation.

  1. On the Input and Output tab, expand the Output 0, select Output Columns, click Add Column. 2. Rename Column to something like ccyymmdd and change the data type. I'm not sure what your target type is (int32, string?) but I'll assume string as I'm lazy
  2. On the Input Columns tab, select the string date source column (assuming it's called srcDate)
  3. Inside the script task, assuming C#, use code like this

Inside script task

public override void Input0_ProcessInputRow(Input0Buffer Row)
{
    DateTime x = DateTime.MinDate;
    if (DateTime.TryParse(Row.srcDate, out x))
    {
        Row.ccyymmdd = x.ToString("yyyyMMdd");
    }
    else
    {
        // Handle NULL values however you wish
        // Assign "known bad value"
        // Row.ccyymmdd = "99991231";
        // Or set the null property to true (preferred)
        Row.ccyymmdd_IsNull = true;
    }

}

Upvotes: 2

Related Questions