DSXchange

Can we do a join here, I am not sure if your source and lookup are both coming from tables on same database, if yes then we can do a join on Table1 and table2 Select KeyId2 from table2 where date2 = (Select Max(table2.Date2) From Table1, Table2 where Table1.Date1<Table2.Date2) You can map this KeyId...

So you have some rows getting rejected but not getting inserted into Error Table. Do you have the insert privileges on that table, Is the metadata same and also are there any constraint violations.. I woud try inserting some rows into this table first.

Is this the only message you are getting in the log. Can you please post the log.

assuming date2 for one rec is 10th and other record is 9th and you have date 1 as 8th. In this case you have 2 records greater than 8th. You can select the max(date2) to get the latest from them.

HTH

what is the memory avaialble , you can check with ulimit -a

Thanks for all the inputs. I am waiting to hear from our admin. I will post the results as it comes.

Thanks

Below is all the error in the log when we are bulk loading into SQLServer. Username,Pwd,tablename and the Server Name seem to be fine as we could load it using an ODBC stage.Please advice.

SQL server OLEDB provider authorization failed.
Failed to establish datasource connection.

You can change your NLS settings in Job Properties window or within each stage for a job. Let us know whats your current setting in there.

vardate= DateToString(Date,'YYYY-MM-DD') and then

StringToDate(((vardate)[6, 2]:(vardate)[9,2]:(vardate)[1,4]),'DDMMYYYY')

HTH

Looks like an issue with disk space on unix. Check the disk space.

vardate=(TO_CHAR(DATE, 'YYYY-MM-DD'), 6, 2):(TO_CHAR(DATE, 'YYYY-MM-DD'), 9, 2):(TO_CHAR(DATE, 'YYYY-MM-DD'), 1,4)

and then convert this back to Date

TO_DATE(vardate,'MMDDYYYY')

You can use an ODBC Enterprise Stage or a DRS stage , depending on your requiremnnt. Enterprise stage is the option.

Keep you Column name as
COL_ID
NEXT_COL
and in the derivation mention
COL_ID#
NEXT_COL#

in your table definitions and see how it works..And one more thing are the columns named as COL_ID#,NEXT_COL# on the DB2 database side.

Is this the complete log message?

What are your NLS Settings for your job. What happens when you set NLS to NONE.