none
Execute SQL task for on Oracle - multiple commands in a single task RRS feed

  • Question

  •  

    Hi

     

    I have a simple SSIS package that is connecting to an Oracle database.  I want to run a script on the database that contains multiple truncates, followed by multiple inserts.  The script runs fine from within oracle, but when i try and run it from the Execute SQL Task in SSIS it errors.  It will only allow me to run 1 command at a time (so its not a connection issue).  Is there anyway for it to run multiple? 

     

    I have reduced the script to just have 2 single truncate table commands and the error returned is (from oracle) "Invalid character", which i'm pretty sure refers to the semi colon i have between the two commands. Removing the semi colon gives different errors.  I have tried separating then with a commit etc.  Cant get the 2 commands to work in one task. 

     

    Help would be much appreciated!

     

    Guy

    Monday, November 3, 2008 6:57 PM

Answers

  • Cheers - turns out you just need to separate each command with "GO", not a semicolon or commit or anything else.  I'm unfamiliar with Oracle SQL but i dont think "GO" is a valid term there, so quite surprised that this was what did it.

     

    Guy

     

    Tuesday, November 4, 2008 9:38 AM

All replies

  • I don't normally try to do this since it makes it harder to understand the flow of the package when I come back to it some time later, but it still should be possible.

    Reference the section "Sending multiple statements in a batch":

    http://msdn.microsoft.com/en-us/library/ms141003.aspx

     

    Let me know if this sheds any light on the issue.

     

    Monday, November 3, 2008 9:57 PM
  • Cheers - turns out you just need to separate each command with "GO", not a semicolon or commit or anything else.  I'm unfamiliar with Oracle SQL but i dont think "GO" is a valid term there, so quite surprised that this was what did it.

     

    Guy

     

    Tuesday, November 4, 2008 9:38 AM
  • Using GO was one of the things the MSDN link, and the particular section I mentioned to you, said to do.

     

    Tuesday, November 4, 2008 1:19 PM
  • We are facing a similar issue. Could you please share your resolution if you have found any for this problem.
    Friday, May 21, 2010 5:57 AM
  • We are facing a similar issue. Could you please share your resolution if you have found any for this problem.
    Friday, May 21, 2010 5:58 AM