Handling Auto Generated column values via RCP
Posted: Fri Sep 21, 2007 12:00 pm
Hi,
I have a simple RCP test job to learn RCP and then put it into practice in a real job. I am a novice and would appreciate your help.
Sequential File ==> Transformer ==> SQL Server Enterprise
The input file has 3 fields
EmpId nvarchar(50)
EmpName nvarchar(255)
Address1 nvarchar(255)
The Destination SQL Server Table has the following definition
CREATE TABLE [EmployeesTS](
[EmpId] [nvarchar](20) NOT NULL,
[EmpName] [nvarchar](255) NULL,
[Address1] [nvarchar](255) NULL,
[RawRecCreatedDate] [datetime] NULL CONSTRAINT [Dft_Employee_RawRecCreatedDate] DEFAULT (getdate())
) ON [PRIMARY]
The RCP schema is given below
record
{final_delim=end, record_delim='\n', delim='|', quote=none, padchar='#'}
(
EmpId:ustring[max=50] {quote=none};
EmpName:nullable ustring[max=255] {null_field='', quote=none};
Address1:nullable ustring[max=255] {null_field='', quote=none};
)
The RawRecCreatedDate field in the DB Table is designed to be auto-populated whenever a new record is inserted. However, in the RCP job, the compilation fails if this field is not mapped in the input schema or supplied in the transformer. I added a derivation in the output of the transformer to set the DSJobStartTimeStamp. This satisfies the compilation and validation rules. When i look at the OSH schema output, the log shows the following definition
With this setup, the SQLServerDB stage automatically picks the RawRecCreatedData field in its definition.
When i run this job, i get the following error...
TargetDB,0: 22007 : [Microsoft][ODBC SQL Server Driver]Invalid date format
01004 : [Microsoft][ODBC SQL Server Driver]String data, right truncation
[sqlsrvrConnection.C:295]
From the error message, it appears that the one of the input text fields, probably the first one, (EmpId, EmpName, Address1) is being attempted to load into the destination RawRecCreatedDate field of the table.
How can I get the settings right so that the derived field goes in at the end of the 3 fields from input?
Thanks in advance for your time and help
I have a simple RCP test job to learn RCP and then put it into practice in a real job. I am a novice and would appreciate your help.
Sequential File ==> Transformer ==> SQL Server Enterprise
The input file has 3 fields
EmpId nvarchar(50)
EmpName nvarchar(255)
Address1 nvarchar(255)
The Destination SQL Server Table has the following definition
CREATE TABLE [EmployeesTS](
[EmpId] [nvarchar](20) NOT NULL,
[EmpName] [nvarchar](255) NULL,
[Address1] [nvarchar](255) NULL,
[RawRecCreatedDate] [datetime] NULL CONSTRAINT [Dft_Employee_RawRecCreatedDate] DEFAULT (getdate())
) ON [PRIMARY]
The RCP schema is given below
record
{final_delim=end, record_delim='\n', delim='|', quote=none, padchar='#'}
(
EmpId:ustring[max=50] {quote=none};
EmpName:nullable ustring[max=255] {null_field='', quote=none};
Address1:nullable ustring[max=255] {null_field='', quote=none};
)
The RawRecCreatedDate field in the DB Table is designed to be auto-populated whenever a new record is inserted. However, in the RCP job, the compilation fails if this field is not mapped in the input schema or supplied in the transformer. I added a derivation in the output of the transformer to set the DSJobStartTimeStamp. This satisfies the compilation and validation rules. When i look at the OSH schema output, the log shows the following definition
Code: Select all
main_program: Schemas:
Data set "SF_RCPInputFile:SF_To_XFM_Lnk.v":
record
( EmpId: ustring[max=50];
EmpName: nullable ustring[max=255];
Address1: nullable ustring[max=255];
)
Data set "SF_RCPInputFile:SourceRejects_Lnk.v":
record
( rejected: raw;
)
Data set "XFM_RCP_2_Database:XFM_To_DB_Lnk.v":
record
( RawRecCreatedDate: nullable timestamp;
EmpId: ustring[max=50];
EmpName: nullable ustring[max=255];
Address1: nullable ustring[max=255];
)
Operator "SF_RCPInputFile":
output 0 interface:
record
( EmpId: ustring[max=50];
EmpName: nullable ustring[max=255];
Address1: nullable ustring[max=255];
)
output 1 interface:
record
( rejected: raw;
)
Operator "XFM_RCP_2_Database":
input 0 interface:
record
( APT_TRinput0Rec0: *;
EmpId: ustring[max=50];
EmpName: nullable ustring[max=255];
Address1: nullable ustring[max=255];
)
output 0 interface:
record
( RawRecCreatedDate: nullable timestamp;
APT_TRoutput0Rec0: *;
)
Operator "TargetDB":
input 0 interface:
record
( RawRecCreatedDate: nullable timestamp;
EmpId: ustring[max=50];
EmpName: nullable ustring[max=255];
Address1: nullable ustring[max=255];
)
Operator "SF_Source_Rejects":
input 0 interface:
record
( rejected: raw;
)
.
When i run this job, i get the following error...
TargetDB,0: 22007 : [Microsoft][ODBC SQL Server Driver]Invalid date format
01004 : [Microsoft][ODBC SQL Server Driver]String data, right truncation
[sqlsrvrConnection.C:295]
From the error message, it appears that the one of the input text fields, probably the first one, (EmpId, EmpName, Address1) is being attempted to load into the destination RawRecCreatedDate field of the table.
How can I get the settings right so that the derived field goes in at the end of the 3 fields from input?
Thanks in advance for your time and help