October 19, 2006 at 1:07 pm
Hello,
I have a DTS package where the data source is a db2 database table. The select statement against that db2 table is as follows;
SELECT
FM_RK_TRN_DTE,
FM_TR_TRN_TIME,
FMDL_BILLING_TYPE,
FMDL_PRODUCT_GROUP,
FM_RK_CNTL_NBR,
FM_CUST_ACCOUNT_CD,
FM_TR_SC_INVC_NBR,
FM_ACCOUNT_NAME,
FM_CUST_CUST_ID,
FM_DT_CMCHK_CRDNBR,
FM_SC_SRVC_CNTR_CD,
FM_SC_NAME,
FM_SC_CHAIN_CD,
FM_TR_TRACTR_GAL,
FM_TR_TRACTR_PPG,
FM_TR_TRACTR_COST,
FM_TR_TRLR_GAL,
FM_TR_TRLR_PPG,
FM_TR_TRLR_COST,
FM_TR_OTHER_GAL,
FM_TR_OTHER_PPG,
FM_TR_OTHER_COST,
FM_TF_OTHER_FUEL,
FM_TF_CASH,
FM_TF_OIL,
FM_TF_PROD_1,
FM_TF_PROD_2,
FM_TF_PROD_3,
FM_TR_OIL_COST,
FM_TR_PROD_CD_1,
FM_TR_PROD_AMT_1,
FM_TR_PROD_CD_2,
FM_TR_PROD_AMT_2,
FM_TR_PROD_CD_3,
FM_TR_PROD_AMT_3,
FM_RD_FUEL_RT,
FM_RD_CASH_ADVC_RT,
FM_RD_SC_TRN_FEE,
FM_BR_SRVC_CNTRFEE,
FM_SC_RD_SC_SLT_DS,
FMDL_COMDATA_FEES,
FMDL_DISCOUNT_AMT,
FM_CDN_CMPLT_TY,
FM_DT_EXP_CASH_FG,
FM_BR_TRN_FG,
FM_PAY_AT_LOAD_FG,
FM_TEXACO_CRXTN_FL,
FM_CO_CR_FL,
FMDL_REBATE_AMT,
FMDL_FLEET_CRD_XTN,
FMDL_FLEET_MCC,
FM_C_TXC_COBRND_FL,
FM_SC_CR_FL,
FM_SC_TEXACO_FL,
P105S1_FILE_TY,
P105S2_FILE_R_DT,
P105S3_PROC_ID,
P105S4_FILE_SEQ_NO,
P501S3_OCC_IND,
P501S4_MSG_NO,
FM_SC_CORPORT_CD,
FM_SC_RD_FUEL_RT,
FM_BC_RD_CSHADVCRT,
FM_PRFD_LVL_FG,
FM_RD_COMP_HND_CHG,
FM_CUST_CORPORT_CD,
FM_IN_NTW_FG,
FM_SC_STATE,
FMdl_TOTAL_GALLONS,
FM_BC_RD_FUEL_RT,
FM_SC_RD_CSHADVCRT,
FM_CRCY_FG,
FM_TR_CASH_ADVCAMT,
FM_BILLING_FG,
FM_TF_TRACTR_FUEL,
FM_TF_TRLR_FUEL,
FM_CUST_NAME,
FM_BC_RD_SC_SLT_DS,
FM_CSTPLS_USED,
FM_SLCT_FCS_DISC,
FM_CUST_ALLIANC_FG
FROM
CBDBOW.FUEL_MGLOG
WHERE
FM_RK_TRN_DTE = (current date) - 1 day
The fields from the db2 table map field for field with the sql server destination table. The DTS package is fairly simple; it pulls the data from the db2 source, maps the fields to the sql server table, and inserts the data into the sql server table. There is an ODBC connection to the db2 database, one tranformation, then a connection to the sql server database.
The only information that I have is that the DTS package failed, and that the following error message was generated (from where I don't know):
October 20, 2006 at 7:43 am
I am not sure if you mention this in the connextion to sql server in DTS package.
But you could do either 1 of this.
1.set the arithabort on at database level
ALTER DATABASE MyDatabase
SET ARITHABORT ON
2. or add a executesql task before the datatransfer using the sql server connection which contains SET ARITHABORT ON statement
October 20, 2006 at 7:44 am
Hmmm. Looks like the good old FM87 format file.
Try using an ExecuteSQL task prior to running the data pump task. In that ExecuteSQL task, put the appropriate SET statement, and make sure it is run on the connection to the SQL database.
Let me know if that works.
jg
October 20, 2006 at 8:08 am
I was aware that I could set ARITHABORT ON at the database level, but if I understand correctly ,If ARITHBORT is off, a value of NULL will be inserted where NULL values are allowed and an arithmetic error occurs. I didn't want to take the chance that I might 'break' something by turning ARITHABORT on at the database level.
Dumb question: If I set ARITHABORT on for the connection, should I turn it back off after the data pump? I'm guessing that I would not have to do that because once the connection is closed. If the database setting for this is Off, there would not be a problem.
Thanks again for your help.
CSDunn
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply