1

I am reading data from a flat file and storing them in a OLE DB destination. In the flat file, dates and in various formats
17/02/2014,
28-Apr-14,
30.06.14

I have used a Derived column transformation to check for empty columns and replace it with null. As far as I have seen SSIS and data base accepts 17/02/2014 format and 28-Apr-14, 30.06.14 are rejected.

I want to convert 28-Apr-14 and 30.06.14 to a valid format which DB accepts.

I have researched a bit and read that Script task can do it but I am not sure of the code how to check this.

Could you please guide which is the best way to do this.

Any suggestions/help is much appreciated.

Thanks Rao

BRDroid
  • 3,920
  • 8
  • 65
  • 143
  • your best bet would be to store the same way as they are coming or sticking to same format for all the dates in flat file – TheGameiswar Jul 15 '16 at 11:50
  • how can I insert different formats in one column – BRDroid Jul 15 '16 at 12:24
  • You can do it in a derived column, a script transformation or a T-SQL query. Personally I prefer T-SQL. What do you prefer? Also are you absolutely certain that the dd.mm.yy will never be in the form mm.dd.yy? (those crazy americans) – Nick.Mc Jul 15 '16 at 13:15
  • I can not implement all the possibilities but I will only to the formats mentioned above. I prefer derived column or script transformation as i am bit familiar with them. In Derived column how do I do it though? – BRDroid Jul 15 '16 at 13:21
  • @JoeC 's link shows you how to do it in T-SQL as his answer suggests and My answer on the same question shows you a SSIS Script Component method. I personally find the script much simpler because there is less string manipulation and it doesn't require a staging table if you first need to load the data from another source into SQL. – Matt Jul 18 '16 at 16:22
  • Possible duplicate of [SSIS clean up date from csv file](http://stackoverflow.com/questions/38225475/ssis-clean-up-date-from-csv-file) – Matt Jul 18 '16 at 16:23

1 Answers1

1

Check out my answer on the following question. This should work for you as well.

SSIS clean up date from csv file

Community
  • 1
  • 1
Joe C
  • 3,925
  • 2
  • 11
  • 31