Issue while inserting to SQL server table

ajithaselvan · Post by **ajithaselvan** » Wed Sep 21, 2011 5:34 am

Hi,
My job is to insert data from file to SQL server.
Below is my Job flow.
Seq file --->transformer --->SQL Server.
I'm reading date as varchar and inserting as Timestamp.
Eg for date: '8/29/2011 5:26:03AM'

1. when I tried to insert without any conversion, below error is I'm getting

odbc_inc_stg: Schema reconciliation detected a type mismatch for field inc_sys_created_on. When moving data from field type VARCHAR(min=0,max=25) into DATETIME(fraction=3), data corruption can occur.

2. when I tried to insert after convering varchar to Timestamp, below is are the warnings i'm getting.

APT_CombinedOperatorController,2: Data string '8-29-2011 5:26:03AM' does not match format '%yyyy-%mm-%dd %hh:%nn:%ss': the value for tag %yyyy has fewer characters than expected.

Could you please help me to fix this issue?

Regards,

suse_dk · Post by **suse_dk** » Wed Sep 21, 2011 5:50 am

You need to tell DS what your timestamp from the source looks like...

The format for '8-29-2011 5:26:03AM' is not '%yyyy-%mm-%dd %hh:%nn:%ss'

ajithaselvan · Post by **ajithaselvan** » Wed Sep 21, 2011 6:03 am

Thanks Sussen. But I tried converting to Timestamp format: '%yyyy-%mm-%dd %hh:%nn:%ss'. Below are the warnings i'm getting.

odbc_inc_stg: Schema reconciliation detected a type mismatch for field inc_sys_created_on. When moving data from field type VARCHAR(min=0,max=25) into DATETIME(fraction=3), data corruption can occur.

odbc_inc_stg: Error when checking operator: When binding input interface field "inc_sys_created_on" to field "inc_sys_created_on": No default type conversion from type "string[max=25]" to type "timestamp[microseconds]".

Regards,

ajithaselvan · Post by **ajithaselvan** » Wed Sep 21, 2011 7:10 am

Can anybody help me out?

Regards

chulett · Post by **chulett** » Wed Sep 21, 2011 7:15 am

Probably. Be patient.

jwiles · Post by **jwiles** » Wed Sep 21, 2011 7:36 am

The clue you're looking for is in suse_dk's answer:

You need to tell DS what your timestamp from the source looks like...

The format for '8-29-2011 5:26:03AM' is not '%yyyy-%mm-%dd %hh:%nn:%ss'

When converting to a timestamp, you need to specify the format of the string you are converting from...that's the format that DataStage needs to be told about. This topic has been covered many times in the forum, and you can also look in the Parallel Job Developer's Guide for more information on time and date format strings.

Regards,

ray.wurlod · Post by **ray.wurlod** » Wed Sep 21, 2011 3:48 pm

ajithaselvan wrote:Can anybody help me out?

Groucho Marx wrote:I'd love to help you out - which way did you come in?

ajithaselvan · Post by **ajithaselvan** » Thu Sep 22, 2011 12:12 am

as per suse_dk and james, I tried converting to Timestamp format: '%yyyy-%mm-%dd %hh:%nn:%ss'. Below is the warnings i'm getting.

odbc_inc_stg: Schema reconciliation detected a type mismatch for field inc_sys_created_on. When moving data from field type VARCHAR(min=0,max=25) into DATETIME(fraction=3), data corruption can occur

suse_dk · Post by **suse_dk** » Thu Sep 22, 2011 12:56 am

For me it looks like you are trying to put a varchar into a datetime... where exactly are you doing your type conversion? - in a transformer?

ray.wurlod · Post by **ray.wurlod** » Thu Sep 22, 2011 12:59 am

String to timestamp is not an implicit conversion - you will need to specify a conversion function in a Modify or Transformer stage to achieve a data type of Timestamp.

ajithaselvan · Post by **ajithaselvan** » Thu Sep 22, 2011 1:58 am

I'm doing the type conversion in Transformer.
Source:8/29/2011 5:28:51AM

below is the conversion logic

StringToTimestamp(lnk_input_to_filter.inc_sys_created_on,"%mm/%dd/%yyyy %hh:%nn:%ss")

Warning:
xfrm_on_lan_id,1: Data string '8/29/2011 5:28:51A' does not match format '%mm/%dd/%yyyy %hh:%nn:%ss': the value for tag %mm has fewer characters than expected.

suse_dk wrote:For me it looks like you are trying to put a varchar into a datetime... where exactly are you doing your type conversion? - in a transformer?

ajithaselvan · Post by **ajithaselvan** » Thu Sep 22, 2011 1:59 am

I'm doing the type conversion in Transformer.
Source:8/29/2011 5:28:51AM

below is the conversion logic

StringToTimestamp(lnk_input_to_filter.inc_sys_created_on,"%mm/%dd/%yyyy %hh:%nn:%ss")

Warning:
xfrm_on_lan_id,1: Data string '8/29/2011 5:28:51A' does not match format '%mm/%dd/%yyyy %hh:%nn:%ss': the value for tag %mm has fewer characters than expected.

ray.wurlod · Post by **ray.wurlod** » Thu Sep 22, 2011 2:04 am

You don't have two digits in the month but your format string says that you do.

Do learn to read what the error messages are telling you.

ajithaselvan · Post by **ajithaselvan** » Thu Sep 22, 2011 2:16 am

I understand the error message. If my input is 08/29/2011 05:28:51AM,
I will not be getting any problem in inserting.
But my source is 8/29/2011 5:28:51AM. If I add leading zero to month (as 08), again there is possibility to get same warning for %hh also as source does not have 2 digits in the format.
moreover I may get source value as 12/29/2011 15:28:51AM.

How to handle both the cases?

chulett · Post by **chulett** » Thu Sep 22, 2011 7:28 am

Documentation is a wonderful thing. The "s" option allows leading spaces in date and/or time formats. For example:

Code: Select all

%(m,s)/%(d,s)/%yyyy %(h,s):%(n,s):%(s,s)

Would handle single digits in the month, day, hour, minute and second. You can also find discussions on how to handle the "am/pm marker" there.