SSIS - How to use results of Execute SQL Task to call a Web Service

Question

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.

Answer 1

The typical pattern you are looking for is this:

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

****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