Date conversion From flat file to Sql Server table

kbsuryadev · Post by **kbsuryadev** » Thu Jan 17, 2008 1:39 pm

I have a csv file with with tab delimited, a date column (i.e 1/6/2008) which i am loading into a SQL server(2005) table ( mm/dd//yyyy)

Seqstage >>> trn >>>>ODBC(sql server table)
varchar timestamp

I am using line termination as DOS Style
delimeter as 009(tab) i can view the data ( i.e 1/6/2008)

using data type as varchar in the seq file stage and timestamp as in the target

Error:
Transformer_16.DSLink19: DSD.BCIPut call to SQLExecute failed.
SQL statement:INSERT INTO xxxxx(x) values (?)
SQLSTATE=S1010, DBMS.CODE=0
[DataStage][SQL Client]Function call is illegal at this point..

Any help is appreciated.

ray.wurlod · Post by **ray.wurlod** » Thu Jan 17, 2008 4:07 pm

You are connecting to SQL Server through ODBC. Dates must be in ODBC-legal format.

However, your error message appears to indicate that you are using some kind of function in the SQL. This is not permissible for INSERT, only for SELECT.

Can you post your SQL statement as well as the exact error message (without censoring the SQL statement)?

kbsuryadev · Post by **kbsuryadev** » Thu Jan 17, 2008 4:54 pm

This is the sql

INSERT INTO test_Cashier_Writeback(property, data_date, cashier_id, dept, category, outlet, cashier_fname, cashier_lname, Infogen_cash, priorday_cash_timing, currentday_cash_timing, tips_paidout, priorday_tip_adj, currentday_tip_adj, accountable_cash, cashdrop_amt, dueback_amt, bank_amt, adjusted_cash, cash_diff) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);

Source Datatype is varchar ; target Datatype is timestamp

I am using transformer just to move the data.

These are the errors in Director log

Corp_Strategy_Upload.MGM2.EDWMART_EBA.DSLink19: DSD.BCIOpenW results of SQLColAttributes(data_date) gave MetaData mismatch
COLUMN.TYPE Expected = Timestamp Actual = Binary

Corp_Strategy_Upload.MGM2.Sequential_File_21.DSLink12: read_delimited() - row 1, column data_date, required column missing

kbsuryadev · Post by **kbsuryadev** » Thu Jan 17, 2008 5:14 pm

This is another error i see in the director log

Corp_Strategy_Upload.MGM1.Transformer_16.DSLink19: DSD.BCIPut call to SQLExecute failed.
SQL statement:INSERT INTO test_Cashier_Writeback(property, data_date, cashier_id, dept, category, outlet, cashier_fname, cashier_lname, Infogen_cash, priorday_cash_timing, currentday_cash_timing, tips_paidout, priorday_tip_adj, currentday_tip_adj, accountable_cash, cashdrop_amt, dueback_amt, bank_amt, adjusted_cash, cash_diff) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
SQLSTATE=22005, DBMS.CODE=0
[DataStage][SQL Client]Non-numeric data was found where numeric required

ray.wurlod · Post by **ray.wurlod** » Thu Jan 17, 2008 9:10 pm

For "non-numeric where numeric required" error inspect the remainder of the error message where the values are reported. A non-numeric value will usually have quote characters surrounding it. Find it and fix it.

"Required column missing" indicates that your metadata for the sequential file do not match what's actually in the file at least in row #1.

The "metadata mismatch" warning indicates that your metadata in your job design does not coincide with the actual table definition in the database. the column in question is data_date. Check, and change the metadata in DataStage to match what's in the database. Best practice would be to re-import the table definition, then re-load that into the job: in this way the linkages between the Repository and the job are preserved.

None of these more recent posts addresses your original question "function call is illegal at this point". Did you fix that?

kbsuryadev · Post by **kbsuryadev** » Thu Jan 17, 2008 10:08 pm

How do i convert the source varchar to target timestamp in transformer.i am new to using SQL server databases.

Thank you for your patiency and help

ray.wurlod · Post by **ray.wurlod** » Thu Jan 17, 2008 10:24 pm

There are routines/transforms in the SDK that you can use. Why re-invent the wheel?

Ultimately, though, you need to re-organize the components of the date into yyyy-mm-dd format (guaranteeing 4, 2 and 2 digits) then add a time component. (Actually, I believe SQL Server is happy enough without the time component, but the ODBC driver may not be.)

kbsuryadev · Post by **kbsuryadev** » Thu Jan 17, 2008 10:33 pm

i have tried but i guess i was not using the right routine in a right way, so it didnt worked ...can you give me a hint to use it in the transformer

kbsuryadev · Post by **kbsuryadev** » Thu Jan 17, 2008 10:38 pm

this is how i used in trn

DateTimeStampToODBC(DSLink2.data_date)

Corp..Transformer_4.DSLink5: DSD.BCIPut call to SQLExecute failed.
SQL statement:INSERT INTO test_Cashier_Writeback(property, data_date, cashier_id, dept, category, outlet, cashier_fname, cashier_lname, Infogen_cash, priorday_cash_timing, currentday_cash_timing, tips_paidout, priorday_tip_adj, currentday_tip_adj, accountable_cash, cashdrop_amt, dueback_amt, bank_amt, adjusted_cash, cash_diff) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
SQLSTATE=S1010, DBMS.CODE=0
[DataStage][SQL Client]Function call is illegal at this point

property = "MGM Grand"
data_date = 1/7/-20-08
cashier_id = "100041193"
dept = "30312"
category = "FOODBEVG"
outlet = "Starbucks West Wing"
cashier_fname = "Jennifer W"
cashier_lname = "Wilson"
Infogen_cash = 393.81
priorday_cash_timing = 0.00
currentday_cash_timing = 0.00
tips_paidout = 8.50
priorday_tip_adj = 0.00
currentday_tip_adj = 0.00
accountable_cash = 385.31
cashdrop_amt = 385.28
dueback_amt = 0.00
bank_amt = 0.00
adjusted_cash = 385.28
cash_diff = -0.03

kbsuryadev · Post by **kbsuryadev** » Thu Jan 17, 2008 11:53 pm

DateBad2[1,4]:"-":DateBad2[5,2]:"-":DateBad2[7,2]:" 00:00:00"

OCONV(DSLink2.data_date,'D-YMD[4,2,2]')

Oconv(Iconv(%ARG1%, "DYMD[4,2,2]"), "D-YMD[4,2,2]")

i have tried all the above ways., nothing seems working, i must be doing something wrong, i am very confused .

kbsuryadev · Post by **kbsuryadev** » Fri Jan 18, 2008 12:44 am

Ray ,

Any help on how can i organize the date component.

ray.wurlod · Post by **ray.wurlod** » Fri Jan 18, 2008 5:27 am

The order of components in data_date is (I am assuming) month, day, year so the second argument to Iconv() needs to specify this.

Code: Select all

Oconv(Iconv(InLink.data_date, "DMDY"), "D-YMD[4,2,2]")

Substring fails because you can have one-digit or two-digit days and months. You need a mix of Field() and Fmt() functions if you're going to do it this way.

Code: Select all

Fmt(Field(InLink.data_date,"-",3,1),"4R") : "-" : Fmt(Field(InLink.data_date,"-",1,1),"R%2") : "-" : Fmt(Field(InLink.data_date,"-",2,1), "R%2")

kbsuryadev · Post by **kbsuryadev** » Fri Jan 18, 2008 10:14 am

Ray,

First of all i really appreciate you for time.Thank you

I have tried both ways but still the same error.i am sure you would notice the data_date output value when i used the 1st and 2nd code

Oconv(Iconv(InLink.data_date, "DMDY"), "D-YMD[4,2,2]")

Fmt(Field(InLink.data_date,"-",3,1),"4R") : "-" : Fmt(Field(InLink.data_date,"-",1,1),"R%2") : "-" : Fmt(Field(InLink.data_date,"-",2,1), "R%2")

1st Code Error:
Corp..Transformer_4.DSLink5: DSD.BCIPut call to SQLExecute failed.
SQL statement:INSERT INTO test_Cashier_Writeback(property, data_date, cashier_id, dept, category, outlet, cashier_fname, cashier_lname, Infogen_cash, priorday_cash_timing, currentday_cash_timing, tips_paidout, priorday_tip_adj, currentday_tip_adj, accountable_cash, cashdrop_amt, dueback_amt, bank_amt, adjusted_cash, cash_diff) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
SQLSTATE=S1010, DBMS.CODE=0
[DataStage][SQL Client]Function call is illegal at this point

property = "MGM Grand"
data_date = 2008-01-07
cashier_id = "235279"
dept = "37119"
category = "FOODBEVG"
outlet = "Centrifuge Bar"
cashier_fname = "Matt"
cashier_lname = "Szumada"
Infogen_cash = 877.00
priorday_cash_timing = 0.00
currentday_cash_timing = 0.00
tips_paidout = 950.46
priorday_tip_adj = 0.00
currentday_tip_adj = 0.00
accountable_cash = -73.46
cashdrop_amt = 0.00
dueback_amt = 74.00
bank_amt = 0.00
adjusted_cash = -74.00
cash_diff = -0.54

2nd Code Error:
Corp..Transformer_4.DSLink5: DSD.BCIPut call to SQLExecute failed.
SQL statement:INSERT INTO test_Cashier_Writeback(property, data_date, cashier_id, dept, category, outlet, cashier_fname, cashier_lname, Infogen_cash, priorday_cash_timing, currentday_cash_timing, tips_paidout, priorday_tip_adj, currentday_tip_adj, accountable_cash, cashdrop_amt, dueback_amt, bank_amt, adjusted_cash, cash_diff) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
SQLSTATE=S1010, DBMS.CODE=0
[DataStage][SQL Client]Function call is illegal at this point

property = "MGM Grand"
data_date = -08-00
cashier_id = "235178"
dept = "37119"
category = "FOODBEVG"
outlet = "Centrifuge Bar"
cashier_fname = "Michael V."
cashier_lname = "Villegas"
Infogen_cash = 266.00
priorday_cash_timing = 0.00
currentday_cash_timing = 0.00
tips_paidout = 76.50
priorday_tip_adj = 0.00
currentday_tip_adj = 0.00
accountable_cash = 189.50
cashdrop_amt = 189.00
dueback_amt = 0.00
bank_amt = 0.00
adjusted_cash = 189.00
cash_diff = -0.50

Is there anything you would think i am doing something wrong....I am still confused about the error message, i have tried to use a reject link to see where the data is going, there are appx 290 rows( going to rejet link)

Input format in the csv file is :1/6/2008( varchar)

kbsuryadev · Post by **kbsuryadev** » Fri Jan 18, 2008 1:31 pm

Ray

I dropped the date column , and deleted the date values from the csv file moved the data which is working fine, infact there were some issues with the csv file which i fixed then the job running fine...but when i view the data in the target table under the data_data column

0x0000000000026983
0x0000000000026984
0x0000000000026985
0x0000000000026986
0x0000000000026987
0x0000000000026988
0x0000000000026989
0x000000000002698A
0x000000000002698B
0x000000000002698C
0x000000000002698D
0x000000000002698E
0x000000000002698F
0x0000000000026990
0x0000000000026991
0x0000000000026992
0x0000000000026993
0x0000000000026994
0x0000000000026995
0x0000000000026996
0x0000000000026997
0x0000000000026998
0x0000000000026999
0x000000000002699A
0x000000000002699B
0x000000000002699C
0x000000000002699D
0x000000000002699E
0x000000000002699F
0x00000000000269A0
0x00000000000269A1
0x00000000000269A2
0x00000000000269A3
0x00000000000269A4
0x00000000000269A5
0x00000000000269A6
0x00000000000269A7
0x00000000000269A8
0x00000000000269A9
0x00000000000269AA
0x00000000000269AB
0x00000000000269AC
0x00000000000269AD
0x00000000000269AE
0x00000000000269AF
0x00000000000269B0
0x00000000000269B1
0x00000000000269B2
0x00000000000269B3
0x00000000000269B4
0x00000000000269B5
0x00000000000269B6
0x00000000000269B7
0x00000000000269B8
0x00000000000269B9
0x00000000000269BA
0x00000000000269BB
0x00000000000269BC
0x00000000000269BD
0x00000000000269BE
0x00000000000269BF
0x00000000000269C0
0x00000000000269C1
0x00000000000269C2
0x00000000000269C3
0x00000000000269C4
0x00000000000269C5
0x00000000000269C6
0x00000000000269C7
0x00000000000269C8
0x00000000000269C9
0x00000000000269CA
0x00000000000269CB
0x00000000000269CC
0x00000000000269CD
0x00000000000269CE
0x00000000000269CF
0x00000000000269D0
0x00000000000269D1
0x00000000000269D2
0x00000000000269D3
0x00000000000269D4
0x00000000000269D5
0x00000000000269D6
0x00000000000269D7
0x00000000000269D8
0x00000000000269D9
0x00000000000269DA
0x00000000000269DB
0x00000000000269DC
0x00000000000269DD
0x00000000000269DE
0x00000000000269DF
0x00000000000269E0
0x00000000000269E1
0x00000000000269E2
0x00000000000269E3
0x00000000000269E4
0x00000000000269E5
0x00000000000269E6
0x00000000000269E7
0x00000000000269E8
0x00000000000269E9
0x00000000000269EA
0x00000000000269EB
0x00000000000269EC
0x00000000000269ED
0x00000000000269EE
0x00000000000269EF
0x00000000000269F0
0x00000000000269F1
0x00000000000269F2
0x00000000000269F3
0x00000000000269F4
0x00000000000269F5
0x00000000000269F6
0x00000000000269F7
0x00000000000269F8
0x00000000000269F9
0x00000000000269FA
0x00000000000269FB
0x00000000000269FC
0x00000000000269FD
0x00000000000269FE
0x00000000000269FF
0x0000000000026A01
0x0000000000026A02
0x0000000000026A03
0x0000000000026A04
0x0000000000026A05
0x0000000000026A06
0x0000000000026A07
0x0000000000026A08
0x0000000000026A09
0x0000000000026A0A
0x0000000000026A0B
0x0000000000026A0C
0x0000000000026A0D
0x0000000000026A0E
0x0000000000026A0F
0x0000000000026A10
0x0000000000026A11
0x0000000000026A12
0x0000000000026A13
0x0000000000026A14
0x0000000000026A15
0x0000000000026A16
0x0000000000026A17
0x0000000000026A18
0x0000000000026A19
0x0000000000026A1A
0x0000000000026A1B
0x0000000000026A1C
0x0000000000026A1D
0x0000000000026A1E
0x0000000000026A1F
0x0000000000026A20
0x0000000000026A21
0x000000000002695B
0x000000000002695C
0x000000000002695D
0x000000000002695E
0x000000000002695F
0x0000000000026960
0x0000000000026961
0x0000000000026962
0x0000000000026963
0x0000000000026964
0x0000000000026965
0x0000000000026966
0x0000000000026967
0x0000000000026968
0x0000000000026969
0x000000000002696A
0x000000000002696B
0x000000000002696C
0x000000000002696D
0x000000000002696E
0x000000000002696F
0x0000000000026970
0x0000000000026971
0x0000000000026972
0x0000000000026973
0x0000000000026974
0x0000000000026975
0x0000000000026976
0x0000000000026977
0x0000000000026978
0x0000000000026979
0x000000000002697A
0x000000000002697B
0x000000000002697C
0x000000000002697D
0x000000000002697E
0x000000000002697F
0x0000000000026980
0x0000000000026981
0x0000000000026982
0x0000000000026A22
0x0000000000026A23
0x0000000000026A24
0x0000000000026A25
0x0000000000026A26
0x0000000000026A27
0x0000000000026A28
0x0000000000026A29
0x0000000000026A2A
0x0000000000026A2B
0x0000000000026A2C
0x0000000000026A2D
0x0000000000026A2E
0x0000000000026A2F
0x0000000000026A30
0x0000000000026A31
0x0000000000026A32
0x0000000000026A33
0x0000000000026A34
0x0000000000026A35
0x0000000000026A36
0x0000000000026A37
0x0000000000026A38
0x0000000000026A39
0x0000000000026A3A
0x0000000000026A3B
0x0000000000026A3C
0x0000000000026A3D
0x0000000000026A3E
0x0000000000026A3F
0x0000000000026A40
0x0000000000026A41
0x0000000000026A42
0x0000000000026A43
0x0000000000026A44
0x0000000000026A45
0x0000000000026A46
0x0000000000026A47
0x0000000000026A48
0x0000000000026A49
0x0000000000026A4A
0x0000000000026A4B
0x0000000000026A4C
0x0000000000026A4D
0x0000000000026A4E
0x0000000000026A4F
0x0000000000026A50
0x0000000000026A51
0x0000000000026A52
0x0000000000026A53
0x0000000000026A54
0x0000000000026A55
0x0000000000026A56
0x0000000000026A57
0x0000000000026A58
0x0000000000026A59
0x0000000000026A5A
0x0000000000026A5B
0x0000000000026A5C
0x0000000000026A5D
0x0000000000026A5E
0x0000000000026A5F
0x0000000000026A60
0x0000000000026A61
0x0000000000026A62
0x0000000000026A63
0x0000000000026A64
0x0000000000026A65
0x0000000000026A66
0x0000000000026A67
0x0000000000026A68
0x0000000000026A69
0x0000000000026A6A
0x0000000000026A6B
0x0000000000026A6C
0x0000000000026A6D
0x0000000000026A6E
0x0000000000026A6F
0x0000000000026A70
0x0000000000026A71
0x0000000000026A72
0x0000000000026A73
0x0000000000026A74
0x0000000000026A75
0x0000000000026A76
0x0000000000026A77
0x0000000000026A78
0x0000000000026A79

I have no idea why this value is being shown in the target .

Now i am working on a test job of moving date to a sql server table.

thank you

ray.wurlod · Post by **ray.wurlod** » Fri Jan 18, 2008 2:51 pm

Try putting the time component onto the timestamp.

Code: Select all

Oconv(Iconv(InLink.data_date, "DMDY"), "D-YMD[4,2,2]") : " 00:00:00"

At least one of your input dates is wrong, because it's generating "-08-00" from the expression. You would do well to validate data_date before trying to manipulate it. Search the forum; an IsValid() function has been posted in the past.