Post reply

DTS and Output from contained Stored Procedure

  • September 2, 2004 at 7:30 am

    #59972

    I have a DTS which imports an excel file, it then calls a stored procedure with two Global Variables -

    Exec netSPT_UploadValidation ?,?

    I need to get an output, a return value from this procedure so that I can pick it up in my C# code. I've tried everything I can think of and hit a brick wall. I need help!

    Thanks in advance.

  • September 3, 2004 at 6:42 am

    #521631

    Are you doing this an an ActiveX Script or an SQL task?

  • September 3, 2004 at 6:47 am

    #521634

    SQL Task to execute the procedure

  • September 3, 2004 at 6:51 am

    #521635

    sorry, but I should have also asked you what version of SQL Server you are using.

  • September 3, 2004 at 6:54 am

    #521636

    I'm using SQL Server 2000

  • September 3, 2004 at 7:06 am

    #521641

    If there's a way to do it using the SQL Task, I can't figure it out this quickly.  I see the output parameters tab, but it doesn't seem to p/u an individual parameter.

    I'll do some reading over the weekend, but in the meantime I would suggest using an ActiveX script task to return the output paramter.  The code would be something like:

      Set cmdLastRunDate = CreateObject("ADODB.Command")

      With cmdLastRunDate

       .CommandType = 4

       .CommandText = "<<Name>>"

       .ActiveConnection = cnn

       .Parameters.Append .CreateParameter("Package", adChar, adParamInput, 6, "RSIMP")

       .Parameters.Append .CreateParameter("Key", adVarChar, adParamInput, 25, "LASTRUN")

       .Parameters.Append .CreateParameter("Value", adVarChar, adParamOutput, 255)

       .Execute , , 128

       <<Name of Global Variable>>=CDate(.Parameters("Value"))

      End With

    This should get you going for now.  It may be the only solution.

  • September 3, 2004 at 7:16 am

    #521644

    Many thanks. I will give this a go. I've tried the output tab on the global variables box - It doesn't seem to let you assign them to output a value from stored procedures, its ok with regular sql statements but I can't get it working the other way.

    Thanks Again.

  • September 3, 2004 at 7:56 am

    #521649

    Matt,

    From the Output parameters tab select the "Create Global Variables" option. This will let you choose the type of output expected (IE single value, recordset...).

    Hope this helps

  • September 3, 2004 at 8:04 am

    #521661

    Not sure I'm with you - I've got the sql task set up to accept two incoming parameters - I click on the parameters button on the sql task box and I can map my parameters, I've mapped the two incoming ones. When I click the output tab it doesn't show my third parameter so I can't write to it. 

  • September 3, 2004 at 8:09 am

    #521663

    You'll need to have the constants declared if you use the code as it appears.  Here are the ones that appear in the code:

    '---- DataTypeEnum Values ----

    Const adChar = 129

    Const adInteger = 3

    Const adVarChar = 200

    '---- CommandInputEnum Values ----

    Const adParamInput = 1

    Const adParamOutput = 2

  • September 3, 2004 at 8:11 am

    #521664

    I found the same thing

  • September 3, 2004 at 3:33 pm

    #521745

    I created a proc that accepts one input parameter and returns one output parameter. I created two Global Parameters (one for input and one for output - I could have created the output one from the output tab on the parameter mapping screen).

    The query looks like this EXECUTE GetLocalityID ?,?

    Once the code is in place I select the Parameter button and from the input parameter tab I select the Global Parameter I want to pass in. Then I select the OutPut Parameter tab and under the Output Global Variables I select the parameter to use there.

  • September 6, 2004 at 2:02 am

    #521818

    The problem I'm having when I try to do this is that for some reason when I go to the output parameters tab it doesn't list any parameters for output. I've tried it before with a simple SQL statement so I know it works but for some reason not in this case.

    Thanks

  • September 7, 2004 at 6:54 am

    #521944

    Did some reading over the weekend, and found a way you could do this.  I'm not sure exactly what way you're trying to get the output parameters, but if it's from a select statement that returns 1 record, write

    SELECT x FROM y WHERE <<criteria>>. 

    You can then assign the item to an output parameter through the Output Parameters tab.

    If the output parameter is some sort of calculation, you can always end the SP with

    SELECT @OutputParameter.

    This will have the same result as above.  If you have multiple statements in your procedure you must use SET NOCOUNT ON.

    Don't rule out doing the process in the ActiveX script as I outlined earlier, but if a SQL task really is the bes, then you can do it this way.

  • September 8, 2004 at 4:52 am

    #522088

    Matt

    You can assign your stored procedure output in a global variable rowset.

    For more info on the rowset take a look at this article

    http://www.sqldts.com/default.aspx?298

    --------------------
    Colt 45 - the original point and click interface

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply