Post reply

Newbie SSIS Question - Ole DB Command and Output Param problem. Help !

  • March 1, 2007 at 12:59 pm

    #72631

    Hi,

    I wonder if anybody can please help. I am using SQL 2005 and I have

    generated a data flow which has a OLE DB Source which is retrieving a list

    of Ids. I then need to call another SP (with 2 output params) passing in the

    ID from the previous step. I have managed to execute the second SP but the

    values returned from the output params are not being populated. What am I doing wrong ?

    Just to reiterate, the process is as follows

    1 : Setup variables

    2 : Run OLE DB Source to obtain list of IDs

    3 : Run OLE Db Command with ID from previous step and store the return

    params in the variables setup in Step 1.

    One other question, is the OLE DB Command the best tool for this job ?

    Any help with this would be much appreciated.

    Thanks in advance.

  • March 5, 2007 at 8:00 am

    This was removed by the editor as SPAM

  • March 5, 2007 at 1:24 pm

    #693201

    It's a little vauge and not my forte but I'll give it a shot.

    Is this all being run in one sql statement or is it run SP_1 then SP_2

    You could pull ID's to a temp table and SQL loop thru the temp table to get your values then drop the temp table.

    or you could create or update SP_2 as select from your OLE DB connection

    Where is the output from step 1 going, to file to temp table?

    what are the variables for?

    _______________________________________________________________________
    Work smarter not harder.

  • March 5, 2007 at 3:38 pm

    #693223

    Hi,

    Thanks for the reply but I have now sorted the problem. It seems that if all the output params are set to NULL within the SP then the SSIS package uses the values of the params before they were passed into the SP. If on the other hand one of the output params is set to anything other than NULL the whole process works.

    The original SP was as follows

    Begin ....

    Select @output1 = field1, @output2 = field2 from Table where ID = 1

    It now reads

    Begin ....

    Declare @val1 int

    Declare @val2 int

    Select @val1 = field, @val2 = field2 from Table where ID = 1

    Set @output1 = @val1

    Set @output2 = @val2.

    Thanks for your help.

Viewing 4 posts - 1 through 3 (of 3 total)

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