Thought I'd add a note as I was getting confused with the built-in function "FormatDateTime()". The example is shown as:

  1.  =FormatDateTime(Fields!BirthDate.Value, DateFormat.ShortDate) 

The other formats are:
  1.  =FormatDateTime(Fields!BirthDate.Value, DateFormat.GeneralDate) 
  2.  =FormatDateTime(Fields!BirthDate.Value, DateFormat.LongDate) 
  3.  =FormatDateTime(Fields!BirthDate.Value, DateFormat.ShortDate) 
  4.  =FormatDateTime(Fields!BirthDate.Value, DateFormat.LongTime) 
  5.  =FormatDateTime(Fields!BirthDate.Value, DateFormat.ShortTime) 

Unfortunately if you are using US dates and want the report to use a specific European date format and you spend as long as I did searching the web for a solution, then ignore all the above.

Another built-in function is the text-formatter

Please note: the following article is not a solution but a page of various methods and date conversions which I try and use depending on the situation.

This is a quick article on how I got time conversion working in an SSIS package which read from a text file. Note that the example below converts a string in European Date Format (ie. "ddmmyyyy" to "dd/mm/yyyy"). Also, my data flow imports two dates one which was imported as a string (DT_WSTR) and another as a integer (DT_R8).

It took me a long time to figure this and it was only by trawling through columns that someone mentioned that maybe all the data in that column is not consistent? This made sense as I need to parse the string to extrapolate the date, month and year.


A quick article on how to skip rows or even specify a range to extract from the Excel file when using as the DataSource.

If you are simply using a text file as your data source, then the options in the connection manager will let you skip rows and specify column datatypes. You don't get that with Excel but you can still control the data range.

My example is that I have an excel sheet where the first 10 rows are descriptions to the column, and then I have 38 columns ("A" to "AL"):

This is a quick article to remind me on how to skip blank rows when using a Flat file as a data source. I would receive another Microsoft error as clear as mud:

  1.  Error: The conditional operation failed. 
  3.  Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR.  The "component  
  4.  "MyDerivedColumns" (4228)" failed because error code 0xC0049063 occurred, and the error 
  5.  row disposition on "output column "DC_MyDate" (7349)" specifies failure on error. An  
  6.  error occurred on the specified object of the specified component.  There may be error  
  7.  messages posted before this with more information about the failure. 

So the solution must be obvious to you by now. At least that's what Microsoft people think to themselves every time they see the error they programmed in.

Apparently this problem also happens when you have a Data File of varying column numbers. My solution below will also fix this.