Wonder if you guys can help. I have an Excel column which stores values with a Date format+ of '20 March 2012' and I am trying to import the spreadsheet with the associated cell using an SSIS package, into a SQL Server staging table with a field that has a datetime datatype. I have tried using a Data Conversion transformation but all I get is an error and it's always along the lines of: "The Value could not be converted because of potential data loss". If I set the package to ignore errors, it just imports a 'Null' column into the SQL table. I am basically fresh out of any ideas of what to try so any help would be massively appreciated. Cheers.

Add a data conversion block between your source and destination - convert your '20 March 2012' column to DT_Date and use that column. I tried it out and it converts correctly

Hi GhostfaceKill4h, Instead, you need to use a SSIS expression. So your 20 March 2012 would become 09/20/2012 (provided it fits your locale or satisfies the database table). Use it is a derived column transform set to <replace column ...> A close if not a complete example could be this expression: SUBSTRING(RowDate,1,2) + ([month name] == "JAN") ? "01" : ([month name] == "FEB") ? "02" : ([month name] == "MAR") ? "03" : ([month name] == "APR") ? "04" : ([month name] == "MAY") ? "05" : ([month name] == "JUN") ? "06" : ([month name] == "JUL") ? "07" : ([month name] == "AUG") ? "08" : ([month name] == "SEP") ? "09" : ([month name] == "OCT") ? "10" : ([month name] == "NOV") ? "11" : ([month name] == "DEC") ? "12" : "00" + SUBSTRING(RowDate,1,2) etc, make sure it worksArthur My Blog

Thank you for the quick replies. I was auto populating the date field with today's date and wondered if this may have been causing the error to occur but even manually selecting inputting the date, it still fails with a potential data loss error when using the Data Conversion transformation. oldjeep, what does the SSIS package you are using have down for the datatype of the Excel field? Even though it is down as a Date datatype in Excel, the SSIS package still has the column down as a Unicode String type. ArthurZ I will give your example a shot now. I have tried a Derived Column Transformation as per the Andy Leonard blog but I was adding it as a new column rather than replacing. Not sure it will though as you can change the Excel Date format to be in the format of DD/MM/YYYY and that fails as well. Will let you know shortly if it works though..

My test package shows the "date" coming out of excel as a WSTR

I have enabled the data viewer to see what value it is trying to bring in and on a test row the date column is : 40988.61775 Which from 01/01/1900 using a Convert + 40988.61775 returns the value of 2012-03-22 14:49:33.597 which is close to what I want (although not sure why it's out by two days...) So now I need to find a way in SSIS to work out the value of each date cell every time it is populated and convert it to Datetime before it outputs to the SQL table...

You can probably just cut the date portion out like: SUBSTRING(DateCol, 1,10)Arthur My Blog

Have a read through this: Converting Numbers to Dates in the Derived Column Component Talk to me now on

Dates are off by two because of the difference in calendar stated in Todd McDermid's blog post, but also because the Excel calendar assigned a leap day to the year 1900. The rules of figuring out leap years are every year divisible by 4 except years divisible by 100 unless it is also divisible by 400. So, 1900 is not a leap year - but 2000 is a leap year. This must have been missed by the office team, but the SQL Server team got it right :)