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,
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.