0

I have an oracle SP which inserts data in the table.

When I am trying to run the oracle SP with no in and out parameter from SSIS execute SQL task it is running successfully but not populating any data in the table.

But When I am running the same SP in PL/SQL it is working absolutely fine and data is also populating in the table.

I have no clue what is going wrong

Hadi
  • 36,233
  • 13
  • 65
  • 124
Red Devil
  • 2,343
  • 2
  • 21
  • 41
  • Did you COMMIT? – Littlefoot Jan 15 '18 at 07:04
  • I have this in my execute sql task begin test; end; – Red Devil Jan 15 '18 at 07:06
  • So, try with "BEGIN TEST; **COMMIT;** END; – Littlefoot Jan 15 '18 at 07:13
  • still not working – Red Devil Jan 15 '18 at 07:20
  • I don't use SSIS so I'll have to ask: as SP works correctly when ran in Oracle, then I presume that there's something going on when called from SSIS. You said that it runs "successfully", i.e. no errors. Do you, by any chance, have some "error handler" in SSIS which does something like "WHEN OTHERS THEN NULL" (a quite stupid thing) in Oracle? You know - maybe it fails, but *something* prevents you from seeing an error. – Littlefoot Jan 15 '18 at 07:38
  • Why do you mention "no in and out parameter from SSIS" - does the proc take parameters? – Nick.Mc Jan 15 '18 at 08:30
  • @Nick.McDermaid I am not having any parameters in my SP – Red Devil Jan 15 '18 at 08:36
  • Sounds like there's no commit but normally that would be obvious because the table would be locked. Perhaps use this method to identify exactly what was submitted: https://stackoverflow.com/questions/14830875/find-out-the-history-of-sql-queries – Nick.Mc Jan 15 '18 at 08:39
  • DId you check if the user, which is executing the SSIS package, is the same as the one you use in PL/SQL? Might be some grants missing or something... – Tyron78 Jan 16 '18 at 14:35
  • I checked and both the user is same. – Red Devil Jan 17 '18 at 07:03

0 Answers0