1

I have a Teradata SQL query that has over 20 separate SELECT queries all separated by UNION ALL. This query runs fine in SQL Assistant. I am wanting to set up an automated SSIS pull from Teradata using this SQL. I am using an ADO.NET task in my Data Flow area with "SQL" as the Data access mode. When I input my SQL in the box, I get this error:

TITLE: Microsoft Visual Studio

Error at Data Flow Task [TIRKS EDW [1]]: System.Data.Odbc.OdbcException: ERROR [42000] [Teradata][ODBC Teradata Driver][Teradata Database] Syntax error, expected something like an 'END' keyword between ')' and the word 'en'. 
   at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode)
   at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader, Object[] methodArguments, SQL_API odbcApiMethod)
   at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader)
   at System.Data.Odbc.OdbcCommand.ExecuteReader(CommandBehavior behavior)
   at System.Data.Odbc.OdbcCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
   at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.ReinitializeMetaData()
   at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostReinitializeMetaData(IDTSManagedComponentWrapper100 wrapper)

ADDITIONAL INFORMATION:

ERROR [42000] [Teradata][ODBC Teradata Driver][Teradata Database] Syntax error, expected something like an 'END' keyword between ')' and the word 'en'.  (TDATA32.DLL)

BUTTONS:

OK

However, if I reduce my SQL to only two Selects with one UNION ALL separator, it will run fine. Again, my SQL with over 20 SELECT/UNION ALL will run fine outside of SSIS. So I suspect it is something within SSIS. Any ideas?

dnoeth
  • 59,503
  • 4
  • 39
  • 56
Matthew
  • 11
  • 1
  • What if you write a quick and dirty little .NET program that uses the same driver and runs your query. This will help identify whether it's an SSIS problem or an ODBC driver issue. Based on the error, it sure seems like it's a driver or a query issue. – billinkc Jan 06 '15 at 01:02
  • What if the source query has 3 selects and a union all? Does that work? How about 10? When does it break? It could be an issue with the length of the query... – billinkc Jan 06 '15 at 01:03
  • 1
    billinkc in probably correct, seems like the string is truncated by SSIS before it's transmitted to Teradata. – dnoeth Jan 06 '15 at 12:00
  • Can you set up the query as a view in Terrradata? – HLGEM Jan 06 '15 at 16:04

1 Answers1

0

It may be a problem with de query length. I had some similar issue when running statments with more than 32000 characters (or some around that) through JDBC.

Try counting your length in a text editor and take as muchs SELECTs to cover around 30k character and test it.

Actually, I never spent the resolve the issue. But you may achieve this specific situation using tricks to reduce your query length (ex: using SEL instead of SELECT).

Cheers

Netto
  • 284
  • 2
  • 10