Complex Oracle Query with Bind Variables

[Bonediggler1]

Hello-

I would like to set up a package that will run a complex query out of Oracle that contains bind variables. The query also contains multiple joins, sub queries, etc.

I can program the variables with the Script Task, but am stumbling on 1) where to put the query string (too long for sql command box) and 2) if this works, can SSIS even run complex queries out of Oracle?

I do not have any development rights on the DB, so I cannot get this into a stored proc.

Thanks in advance for the help!

 

[MDXer]

If you utilize an OLE DB connection SSIS can handle any oracle query that you can execute. Store yuor query as a variable and then in your oledb connection use the variable as your query source.

 

[Bonediggler1]

Thank you MDXer -

2 issues here:

1) The query is over the 4000 character (or whatever) limit
2) How to use SSIS variables for the bind variables in the greater query string variable (i.e. embedded variables)?

 

[MDXer]

If your query is that large and complex have you considered breaking it into smaller queries and or pushing off some elements of the query to SSIS?

I can't imagine SSIS not being able to replicate some of the functionality of your query.