Home Forums Data Warehousing Integration Services Dynamically set variable to pass to foreach loop and exec web service inputs RE: Dynamically set variable to pass to foreach loop and exec web service inputs

  • March 30, 2015 at 9:14 am

    #1787390

    Marcus Farrugia (3/24/2015)

    Hi, I'm a little lost (and new) on where to begin with this. Using SSIS 2008 r2 I've been able to create a small package to connect to a currency converter web service and select the hardcoded supplied by a web service. However, doing it this way I would have to create multiple mini sequence containers and select the currency to and from country codes. I would like to create a user::to and user::from variable and then run a sql task to set the variables.

    Ie, select distinct( [to_countryCD], [from_CountryCD] ) from tbl_CountryCd

    and then run my sequence as a forloop dynamically passing the 'to' and 'from' country codes. Any idea's would really be appreciated.

    I am doing something similar, and in my scenario I have to pass a formatted url to extract the currency rates and save it as an xml file.

    This is how I am currently doing it, and it works pretty well for me:

    • I store all the different url's in a table, with placeholders for anything that needs to change (like dates). You can make the actual currency codes dynamic too, if you have those combinations somewhere for reference. My stored url looks something like this: http://currencysite.com/GetCurrencies.xml/GetRates?Symbols=~Currencies~&_token=bleh&StartDate=~StartDate~&EndDate=~EndDate~
    • I then have a stored proc that returns a recordset containing all the different combinations, replacing my placeholders with the required data.
    • In SSIS it is then fairly simple to execute the proc, store the results in an object variable and iterate through it in a "Foreach Loop" container.
    • Within the "Foreach Loop" container, I have a script task to send the url to the web service, consume the xml file and a "Data Flow" task to process the results.