I am using DataStage 7.5.1
I am trying to load a test csv file which reads like this:
"16/11/2005 13:58:38","17/05/2007 09:59:29",0,"This one does not have a null date"
"16/11/2005 13:58:38",,1,"This one does have a null date in field 2"
"16/11/2005 13:58:38",,2,"This one does have a null date in field 2"
,"16/11/2005 13:58:38",3,"This one does have a null date in field 1"
My job is very simple, it has a sequential file stage and an Oracle Enterprise stage. The job is supposed to read the sequential file and load the data into the database. It is supposed to load the empty date fields in oracle as NULL values.
In my sequential file stage, I define the columns as;
Field 1 ****See image****
-- SQL Type = Timestamp
-- Format string = %dd/%mm/%yyyy %hh:%nn:%ss
-- Nullable = Yes
Field 2 ****See image****
-- SQL Type = Timestamp
-- Format string = %dd/%mm/%yyyy %hh:%nn:%ss
-- Nullable = Yes
Field 3
-- SQL Type = Integer
-- Nullable = No
Field 4
-- SQL Type = VarChar(255)
-- Nullable = No
When I run the job I get the message:
Sequential_File,0: Import complete; 1 records imported successfully, 3 rejected.
The only record that goes into the database is the first one.
("16/11/2005 13:58:38","17/05/2007 09:59:29",0,"This one does not have a null date")
I have used a workaround for this before by including a transformer in the job and bringing the timestamp in as a varchar and then converting it to a timestamp in the transformer, however it is not a very elegant solution.
I would like to define the data types of the csv file in the sequential file definition where it belongs.
And about the asterisks... It looks like DataStage automatically replaces the null dates with 19 asterisks (*******************) and I beleive this is what causes the rejection into the database.
![Image](http://www.ibm.com/developerworks/forums/servlet/JiveServlet/download/826-171359-13978604-256010/Edit_Column_Meta_Data.jpg)