Post reply

How to use stored procedure result set into a script task in SSIS

  • May 20, 2020 at 3:47 pm

    #3753301

    Hello, guys!

    I created a SSIS package in Visual Studio Community 2019:

    ssis

    Everything is working fine, but I don't know how to pass the data from "Commutations" (SQL Task) to "Net Present Value" (Script Task).

    Basically, "Commutations" generates a result set that I'm passing to a SSIS object variable. This result set has a couple of columns that I'd like to pass to a C# array of doubles in the script task, each column corresponds to a different array.

    Below I'm just illustrating an idea. I know it's not correct.

    double[] firstColumn = double[] { Dts.Variables["CommutationsFirstColumn"].Value }

    Why do I need that?

    "Commutations" has a probability stored for each age from 0 to 120. Each person needs the value of each column in "Commutations" corresponding to their age, so I'm trying to do the following as soon as a get the result set into an array:

    double probability1 = firstColumn [age];

    That's the idea that I'm having trouble to implement.

    Any help would be very appreciated.

    Thank you, guys

     

     

  • May 21, 2020 at 4:10 pm

    #3756628

    Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • May 26, 2020 at 1:28 pm

    #3757406

    Well, I can get you part of the way, I think.

    1. Create a new user variable in the package to hold your Commutations dataset. This variable should have a data type of "Object". You'll use this variable to hold the dataset results returned by the task. Name it what you like, but name it something that indicates what it contains (e.g. "CommuntationsDS" [for "commutations data set"], or whatever is meaningful to you)
    2. In your "Comuntations" Execute Sql Task, under "General", For the "ResultSet" propert, choose "Full Result Set". Next,  pick "Result Set" (on the left options, General, Parameter Mapping, Result Set, Expressions) and assign results to your user variable. Click "Add" to add a new result set, then set "Result Name" to "0" (zero) and under Variable Name, pick the variable you created in step #1
    3. In your "Net Present Value" script task, on the "Script" properties, add your user variable (created in step 1) to the "ReadOnlyVariables" under "Custom Properties" -- this is how you get the script to see your result set
    4. Use the result set as a data set, in your script. You have to convert the incoming Object variable into a .NET dataset. I don't have code on hand to do that, but you should be able to google for help on that.

    Also, you'll want to wire your Commutations Execute SQL Task into the flow, it's not wired in right now so it'll execute in parallel, and you won't be guaranteed that it'll be done and ready by the time the rest of your stuff runs. I'd wire that in before the "People Data" task, so it runs like Commutations -> People Data -> For Each Person

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

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