How to convert NUMBER DataType to DATE DataType in ssis

0
votes

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..?

1
ssisssis-2012
Did you try what was suggested by @Justin? What is the current error that you are getting? – Abhishek
Yes i am getting THIS ERROR:- [Derived Column [36]] Error: An error occurred while attempting to perform a type cast. [Derived Column [48]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "Derived Column" failed because error code 0xC0049064 occurred, and the error row disposition on "Derived Column.Outputs[Derived Column Output].Columns[DERIVED_ADMIT_DATE]" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure. – Sanjiv
Before doing CAST in Derived Column have you converted your input Number to String using Data Conversion ? – Abhishek
@Abhishek :- Yes i have done that – Sanjiv
There must be something wrong in your implementation, you might had missed something - cause the method described works very much fine. – Abhishek

1 Answers

1
votes

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