So I finally got there with some pain around data typing. I know my solution is not the best but it is working for me and I can acheive the business results using this mehtod

I now have my SSIS package FileDate variable with a string data type. I declared a variable within the VB code of type date, set this variable to the GetCreationTime results, then place this variable into the DTS package FileDate variable.

Here is the updated piece of VB

Public Sub Main()

'

Dim filepath As String

Dim filedatedatetime As Date

filepath = Dts.Variables("FilePAth").Value.ToString

Debug.Print(filepath)

filedatedatetime = File.GetCreationTime(filepath)

Dts.Variables("FileDate").Value = filedatedatetime.ToString

Debug.Print(Dts.Variables("FileDate").Value)

Dts.TaskResult = ScriptResults.Success

I can now get a value written to the SQL table, whereas without this method the DTS Package FileDate variable was being set to the enpty string and populating the DB with the default date.

Luckily I am not considered an SSIS expert but now I can get the data into the DB correctly I am back into my comfort zone.