SSIS datetime string transformation

Hello David,

To transform string field into corresponding format you can use Script
Component included in Data Flow Component of SSIS.
In the script you generate new column containing date in appropriate
format.
Here is an example of this function:

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As
Input0Buffer)

Dim StrDate as StringStrDate = Mid(Row.Date1, 5, 2) &
"/"StrDate = StrDate & Mid(Row.Date1, 3, 2) & "/"

StrDate = StrDate & Mid(Row.Date1, 1, 2)

If IsDate(StrDate)Then
Row.Date2 = CDate(StrDate)
End If
End Sub

I hope it will be helpful.

Best regards,
Radoslaw Lebkowski


dtaylo04@gmail.com wrote:
> I'm new to SSIS and I'm trying to rebuild a sql 2000 dts package that
> imports a text file. This file has a field that is a string that is a
> date timestamp. The field looks like this: 20050622180517 in this
> format yyyyMMddHHmmss. In my sql 2000 dts, i use a date time string
> transformation to put it in this format: MM/dd/yyyy HH:mm:ss. I do
> that so that it can be loaded as a datetime into my sql table. I'm at
> a loss on how to do this in SSIS, any help would be greatly
> appreciated.
>
> Thanks in advance,
> David

I have done this kind of thing using the Derived Column transformation,
followed by the Data Conversion transformation. The Derived Column
transformation creates a new string column that puts the slash character /
separator. The Data Conversion component then converts the column into a new
data type. Here is an example of the expression you would use in the Derived
Column transform:

SUBSTRING([HireDate], 1, 4) + "/" + SUBSTRING([HireDate], 5, 2) + "/" +
SUBSTRING([HireDate], 7, 2)

This method avoids writing script and is quite quick.

Charles Kangai, MCT, MCDBA




"dtaylo04@gmail.com" wrote:

> I'm new to SSIS and I'm trying to rebuild a sql 2000 dts package that
> imports a text file. This file has a field that is a string that is a
> date timestamp. The field looks like this: 20050622180517 in this
> format yyyyMMddHHmmss. In my sql 2000 dts, i use a date time string
> transformation to put it in this format: MM/dd/yyyy HH:mm:ss. I do
> that so that it can be loaded as a datetime into my sql table. I'm at
> a loss on how to do this in SSIS, any help would be greatly
> appreciated.
>
> Thanks in advance,
> David
>
>

Charles -

Yes, this does help. I've create the transformation like this:
SUBSTRING([Column 11],5,2) + "/" + SUBSTRING([Column 11],7,2) + "/" +
SUBSTRING([Column 11],1,4) + " " + SUBSTRING([Column 11],9,2) + ":" +
SUBSTRING([Column 11],11,2) + ":" + SUBSTRING([Column 11],13,2)

So that I can get my output like 12/27/2005 17:12:35

I then realized that I had to change my flat file connection to handle
the larger string value, but all is working now.

Thanks for the help.
David