SSIS Date Conversion Problem

Question

SSIS Date Conversion Problem

Mike Sylvester

Old Hand

Points: 331
More actions
April 5, 2006 at 12:57 pm

#112472

I am having trouble converting a string 2006-03-13-10.2 to a valid date format. I am using SSIS and then using a derived column
REPLACE(SUBSTRING([Data Conversion 1].START_OF_EXECUTION,1,10) + " " + RIGHT([Data Conversion 1].START_OF_EXECUTION,8),".",":")
This works to convert the string into a valid date. But when I try to add the data conversion step, changing the field to Date it fails when it runs
The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data.".
Please help

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Mike Sylvester Old Hand Points: 331 More actions · Answer 1

If I do not try to convert the field type but leave it as char it works. But is it safe to leave a date field as char? I have done some testing and it seems OK other than the MIN function returns NULLS when it should not. MAX works as it should and searches on dates works as well.

Anyone??

Steve-3_5_7_9 SSChampion Points: 10832 More actions · Answer 2

Have you converted the string to a date in your script before putting it in the database field?

1)Input data as a string

2)Create new output column "LastUpdateDateOut" as an output datatype = DT_DBTIMESTAMP

3)Convert the date string in your script.

Row.LastUpdateDateOut =

CDate(CStr(CType(yourdatestring, Date)))

4) This output "row.LastUpdateDateOut" becomes the input to the database.

Mike Sylvester Old Hand Points: 331 More actions · Answer 3

Row.LastUpdateDateOut = CDate(CStr(CType(yourdatestring, Date)))???

I do not understand where to put this? What step in the package does this belong.

Thanks

Steve-3_5_7_9 SSChampion Points: 10832 More actions · Answer 4

Add a "script component", after the source and before the destination. You'll use this script to transform the columns that require conversions.

The output of the script will be used for INPUT to the destination.

Mike Sylvester Old Hand Points: 331 More actions · Answer 5

Okay I put the scrip component after the derived column and before the destination. I go into the script component and select the column then go to design script and type in the following.

Public

Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

'

Row.DataConversion1STARTOFEXECUTION =

CDate(CStr(CType(Row.DataConversion1STARTOFEXECUTION, Date)))

'

End Sub

It takes everything but what is in red?? What do I put here, this was where you had yourdatestring.

Thanks for the help, and sorry for my inexperience..

Steve-3_5_7_9 SSChampion Points: 10832 More actions · Answer 6

'Here's my code segment. I'm converting to columns here, a name and a 'date.

Dim LastUpdate As String

Dim ReportDateIn As String

Dim mm As String

Dim dd As String

Dim yyyy As String

Dim ShortName As String

ReportDateIn = Row.LastUpdateDate

yyyy = Left(ReportDateIn, 4)

mm = Mid(ReportDateIn, 5, 2)

dd = Mid(ReportDateIn, 8, 2)

ShortName = Left(Row.CSOWNERTABLES, 7)

LastUpdate = mm &

"/" & dd & "/" & yyyy

Row.LastUpdateDateOut =

CDate(CStr(CType(LastUpdate, Date)))

Row.SHORTNAME = ShortName

''' LastUpdateDateOut and ShortName become INPUTS to my destination

Mike Sylvester Old Hand Points: 331 More actions · Answer 7

That does make sense. Since I am a VB guy. Below is what I have done. It runs but complains about converting string do date.

Dim LastUpdate As String

Dim ReportDateIn As String

Dim mm As String

Dim dd As String

Dim yyyy As String

Dim ShortName As String

ReportDateIn = Row.DataConversion1STARTOFEXECUTION

yyyy = Left(ReportDateIn, 4)

mm = Mid(ReportDateIn, 6, 2)

dd = Mid(ReportDateIn, 9, 2)

LastUpdate = mm &

"/" & dd & "/" & yyyy

Row.DataConversion1STARTOFEXECUTION =

CStr(CDate(CStr(CType(LastUpdate, Date))))

This is part of the error the script produces.

Conversion from string "//" to type 'Date' is not valid.

Steve-3_5_7_9 SSChampion Points: 10832 More actions · Answer 8

Check out the value your passing in for:

DataConversion1STARTOFEXECUTION

My dateIn is in the format yyyymmdd. I believe you had dashes? You'll have to modify your code for your input.

Mike Sylvester Old Hand Points: 331 More actions · Answer 9

Bingo!!! There were some blank fields so it was trying to set // to a date. I placed an If YYYY<>"" then .... and it worked great.

Thanks so much for the help!!!!

Steve-3_5_7_9 SSChampion Points: 10832 More actions · Answer 10

Steve-3_5_7_9

SSChampion

Points: 10832

April 6, 2006 at 12:03 pm

#631318

you're welcome

naveen.mse Newbie Points: 5 More actions · Answer 11

Steve-3_5_7_9 (4/6/2006)
you're welcome

hi i am facing problem in data conversion,

i am trying to insert values from oledb source to oledb destination,

in oledb source i have a column which contains date datas with nvarchar datatype,

i need to insert those datas into oledb destination. this column not only contains date values. some times it may contain values like na, null or some strings,

kindly help me out in this issue.