I have a problem.
I'm trying to insert a date value (which is a sting) into a date column (which is a date data type). The date is read from the file creation property of a file using the script task in C# as follows:
strFileDate = (FileObject.CreationTime.ToShortDateString)
strFileTime = (FileObject.CreationTime.ToShortTimeString)
I pass the date and time to variables in SSIS and then from there I use the SQL task to try to insert the date and time variables into the SQL server table as follows:
INSERT INTO ag_opendatetime (OpenDate, OpenTime) SELECT '', ''
In the SQL task in Expressions I have set the following to read the values of the variables as follows:
"INSERT INTO ag_opendatetime (OpenDate, OpenTime) SELECT '"+ @[User::FileDate]+"', '"+@[User::FileTime] +"'"
Eventually I get the error
SSIS: Conversion failed when converting date and/or time from character string
I've tried every convert(date, cast( etc and it doesn't work. How can I make SQL server accept the string date and or time values?