Handling Auto Generated column values via RCP

[VCInDSX]

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

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;
)
.

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

[him121]

Change Transformer property to run on a Sequential Mode.

[ray.wurlod]

RCP only works for pure pass-through.

If you neeed to derive an output column from an input column by any other means, or to use an input column in a stage variable or reference key expression, or to use an input column in an output link constraint, then that input column must explicitly be mentioned in the input link schema.

[VCInDSX]

Thanks a lot for your time him121 and Ray. Appreciate it much.
Setting the transformer to Sequential Mode did not work him121. I selected "Sequential" for the "Execution Mode" in the Advanced page of the Transformer stage properties. It has the same error. If this is not what you meant, please let me know.

Ray,
In my case, I don't have an input column that I want to work with - in my transformer. This is a column that is not coming in as a part of the source feed, but an internal maintenance field that can be populated by the Database whenever a new record is added. This is a column that will be a part of all the database tables in our application and not provided by the incoming feeds. From your response, it appears that I will not be able to make this work using RCP.
We were planning to have a single job to handle most of the feeds from different providers by supplying the schema files to this job. Looks like the only way to do that is to have specific jobs for each feed file.

Thanks again for your time and input,