CODEWITHSUNDEEP
ssisssis-2012
Sanjiv
Sanjiv

Reputation: 1298

How to convert NUMBER DataType to DATE DataType in ssis

Hi i have csv file data e.g:- (ActiveDate= 20180105) so I want to save this column to my database. In my database column datatype is datetime .

And while using data conversion as DT_Date i am getting this error:-

The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data.

So how can i import this ActiveDate data from my excel to my database as datetime ?

Can you help me out..?

Upvotes: 0

Views: 679

Answers (1)

Justin
Justin

Reputation: 9724

You must first convert Your column YYYYMMDD To String in Data Conversion element like this:

  Input Column   Output Column   Data Type                      Length 
  IntDate       StringDate      Unicode string [DT_WSTR]        50

and then you can use this formula in Derived Column element to convert new string column to real date:

(DT_DATE)(SUBSTRING(StringDate,1,4) + "-" + SUBSTRING(StringDate,5,2) + "-" + SUBSTRING(StringDate,7,2))

For data with null in date column use this formula, this formula gonna change null in current date:

isnull(StringDate)? GETDATE() : (DT_DATE)(SUBSTRING(StringDate,1,4) + "-" + SUBSTRING(StringDate,5,2) + "-" + SUBSTRING(StringDate,7,2))

Results:

IntDate     StringDate  DateDate
20170809    20170809    2017-08-09 00:00:00.0000000

Upvotes: 1

Related Questions