1

I need to use a result from SQL SELECT which should return an array of IDs to iterate through it in my foreach loop. I'm quite new to SSIS. I created Execute SQL Task, connect to DB and write 

SELECT ID FROM TABLE

Then I created Script Task and connect these two components with Constraint. But I don't know how to pass result from SQL Task into an object in Script Task.

KeithL
  • 5,348
  • 3
  • 19
  • 25
soldous
  • 111
  • 1
  • 9

1 Answers1

2

The typical pattern you are looking for is this:

Control Flow

1. In execute SQL you need to:
a. Assign the connection
b. Add your SQL Statement
c. Change Result Set to Full Result Set
d. Map the result set to an Object type variable

2. In Foreach
a. Change enumerator to ADO Enum
b. Assign your variable from #1
c. Map a variable to the interation

enter image description here

****EDIT****
3. Change out data flow for script task
a. Pass in iteration variable
b. in script create the URL as a string
c. use webclient to connect to web service


string url = @"https://www.blah.com? ID=" + 
             Dts.Variable["variable"].Value.ToString();

WebClient wc = new WebClient();
string result = wc.DownloadString(url);

4. Now you have to do something with that result
KeithL
  • 5,348
  • 3
  • 19
  • 25
  • If you really want to jump into a script task then you need to bring that object variable from step 1 into a script task and load it into a data table. But if you are doing that, I would rather just load the data table directly with the SQL in the script task. – KeithL Dec 10 '19 at 14:17
  • Thanks a lot @KeithL what I need is to call a REST API for each ID from source table and the ID is part of called URL so I need to get the ID somehow into the Script Task – soldous Dec 10 '19 at 14:26
  • Good use of this and the script task then. Change out data flow for a script task. I'll update step 3 – KeithL Dec 10 '19 at 14:28
  • Thank you again. You're the beast. But I'm not sure with passing in the iteration variable... so the same variable I put into ADO object source variable in Collection in foreach loop I should put in ReadOnlyVariables in Script in Script Task? – soldous Dec 10 '19 at 14:39
  • 2c. Map a variable to iteration in side Foreach. I'll add an image. – KeithL Dec 10 '19 at 14:41
  • Where do I send my invoice? Just kidding. Learning what you just did can earn you a lot of money. – KeithL Dec 10 '19 at 15:01