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:-
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.
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
|Open SQL Server Data Tools Or Business Intelligence development Studio ( prior to SQL 2012)|
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.
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.
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.