DSXchange

Hope you could help out with my second issue

My job design is like:
sequencial file stage=>transformer=>dataset

the sequencial file is fixed-width, timestamps are in the format: YYYYMMDDHHMISS, for example, 20070227124900 and are filled with spaces where optional.

The SQL Type in the sequencial file stage is VARCHAR(14). I did the conversion in the transformer stage, the logic is like:
1. First trim trailing spaces and store into a stage variable say SVTimestamp
2. if SVTimestamp = '' Then SetNull()
3. If IsValid("Timestamp", StringToTimestamp(SVTimestamp,"%yyyy%mm%dd%hh%nn%ss") Then StringToTimestamp(SVTimestamp,"%yyyy%mm%dd%hh%nn%ss") Else SetNull()

If the incoming timestamps are invalid, e.g. 20070227124961, the conversion in the IsValid will produce a warning in the job log.

Am I right? How can I eliminate the warning from the job log? Appreciate your help.

You can create a job-specific message handler to demote these warnings to informational.

Thanks for your direction, Ray. Do you know where I can get documentation about this?

ray.wurlod wrote:You can create a job-specific message handler to demote these warnings to informational. ...

The quick way is to right click on a warning message and "add rule to message handler". While in that dialog, click Help.

It will be in one of the manuals, but I don't have time to research which one. I'd guess Manager Guide would be the first place I'd look.

Edward,

Can you post the warning you received?. You should not receive any warning. If the incoming timestamp is invalid then IsValid() function will return 0 and control passes to else condition.

The job flow:
sequential file=>transformer=>dataset

The input SQL Type : varchar(14)
The output SQL Type: timestamp
The deviration logic in the transfomer:
If Trim(DSLink10.ColTimestamp) = "" Or IsValid("timestamp", StringToTimestamp(Trim(DSLink10.ColTimestamp) ,"%yyyy%mm%dd%hh%nn%ss")) <> @TRUE Then
SetNull()
Else
StringToTimestamp(Trim(DSLink10.ColTimestamp) ,"%yyyy%mm%dd%hh%nn%ss")

There are 3 input values:
"20020101010161"
"20020101010101"
" " (14 spaces)

The warning I had is for the first record:
APT_CombinedOperatorController,0: Conversion error calling conversion routine timestamp_from_string data may have been lost

I think it is becuase the StringToTimestamp converion in the IsValid function. Can I use the IsValid directly to the string "20020101010161"? Any ideas?

balajisr wrote:Edward,

Can you post the warning you received?. You should not receive any warning. If the incoming timestamp is invalid then IsValid() function will return 0 and control passes to else condition.

Edward
you don't need to supress the warning. I have an stupid idea to avoid the warning. just replace your code in transformation to this:

If NOT (IsValid("Timestamp", (DSLink10.ColTimestamp[1,4]:"-":DSLink10.ColTimestamp[5,2]:"-":DSLink10.ColTimestamp[7,2]:" ":DSLink10.ColTimestamp[9,2]:":":DSLink10.ColTimestamp[11,2]:":":DSLink10.ColTimestamp[13,2]))) then SetNull() Else StringToTimestamp(Trim(DSLink10.ColTimestamp) ,"%yyyy%mm%dd%hh%nn%ss")

rather then

If Trim(DSLink10.ColTimestamp) = "" Or IsValid("timestamp", StringToTimestamp(Trim(DSLink10.ColTimestamp) ,"%yyyy%mm%dd%hh%nn%ss")) <> @TRUE Then
SetNull()
Else
StringToTimestamp(Trim(DSLink10.ColTimestamp) ,"%yyyy%mm%dd%hh%nn%ss")

Are you sure it is for the 1st record. I just ran a test job with the 3 values given by you and had no warnings. Do you have only these three values in the file?

20020101010101 is converted into 2002-01-01 01:01:01 and IsValid returns 1.

20020101010161 is not a valid timestamp so it has been converted into null and IsValid returns 0.

Can you set APT_DISABLE_COMBINATION environment to true in your job and post the results.

Ray,

Is there any reason you advocated using message handler?

Because the question was how to eliminate warning from job log, and I treated it literally.