4

I am using a flat file data provider in SSIS to import data from an external system. I don't have any control over the file, it is pushed out on a weekly basis, and I pick it up from a common folder.

The first two columns of the CSV are dates. Part of the way through the file, the date format has changed from date to numeric as follows:

Service_Date, Event_Datetime
2018-04-30,2018-04-30 21:18
43220,43220.92412

As you can see, the format changed from date to numeric. Other date columns not shown here also have changed.

Obviously, this is breaking the data flow task.

Aside from going into Excel and saving the CSV with the proper column format, is there any way within SSIS can convert on the fly so that the job doesn't fail and require manual intervention?

Hadi
  • 36,233
  • 13
  • 65
  • 124
user8015860
  • 53
  • 5
  • Write a UDF to do it. Recognise obvious Excel dates and convert them, recognise know text formats and convert them, use conversion with TRY_PARSE to convert everything else. – Ben Feb 21 '19 at 22:39
  • Why ignoring the provided answer if it solved the issue or you agree with you have to upvote it or accept it, else you should leave a comment to elaborate more with the answerer – Yahfoufi Mar 11 '19 at 11:47

2 Answers2

3

These data values 43220,43220.92412 are called date serials, you can get the date value in many approaches:

(1) Using A derived Column

You can convert this column to float then to datetime using a derived column:

(DT_DATE)(DT_R8)[dateColumn]

References

(2) Using a script component

You can use DateTime.FromOADAte() function, as example: (code in VB.NET)

If Row.ServiceDate_IsNull = False AndAlso String.IsnullOrEmpty(Row.ServiceDate) Then 

   Dim dblTemp as Double

   If Double.TryParse(Row.ServiceDatemdblTemp) Then

        Row.OutputDate = DateTime.FromOADate(dblTemp)

   Else

       Row.OutputDate = Date.Parse(Row.ServiceDatemdblTemp)

   End


End If

Reference

Hadi
  • 36,233
  • 13
  • 65
  • 124
0

I was able to solve the problem using a variation of the derived column. This expression would catch the column obviously formatted as a date, and convert it to a date, otherwise it converts the date serial to a float first, then to a date

FINDSTRING(Date_Service,"-",1) != 0 ? (DT_DATE)Date_Service : (DT_DATE)(DT_R8)Date_Service
Hadi
  • 36,233
  • 13
  • 65
  • 124
user8015860
  • 53
  • 5