RFE: SSIS should recognise YYYYMMDD HH:MI:SS & YYYY-MM-DDTHH:MI:SS

Hi,

I've found a bit of an inconsistency between the way SQL Server treats dates and the way that SSIS treats them.

As stated here: http://blogs.conchango.com/jamiethomson/archive/2005/04/26/1337.aspx the only totally unambiguous datettime string formats that SQL Server accepts are:

yyyymmdd hh:mi:ss

yyyy-mm-ddThh:mi:ss

Given that then, its a bit annoying that, as stated here: http://blogs.conchango.com/jamiethomson/archive/2006/06/26/4141.aspx those two formats are not accepted as valid datetime string formats in the SSIS pipeline (check the bottom data viewer in the screenshot at the above link).

I know that there is no strict requirement for SQL Server and SSIS to do the same thing as each other in situations like these but let's be honest - we all expect it to.

Any comments from MSFT?

Is it worth me raising this at Microsoft Connect?

-Jamie

Jamie,

For those counter examples in your second link, have you tried using a data conversion transform rather than derived column, and setting the FastParse property on the new columns to True? Those formats look like ISO standards, which should be correctly converted by the FastParse routines.

Mark

|||

Mark,

I haven't tried that, no. But in suggesting it you've kinda made my point for me.

i.e. If they are ISO standards, why doesn't SSIS expression language like them?

-Jamie

P.S. FastParse isn't relevant here cos this isn't data from a flat file source.

|||

Anyone?

cheers

-Jamie

|||

I wrote a really nice answer to this and lost my connection just as I was posting. :-( So here's a shorter version.

We use the oleaut*.dll libraries for standard conversions to be compatible with as wide a range of technologies as possible. It is important to remember that we talk to SQL through clients - so compatibility with the client interfaces is the critical factor. Some clients such as the OLEDB and ADO.Net Providers can do limited conversions themselves, or pass, say, string data to the engine for conversion on loading. This latter technique is useful if you want to use the engine's own conversions - although they are limited, and you don't find errors until trying to load them.

SQL Server has their own routines, but also includes sps for conversion to ole formats. See http://msdn2.microsoft.com/en-us/library/ms191530.aspx

We do not extend the oleaut* conversion routines in our standard conversions and parsing - we do so by implementing additional functionlity in FastParse.

Fast Parse is not just for text files - you can find it on the advanced tab of the Data Conversion component too.

Great points Jamie - thanks for this thread.

Donald

|||

Donald Farmer wrote:

Fast Parse is not just for text files - you can find it on the advanced tab of the Data Conversion component too.

Doh! Well I learnt something then!!!

Thanks Donald!!

-Jamie