Search code examples
ssisscript-taskexecute-sql-task

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

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.

Solution

  • 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