SSIS - Multiple statements in Oracle OLE DB Source

Question

Is it allowed to use more than one SQL Command inside an OLE DB Source Task?

I have to call an Stored procedure (that returns nothing) before to query the source table (oracle database) for data security reasons. Is it possible to use both commands inside the OLE DB Source Task? the query looks like :

Call A

GO

Select * from XXX

and I am getting an error. I have also tried with ";" after the first statement but it didn't help.

Does the OLE DB source component allow to do this?

Thank you in advance,

billinkc · Accepted Answer · 2020-10-09 14:08:13Z

1

No but you can still get this done. The OLE DB Source component needs to be able to determine the metadata of the source data looks like. Adding precursor, non-metadata generating steps can throw that off.

Add an Execute SQL Task and call it something like "Prime data source" or whatever procedure "A" does. Here you can add in all the precursor steps with no concerns about metadata shape.

Drag the Green arrow (success) from the SQL Task to the Data Flow Task.

answered Oct 9, 2020 at 14:08

billinkc

61.4k10 gold badges110 silver badges169 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Cristina Over a year ago

Thanks @billinkc , actually that's how I have it implemented and I guess that is the correct way..., but I was looking for a way to have both statements in the same query to try to fix this other problem : stackoverflow.com/questions/64229762/…

Collectives™ on Stack Overflow

SSIS - Multiple statements in Oracle OLE DB Source

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related