I was up at 3:00 AM in the morning and felt that the world needs to know where all the airports are and how you could use SSIS and PowerView to display this information in a clean and concise way. This need was so powerful that I spent the next 4 hours writing an SSIS package that would get this done. However I also realized that most guys might not follow how I got from a URL to the below Excel sheet so here is the whole thing documented. I will upload the entire project file along with some additional tweaks I am making to display weather information as well soon.

All the posts regarding this series are now mentioned below:-

Getting started

Using a Web service Task

Setting Dynamic File Paths

Using Execute command to run Powershell Scripts

Using a Foreach loop container for File iterations

Loading data using DFT

Enriching data using DFT

This series of posts and a follow up video will cover how to use some common tasks within SSIS, the overall steps are identified below.

Find a Datasource which provide airport info

I used http://free-web-services.com/web-services/geo/location-info/ since it’s a web service that is free. Naturally being free I doubt how authentic the data is but it’s good enough for now?

Extract data from this source

The SSIS Web Service task helped achieve this aspect

Transform the data

The file was encoded in UTF 16 and had issues with XML < and > being represented as < and > this issue was resolved using Powershell and the Execute process task.

Load the data

Loading the data was done using the DFT and ForeachFile loop.

Clean Up and Housekeeping

Archive the data once it’s been loaded.

Additional Formatting

Convert the Geo Coordinates 71.56 N 122.12 E into decimal Degrees coordinate system

So if you look at the process visually it’s like this

STEP1 Web Service output	STEP 2 SSIS Package Output
STEP 3 SQL Database Engine Output	STEP 4 Power View Output

STEP 1 – Getting data from the Web Service

Click File > new > project> select Integration services from the left hand side nav bar.

Give the Project a Name and press OK

Drag and Drop a ForEachLoop Container and a Web Service Task into the Package window as shown in the screenshot

Note: – Since the web service can provide airport info for a number of countries we are going to use a for each loop container and define a list of Countries for which we need info.

For each Country in the list the Web Service task will then be called.

In this step we configure the ForEachloop container. Click the Collection link on the left hand side nav bar.

1 – In the Enumerator select ForeachItem Enumerator

2 – Click Columns to get the popup 3

3 – On the popup click the Add button (4) once

Click OK on the Popup

5 In the List Enter the names of the countries you want to fetch airport info for.

Press OK

Click the Vairable Mappings Option from the Nav bar next

Note don’t worry if you are not seeing the variables like airportresponse etc yet.

You haven’t created them I have.

Click new variable.

In the variable Popup, define a variable called Countryname as shown in the screenshot, it will hold the value of the current country name being iterated.

Naturally it will be a string.

Press OK

Your screen should look like this. Press OK

Congrats you have configured a foreachloop container to iterate over a list.

Next we tackle the web service.

The next post configures the Web Service Task. Click here to move on.

Please Consider Subscribing

Subscribe