(Scroll to the end for a summary...)
Hi Craig,
OK, I've got a DS server job as follows:
SeqFile --> Extract (link) --> xfm --> Load (link) --> DRS Stage (MS SQL Server)
The SeqFile contains two rows:
2009-09-30
2009-09-30 12:34:56
The column is defined varchar (doesn't really matter for a seq file anyway)
The xfm is a straight mapping to the SQL Server table.
The SQL Server table has a single column "Date" of type datetime.
The DRS update action is "Clear table then insert rows".
Scenarios:
1) Load link has metadata type timestamp:
Generated SQL:
INSERT INTO dbo.ScottDateTesting (Date) VALUES (TO_DATE(?, 'YYYY-MM-DD HH24:MI:SS'))
Results:
2 rows loaded
1st row fails, 2nd row succeeds
Log (Warnings only):
ScottTest4..Transformer_1: At row 1, link "Load", while processing column "Date"
Value treated as NULL
Attempt to convert String value "2009-09-30" to Timestamp type unsuccessful
2) Load link has metadata type date:
Generated SQL:
INSERT INTO dbo.ScottDateTesting (Date) VALUES (?)
Results:
2 rows loaded
Both rows fail
Log (Warnings only):
ScottTest4..Transformer_1: At row 1, link "Load", while processing column "Date"
Value treated as NULL
Attempt to convert String value "2009-09-30" to Date type unsuccessful
ScottTest4..Transformer_1: At row 2, link "Load", while processing column "Date"
Value treated as NULL
Attempt to convert String value "2009-09-30 12:34:56" to Date type unsuccessful
3) Load link has metadata type varchar:
Generated SQL:
INSERT INTO dbo.ScottDateTesting (Date) VALUES (?)
Results:
0 rows loaded
Obviously both rows fail
(Funnily enough, "View data" shows two blank rows. If the table was cleared first, then zero rows loaded, I would think it would be empty?)
Log (Warnings only):
ScottTest4..Transformer_1: [DataDirect][ODBC SQL Server Driver][SQL Server]Arithmetic overflow occurred.
ScottTest4..Transformer_1: Date = 2009-09-30
ScottTest4..Transformer_1: At row 1, link "Load"
Row rejected.
ScottTest4..Transformer_1: [DataDirect][ODBC SQL Server Driver][SQL Server]Arithmetic overflow occurred.
ScottTest4..Transformer_1: Date = 2009-09-30 12:34:56
ScottTest4..Transformer_1: At row 2, link "Load"
Row rejected.
***** Now, change seq file data from YYYY-MM-DD to YYYY-DD-MM:
2009-30-09
2009-30-09 12:34:56
The auto-generated TO_DATE function has the string 'YYYY-MM-DD...', but I've confirmed that this does not work in the SQL client. See original post, but to recap:
DELETE FROM dbo.ScottDateTesting
INSERT INTO dbo.ScottDateTesting (Num,Date) VALUES (1,'2009-30-09') /* works, even without the hhmmss */
INSERT INTO dbo.ScottDateTesting (Num,Date) VALUES (2,'2009-09-30') /* fails */
SELECT * FROM dbo.ScottDateTesting
SQL Server client log:
Msg 242, Level 16, State 3, Line 4
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
The statement has been terminated.
The Num=1 row was inserted, but not Num=2 row...
*****
4) Load link has metadata type timestamp:
Generated SQL:
INSERT INTO dbo.ScottDateTesting (Date) VALUES (TO_DATE(?, 'YYYY-MM-DD HH24:MI:SS'))
Results:
1 row loaded
However, "View data" returns Empty data source, so go figure on the performance statistics returned to the job!
Log (Warnings only):
ScottTest4..Transformer_1: At row 1, link "Load", while processing column "Date"
Value treated as NULL
Attempt to convert String value "2009-30-09" to Timestamp type unsuccessful
ScottTest4..Transformer_1: [DataDirect][ODBC SQL Server Driver]Invalid date format
ScottTest4..Transformer_1: Date = 2009-30-9 12:34:56.0
ScottTest4..Transformer_1: At row 2, link "Load"
Row rejected.
5) Load link has metadata type date:
Generated SQL:
INSERT INTO dbo.ScottDateTesting (Date) VALUES (?)
Results:
2 rows loaded
Both rows fail
Log:
ScottTest4..Transformer_1: At row 1, link "Load", while processing column "Date"
Value treated as NULL
Attempt to convert String value "2009-30-09" to Date type unsuccessful
ScottTest4..Transformer_1: At row 2, link "Load", while processing column "Date"
Value treated as NULL
Attempt to convert String value "2009-30-09 12:34:56" to Date type unsuccessful
6) Load link has metadata type varchar:
Generated SQL:
INSERT INTO dbo.ScottDateTesting (Date) VALUES (?)
Results:
2 rows loaded
Both rows succeeded!!!
Yay!
So, after two hours of hacking around
, what works in writing date values via the DRS stage to SQL Server datetime column:
1) Make your metadata timestamp, use generated SQL code TO_DATE(?,'YYYY-MM-DD HH24:MM:SS'), and ensure your data is formatted in YYYY-MM-DD format (must include HH:MM:SS - could always just whack on 00:00:00)
2) Make your metadata varchar, generated SQL code will not include the TO_DATE function, and ensure your data is formatted in YYYY-
DD-MM format (actually any format which works in native SQL Server will do). You DON'T have to include the time portion - SQL Server will 00:00:00 if it's not supplied - convenient.
3) Finally, I also tried YYYY-MON-DD format, which also works natively in SQL Server (again, see original post):
2009-SEP-30
2009-SEP-30 12:34:56
This also worked (also worked without the dashes)
Sorry for the long post, in a long thread. Perhaps someone will find this useful in a future search, although the chance that they'll scroll to the end of this thread is minimal
Regards,
Scott