OLEDB Source Syntax Error reported is invalid(AS400)

Question

OLEDB Source Syntax Error reported is invalid(AS400)

Brian Carlson

SSCarpal Tunnel

Points: 4170
More actions
October 18, 2011 at 7:31 am

#250296

I have a query that I have executed successfully on an AS400. When I copy & paste the query into and OLEDB data source in SSIS, it reports that there is a syntax error. I've tried setting both DelayValidation=True and ValidateExternalMetadata=False but still get the error. How do I get OLEDB to just send the query to the AS400?
Provider=Native OLE DB\IBM DB2 for i5/OS IBMDASQL OLE DB Provider
(select column_list from table) -- This part works fine without the where clause
WHERE -- This works on the AS400 but not through the OLEDB Source
int(substr(rtrim(char( MAX(auctdu, 20012345))), char_length(rtrim(char( MAX(auctdu,20012345)))) - 7, 8)) IN (20111231, 20121231)
Thanks,
Brian.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

kramaswamy SSCoach Points: 18181 More actions · Answer 1

Might not be the most elegant solution, but I've found I've been able to get it to work by using an ODBC driver instead.

Brian Carlson SSCarpal Tunnel Points: 4170 More actions · Answer 2

Thanks for the suggestion. Unfortunately, I don't have a DB2 ODBC driver on that machine and it's not likely I'll be able to get one.

kramaswamy SSCoach Points: 18181 More actions · Answer 3

Could you, instead of using a query, maybe try putting the query into a stored procedure then executing that procedure?

Brian Carlson SSCarpal Tunnel Points: 4170 More actions · Answer 4

I only have select permission on the AS400 so I can't create a stored procedure. It's an old system and the original developers are long gone. The current people maintaining the system won't make any changes. The system should be replaced in 1-2 years...

Thanks.

kramaswamy SSCoach Points: 18181 More actions · Answer 5

alright then, as a final suggestion from me, why not just get all the data and then do the WHERE clause as conditional transformations in your SSIS package?

Brian Carlson SSCarpal Tunnel Points: 4170 More actions · Answer 6

35,000 rows vs 14,000,000. The funky column names are malconfigured time stamps. I can do this through an OPENQUERY statement inside SQL Server. Once I do that though, there's no point in using SSIS. I do wonder why it doesn't work in SSIS though because the OPENQUERY through a linked server uses the same driver on the same machine.

Thanks,

Brian.