0

I want to import a huge csv-file with datetime-column in format YYYY.MM.DD hh:mm:ss.nnn (2015.09.28 00:00:02.721). It is the supported string literal format for datetime: datetime (Transact-SQL). I'm using DT_DBTIMESTAMP as the Integration Services data type: Integration Services Data Types. But the import does not work due to convertion error. I can only import in format YYYY-MM-DD hh:mm:ss.nnn. My OS, SQL 2014 and DB are all in german. How can I execute this task without search and replace in csv with regular expression?

Bern Liefer
  • 1
  • 1

3 Answers3

0

I'm not sure I understand what you mean about Regular Expressions - but here is a solution for you:

You can import the file into a temporary holding table and then convert it to a datetime using the DataConversion task.

You can also apply the following SQL to your table - which is probably going to be quicker:

UPDATE tempTable SET NewDateCol = CAST(OldDateCol AS DATETIME)

where OldDateCol is the imported text field containing the "invalid" date format.

Because we know that this will work:

SELECT CAST('2012.05.06 11:25:33.123' AS DATETIME)
BIDeveloper
  • 2,628
  • 4
  • 36
  • 51
  • I must use regular expession with search and replace, because point is also delimiter for milliseconds and I cannot use fast replace with e.g. FART (http://fart-it.sourceforge.net/). We are speaking here about huge csv-files (above 100M rows) and I cannot believe, that I need extra conversion step with source file or SQL to achieve proper type conversion? – Bern Liefer Jun 20 '16 at 11:12
  • 1
    PS Thanl you very much for your suggestion, CAST works properly. But I search for solution without extra step, pehpaps with BULK INSERT and format file - will it work? – Bern Liefer Jun 20 '16 at 11:20
  • I don't think you need an "extra" step - when importing your input column within your flatfile connection should be set to DT_DBTIMESTAMP2. – BIDeveloper Jun 20 '16 at 11:26
0

Pure SSIS dataflow solution - Derived Column transformation with the following formula for NewDateCol 

(DT_DBTIMESTAMP2)(REPLACE(SUBSTRING(OldDateCol,1,10),".","-")+SUBSTRING(OldDateCol, 11, LEN(OldDateCol)))

Basically, this expression replaces . with - on the first 10 symbols and then appends the rest.

Ferdipux
  • 5,116
  • 1
  • 19
  • 33
  • Why do you need to do a replace? – BIDeveloper Jun 20 '16 at 14:20
  • @BIDeveloper, string to be casted to SSIS Datetime has to be in format YYYY-HH-MM hh:mm:ss.nnn. So, before type cast periods should be replaced with dashes. – Ferdipux Jun 20 '16 at 14:38
  • Try this: SELECT CAST('2012.05.06 11:25:33.123' AS DATETIME) ...No **REPLACE** required. – BIDeveloper Jun 20 '16 at 14:49
  • @BIDeveloper, the point of this character replace is to completely eliminate need for temp holding table from your answer. Pure SSIS data transformation without additional SQL roundtrip. – Ferdipux Jun 20 '16 at 15:05
  • ... yes... and as I said in my answer... you don't need one :-) Furthermore, there is no need to do a REPLACE. Which was my original point to you. – BIDeveloper Jun 20 '16 at 15:10
  • 1
    @BIDeveloper, just tested your setup - my server has russian locale so it expects datetime as YYYY/MM/DD .... Created test file with two lines of data - like 2016.01.02 00:00:01.123, described this field as DT_DBTIMESTAMP2 in Flat File Source. Data Viewer displays an error "ttt Error: Year, Month, and Day parameters describe an un-representable DateTime." So, when SSIS engine cannot parse string as DateTime in its own, you have to align it with standard format YYYY-MM-DD .... – Ferdipux Jun 20 '16 at 15:23
  • I don't think, that it is an SQL issue, because i can execute this without error in my german database:DECLARE @datevar datetime2 = '2015.09.28 00:00:01.159'; SELECT @datevar; GO Nevertheless I habe allready enabled and created contained databases with english and british english as default language - same error. I have also created the user with english and british english as default language - same error. – Bern Liefer Jun 21 '16 at 08:06
  • And I have allready tested with different DB-Providers (ODBC, OLE DB und native) - without success. I think, that it is the SSIS string parser, that cannot correctly translate such date format as YYYY.MM.DD from string literal. Can you help me, what language has actually such date format? – Bern Liefer Jun 21 '16 at 08:06
  • @BernLiefer - SSIS default format for datetime string literal is **YYYY-MM-DD hh:mm:ss.nnn** [my reply on similar topic](http://stackoverflow.com/a/37804038/1010622). – Ferdipux Jun 21 '16 at 08:15
0

My workaround: 1. CREATE TABLE with datetime (it makes milliseconds rounding error - 2015.09.28 00:00:01.159 is 2015-09-28 00:00:01.160 etc., for whatever reason) or datetime2(3) (2015.09.28 00:00:01.159 is 2015-09-28 00:00:01.1590000 - correct but oversized) 2. SET DATEFORMAT ymd 3. BULK INSERT from csv with TABLOCK and CODEPAGE='RAW' to increase perfomance

Bern Liefer
  • 1
  • 1