attunity driver and TPT Export error encountered during initiate phase

Database
Enthusiast

attunity driver and TPT Export error encountered during initiate phase

I am using SSIS and using Teradata attunity driver as source in one of the packages to get some data from Teradata database. The Parse query is fine, preview of data is fine. I can run the same query cleanly on Teradata SQL Assistant.

However, when I copy the package to sql server and run it it throws the error. 

Error: 2014-05-08 20:11:29.74     Code: 0x00000328     Source: CF and CF GT 30 Teradata Source [127]     Description: TPT Export error encountered during Initiate phase. Syntax error, expected something like an 'IN' keyword or a 'CONTAINS' keyword between ')' and '|'.  End Error  Error: 2014-05-08 20:11:30.03     Code: 0xC0047038    

The query string all in one line - due to restrictions - by attunity or teradata is below.

What is the best way to identify the problem? The query is good as per SSIS - however it is probably checking to ODBC standard and not teradata sql TPT syntax. Is there a command line tool or other that will identify where the problem is. There were 15 similar errors like this before I could get it to this shape. 

Some of the restrictions are like no line-feed character, no substring ( string, n, m) but substring ( string from n for m) and so on. Is there some documentation when using Attnity driver as to what is acceptable syntax ?

Thank you

SELECT WCCLLI,DATESTAMP,SUM(POTS) AS POTS_CFs,SUM(DSL) as DSL_CFs,SUM(POTSGT30) as POTS_CF_GT30,  DUE_DT, CMP_DT, SO_NUM FROM (   SELECT NPA.WCCLLI,VZVL021.DATA_YEAR_MONTH_DT as DATESTAMP,   VZVL021.ACCT_NPA_CD||'/'||VZVL021.ACCT_NXX_CD||'/'||VZVL021.ACCT_LINE_NBR AS TN,   CAST(CAST((CAST(VZVL021.SOC_DUE_DATE AS CHAR(2)) + 2000) AS INTEGER) || '/' || SUBSTRING(VZVL021.SOC_DUE_DATE FROM 3 FOR 2 )|| '/' ||  SUBSTRING(VZVL021.SOC_DUE_DATE FROM 5 FOR 2)  AS DATE) DUE_DT,   CAST(CAST((CAST(VZVL021.SOC_COMP_DATE AS CHAR(2)) + 2000) AS INTEGER) || '/' || SUBSTRING(VZVL021.SOC_COMP_DATE FROM 3 FOR 2)||'/'||  SUBSTRING(VZVL021.SOC_COMP_DATE FROM 5 FOR 2)  AS DATE) CMP_DT,  CAST(CAST((CAST(VZVL021.SOC_COMP_DATE AS CHAR(2)) + 2000) AS INTEGER) || '/' || SUBSTRING(VZVL021.SOC_COMP_DATE FROM 3 FOR 2)||'/'||  SUBSTRING(VZVL021.SOC_COMP_DATE FROM 5 FOR 2)  AS DATE)  -   CAST(CAST((CAST(VZVL021.SOC_DUE_DATE AS CHAR(2)) + 2000) AS INTEGER)||'/'||SUBSTRING(VZVL021.SOC_DUE_DATE FROM 3 FOR 2)||'/'||  SUBSTRING(VZVL021.SOC_DUE_DATE FROM 5 FOR 2)  AS DATE)  AS DIFF,  VZVL021.SOC_S_O_NUM SO_NUM,VZVL021.SOC_MISSED_APPT_CODE,SOC_2ND_MAC,  VZVL021.SOC_SUBS_MAC_1,VZVL021.SOC_SUBS_MAC_2,VZVL021.SOC_SUBS_MAC_3,  CASE WHEN (VZVL021.SOC_I_ADSL_IND = 'Y' OR SUBSTRING(VZVL021.SOC_NEW_BCS_USOC FROM 1 FOR 3) = 'SAF') THEN 1 ELSE 0 END AS DSL,  CASE WHEN (VZVL021.SOC_I_ADSL_IND = 'Y' OR SUBSTRING(VZVL021.SOC_NEW_BCS_USOC FROM 1 FOR 3) = 'SAF') THEN 0 ELSE 1 END AS POTS,  CASE WHEN  CAST(CAST((CAST(VZVL021.SOC_COMP_DATE AS CHAR(2)) + 2000) AS INTEGER)||'/'||SUBSTRING(VZVL021.SOC_COMP_DATE FROM 3 FOR 2)||'/'||  SUBSTRING(VZVL021.SOC_COMP_DATE FROM 5 FOR 2)  AS DATE)  -   CAST(CAST((CAST(VZVL021.SOC_DUE_DATE AS CHAR(2)) + 2000) AS INTEGER)||'/'||SUBSTRING(VZVL021.SOC_DUE_DATE FROM 3 FOR 2)||'/'||  SUBSTRING(VZVL021.SOC_DUE_DATE FROM 5 FOR 2)  AS DATE)  > 30  AND VZVL021.SOC_I_ADSL_IND <> 'Y'  AND SUBSTRING(VZVL021.SOC_NEW_BCS_USOC FROM 1 FOR 3) <> 'SAF'  THEN 1 ELSE 0 END AS POTSGT30  FROM CORP_FINANCE_ACCESS_VIEWS_R2.VZVL021_H_BLS_CRIS_SOC_ACTVTY VZVL021  JOIN CORP_FINANCE_ACCESS_VIEWS_R2.VZVL023_H_BLS_CRIS_SOS_ACTVTY VZVL023  ON  VZVL021.SOC_SER_NUM = VZVL023.SOS_SER_NUM AND VZVL021.SOC_SITE_CODE = VZVL023.SOS_SITE_CODE  LEFT JOIN  (   SELECT NPA,NXX,WCCLLI,CNT,  ROW_NUMBER() OVER (PARTITION BY NPA,NXX ORDER BY CNT DESC) SEQ  FROM  (  SELECT NPA,NXX,SWITCH_CLLI_8 as WCCLLI,COUNT(*) CNT  FROM EDW_PUBLIC_VIEWS.VCAN003_NPA_NXX_ALL  WHERE OFFICE_TYPE = 'EOC'   GROUP BY 1,2,3  ) a  ) NPA  ON SUBSTRING(VZVL023.SOS_SI_DATA FROM 1 FOR 3)  =  NPA.NPA AND SUBSTRING(VZVL023.SOS_SI_DATA FROM 4 FOR 3) = NPA.NXX AND NPA.SEQ = 1  WHERE VZVL021.DATA_YEAR_MONTH_DT >= 1130101  AND VZVL021.SOC_FACS_IND = '1' AND SUBSTRING(VZVL021.SOC_S_O_NUM FROM 1 FOR 1) IN ('N','T','C')   AND SUBSTRING(VZVL023.SOS_ROUTE_CODE FROM 3 FOR 1)  != 'D'     AND  VZVL021.SOC_NEW_BCS_USOC != 'UEANL'   AND SUBSTRING(VZVL021.SOC_NEW_BCS_USOC FROM 1 FOR 3) NOT IN ('UEE','UEF','UEQ')   AND (VZVL021.SOC_MISSED_APPT_CODE = 'CF' OR SOC_2ND_MAC = 'CF'  OR VZVL021.SOC_SUBS_MAC_1 = 'CF'  OR VZVL021.SOC_SUBS_MAC_2 = 'CF' OR VZVL021.SOC_SUBS_MAC_3 = 'CF' )  ) a  GROUP BY 1,2, 6, 7, 8   ORDER BY 1,2

Tags (1)
10 REPLIES
Enthusiast

Re: attunity driver and TPT Export error encountered during initiate phase

I did not come up with that SQL. All I am trying to do is to change the datasource from ODBC DSN to Attunity driver for perceived and claimed efficiency of the TPT driver over odbc dsn. Thanks.

Enthusiast

Re: attunity driver and TPT Export error encountered during initiate phase

I suspected the concatenation operator had anything to do with it and removed all concatenation operators. I realized those fields that were getting contanetated could be transformed using a derived column transformation on SSIS. Now there is not a single "|" in my query. However, I get the exact same error. 

Does the TPT rewrite some of the other expressions I have using | ? Or is it caching any of my old SSIS package somewhere? 

I closed the package a few times and reopened. I have also refreshed the jobs and verified that SSIS package does get copied to the correct location. I verified the package path and reselected the package path and the package again. Refreshed the configuration files. 

Strange that I get the same error with not a single || or | character in the resulting query.

SELECT WCCLLI,DATESTAMP ,  DUE_DT, CMP_DT, SO_NUM ,SUM(POTS) AS POTS_CFs,SUM(DSL) as DSL_CFs,SUM(POTSGT30) as POTS_CF_GT30 FROM (   SELECT NPA.WCCLLI,VZVL021.DATA_YEAR_MONTH_DT as DATESTAMP,     VZVL021.SOC_DUE_DATE   DUE_DT,   SOC_COMP_DATE CMP_DT,    VZVL021.SOC_S_O_NUM  SO_NUM,VZVL021.SOC_MISSED_APPT_CODE,SOC_2ND_MAC,  VZVL021.SOC_SUBS_MAC_1,VZVL021.SOC_SUBS_MAC_2,VZVL021.SOC_SUBS_MAC_3, CASE WHEN (VZVL021.SOC_I_ADSL_IND = 'Y' OR SUBSTRING(VZVL021.SOC_NEW_BCS_USOC FROM 1 FOR 3) = 'SAF') THEN 1 ELSE 0 END AS DSL,  CASE WHEN (VZVL021.SOC_I_ADSL_IND = 'Y' OR SUBSTRING(VZVL021.SOC_NEW_BCS_USOC FROM 1 FOR 3) = 'SAF') THEN 0 ELSE 1 END AS POTS,  CASE WHEN  (VZVL021.SOC_COMP_DATE - VZVL021.SOC_DUE_DATE)*30/100 > 30  AND VZVL021.SOC_I_ADSL_IND <> 'Y'  AND SUBSTRING(VZVL021.SOC_NEW_BCS_USOC FROM 1 FOR 3) <> 'SAF'  THEN 1 ELSE 0 END AS POTSGT30  FROM CORP_FINANCE_ACCESS_VIEWS_R2.VZVL021_H_BLS_CRIS_SOC_ACTVTY VZVL021  JOIN CORP_FINANCE_ACCESS_VIEWS_R2.VZVL023_H_BLS_CRIS_SOS_ACTVTY VZVL023  ON  VZVL021.SOC_SER_NUM = VZVL023.SOS_SER_NUM AND VZVL021.SOC_SITE_CODE = VZVL023.SOS_SITE_CODE  LEFT JOIN  (   SELECT NPA,NXX,WCCLLI,CNT,  ROW_NUMBER() OVER (PARTITION BY NPA,NXX ORDER BY CNT DESC) SEQ  FROM  (  SELECT NPA,NXX,SWITCH_CLLI_8 as WCCLLI,COUNT(*) CNT  FROM EDW_PUBLIC_VIEWS.VCAN003_NPA_NXX_ALL  WHERE OFFICE_TYPE = 'EOC'   GROUP BY 1,2,3  ) a  ) NPA  ON SUBSTRING(VZVL023.SOS_SI_DATA FROM 1 FOR 3)  =  NPA.NPA AND SUBSTRING(VZVL023.SOS_SI_DATA FROM 4 FOR 3) = NPA.NXX AND NPA.SEQ = 1  WHERE VZVL021.DATA_YEAR_MONTH_DT >= 1130101  AND VZVL021.SOC_FACS_IND = '1' AND SUBSTRING(VZVL021.SOC_S_O_NUM FROM 1 FOR 1) IN ('N','T','C')   AND SUBSTRING(VZVL023.SOS_ROUTE_CODE FROM 3 FOR 1)  != 'D'     AND  VZVL021.SOC_NEW_BCS_USOC != 'UEANL'   AND SUBSTRING(VZVL021.SOC_NEW_BCS_USOC FROM 1 FOR 3) NOT IN ('UEE','UEF','UEQ')   AND (VZVL021.SOC_MISSED_APPT_CODE = 'CF' OR SOC_2ND_MAC = 'CF'  OR VZVL021.SOC_SUBS_MAC_1 = 'CF'  OR VZVL021.SOC_SUBS_MAC_2 = 'CF' OR VZVL021.SOC_SUBS_MAC_3 = 'CF' )  ) a  GROUP BY 1,2, 3,4,5   ORDER BY 1,2

Enthusiast

Re: attunity driver and TPT Export error encountered during initiate phase

Source: CF and CF GT 30 Teradata 12 Source 13 [174]    

Description: TPT Export error encountered during Initiate phase.

Syntax error, expected something like an 'IN' keyword or a 'CONTAINS' keyword between ')' and '|'.

Enthusiast

Re: attunity driver and TPT Export error encountered during initiate phase

I changed the SSIS source name just to make sure any change I make there does get to the package and the error reflects the correct name. It does. 

Experts, Please let me know if this is the correct forum or I should have posted this on a different forum. Thanks

Junior Contributor

Re: attunity driver and TPT Export error encountered during initiate phase

There's no comparison operator != in Teradata/Standard SQL, but depending on some ODBC settings the driver automatically replaced it with valid syntax: <>

Connecting through CLI (like TPT) or .NET will not change your query and thus fail.

Enthusiast

Re: attunity driver and TPT Export error encountered during initiate phase

Dieter,

Thanks a lot for the response. That is great input. I tried that and the message is different. That helps me a lot.

Is there a TPT manual on what is allowed. For example instead of substr(orig_string, 3, 4) I found out it should be substring(orig_string from 3 for 4). Is there a list of such equivalents for TPT.



Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.  End Error  DTExec: The package execution returned DTSER_FAILURE (1).  Started:  3:02:13 PM  Finished: 3:07:01 PM  Elapsed:  287.915 seconds.  The package execution failed.

Enthusiast

Re: attunity driver and TPT Export error encountered during initiate phase

Dieter,

I am confused about one snippet from that query.

WHERE VZVL021.DATA_YEAR_MONTH_DT >= 1130101  AND

I am not sure what the author meant when he wrote that and what is really being compared there. to DATA_YEAR_MONTH_DT? Please let me know, if you do. 

I want to change that to something more sensible. I doubt whoever wrote this was doing for TPT either, since it was originally for Teradata DSN. I am using the attunity driver hoping to improve performance. So, that must have been for plain teradata. It does preview well - so that must be valid date for teradata.

Enthusiast

Re: attunity driver and TPT Export error encountered during initiate phase

Dieter,

Another difficult aspect work with Teradata thru/with Attunity driver is the absence of a cause-error relationship. I only see the error and make modifications based on that not knowing what the actual cause. The original error about something like an IN or contains clause missing between ) and | has no relationship to the actual reason that != is not supported. 

Is there a tool/GUI or some way to get the sql validated/parsed for TPI/CLI?

Thank you

Junior Contributor

Re: attunity driver and TPT Export error encountered during initiate phase

Both substr(orig_string, 3, 4) and substring(orig_string from 3 for 4) are valid TD SQL, the latter is the official Standard SQL version, just SQL Server's substring(orig_string, 3, 4) is invalid.

WHERE VZVL021.DATA_YEAR_MONTH_DT >= 1130101 is a comparison to the date 2013-01-01 using a very old and deprecated syntax based on the internal storage of a date:

(year-1900) * 10000 + (month*100) + day = (2013-1900) * 10000 + (1*100) + 1

1130101 -> 2013-01-01
113 -> 2013
01 -> 01
01 -> 01

Any tool capable to connect and submit SQL to your TD system can be used to validate it. But if it's using ODBC be shure to check the Disable Parsing option (equal to NoScan=Yes in the ODBC.ini file).

The error message was as good as possible, "expected something like an 'IN' keyword or a 'CONTAINS' keyword between ')' and '|'", because the exclamation mark '!' can be used instead of the vertical bar '|' and thus the parser got stuck not expecting '|='