Page 1 of 1
Problem with Date stored in Shared container
Posted: Mon Feb 06, 2006 4:48 pm
by pmadhavi
Hi
I have a shared container attached to the taget DRS which has the latest timestamp stored.
the creteria given in source is to get the records which have timestamp greater than the timestamp stored in the shared container.
During the initial run as there wont be any data stored in date column of shared container, it is not satisfying the condition data is not loaded in the target.
pls suggest me what to do for the initial load
Posted: Mon Feb 06, 2006 5:20 pm
by ArndW
Can you put in a condition into your transform stage output derivation for this value which tests for a null or notfound?
Re: Problem with Date in Shared container
Posted: Tue Feb 07, 2006 3:01 pm
by sun rays
pmadhavi wrote:Hi
I have a shared container attached to the taget DRS which has the latest timestamp stored.
the creteria given in source is to get the records which have timestamp greater than the timestamp stored in the shared container.
During the initial run as there wont be any data stored in date column of shared container, it is not satisfying the condition data is not loaded in the target.
pls suggest me what to do for the initial load
how does your source know, what value is stored in your shared container? Are you passing/using some parameters?
Also could you tell us , what is in your shared container? Are you writing it to a hash file in the shared container?
Re: Problem with Date in Shared container
Posted: Tue Feb 07, 2006 3:48 pm
by pmadhavi
sun rays wrote:pmadhavi wrote:Hi
I have a shared container attached to the taget DRS which has the latest timestamp stored.
the creteria given in source is to get the records which have timestamp greater than the timestamp stored in the shared container.
During the initial run as there wont be any data stored in date column of shared container, it is not satisfying the condition data is not loaded in the target.
pls suggest me what to do for the initial load
how does your source know, what value is stored in your shared container? Are you passing/using some parameters?
Also could you tell us , what is in your shared container? Are you writing it to a hash file in the shared container?
The last modified date is stored in the shared container.
We have a where clause in the source DRS as
Dttm_stamp_sec> %datetimein(#LastModifiedDateTime#)
I need to convert the date of %datetimein(#LastModifiedDateTime#)
to 'yyyy-mm-dd hh:mm:ss' format
As i am not able to do it, we are getting error at the first stage itself.
Pls guide me
Re: Problem with Date in Shared container
Posted: Tue Feb 07, 2006 9:26 pm
by loveojha2
pmadhavi wrote:
The last modified date is stored in the shared container.
We have a where clause in the source DRS as
Dttm_stamp_sec> %datetimein(#LastModifiedDateTime#)
I need to convert the date of %datetimein(#LastModifiedDateTime#)
to 'yyyy-mm-dd hh:mm:ss' format
As i am not able to do it, we are getting error at the first stage itself.
Pls guide me
How are you running your job? Are you running it directly (server job) or through a sequence?
What value you are passing in #LastModifiedDateTime# parameter?
Even if it is '' then also it works well (SQL server)..
Posted: Tue Feb 07, 2006 10:34 pm
by Andal
How you are taking date stored in a Hashed file. If you are fetching the date through a routine, write the logic in the routine itself. I hope you will be storing the Date in the Hashed file with Jobname.
So while reading the Hashed file for fetching the date, Just check if an entry for the jobname is there already, If it is there pass it as a output else pass the default value (1900-01-01 00:00:00) as Output.
Now you will get matching records and your job will run for the Initial load.
For converting Date to the Specified format also, you can write the logic in the routine itself.
Code: Select all
MaxVal= "You date" (Something like 1900-01-01 00:00:00)
Temp1 = LEFT(MaxVal,10)
Temp2 = RIGHT(MaxVal,8)
MaxDate = OCONV(Temp1 , "D-YMD[4,2,2]")
MaxTime = OCONV(Temp2 , "MTS")
Ans = MaxDate:" ":MaxTime
Posted: Tue Feb 07, 2006 11:17 pm
by loveojha2
Hey Anand as I said it would work well even with %datetimein('').
No need for going into Routine.
In fact
Code: Select all
%DateTimeIn('0000-00-00 00:00:00') > %DateTimeIn('')
is true.
So it is sure to run for all the rows (Initially), if it is coming as ''.
Re: Problem with Date in Shared container
Posted: Wed Feb 08, 2006 12:01 pm
by sun rays
="pmadhavi
The last modified date is stored in the shared container.
We have a where clause in the source DRS as
Dttm_stamp_sec> %datetimein(#LastModifiedDateTime#)
I need to convert the date of %datetimein(#LastModifiedDateTime#)
to 'yyyy-mm-dd hh:mm:ss' format
As i am not able to do it, we are getting error at the first stage itself.
Pls guide me
I am sure , you must be using a Job sequence to accomplish this.
The first step might be to use a routine to get the date from the hash file.
The next step would be to send this return value as a parameter to the actual job that is reading the source table.
If this is the scenario then you can just go the job Activity(job that is reading the source table) in your job Sequence, right click and go to properties. Here you would be seeing the parameters that are passed to the job activity, change the value of the parameter to some default date or any date less than the Minimum date of your source table.
This would actually avoid any modifications to the where clause, or the routine itself.
For this you need to include the date parameter in your job parameters in your Job Sequence.
Re: Problem with Date in Shared container
Posted: Wed Feb 08, 2006 12:08 pm
by sun rays
Forgot to mention !
After your first run, you need to change the properties of the job activity in you job Sequence.
The value for the date parameter must be the return value of your routine.
Let us know if this works.