Introduction
Weather Scraper is used to get weather information for your data warehouse and reporting/analytical needs.
Create a SQL table to store the weather information:
CREATE TABLE [dbo].[Weather](
[ID] [int] IDENTITY(1,1) NOT NULL,
[InsertDate] [varchar](255) NULL,
[ZipCode] [varchar](255) NULL,
[CityID] [varchar](255) NULL,
[CityName] [varchar](255) NULL,
[CoordLong] [varchar](255) NULL,
[CoordLat] [varchar](255) NULL,
[Country] [varchar](255) NULL,
[SunriseStart] [varchar](255) NULL,
[SunriseSet] [varchar](255) NULL,
[TemperatureAvg] [varchar](255) NULL,
[TemperatureMin] [varchar](255) NULL,
[TemperatureMax] [varchar](255) NULL,
[TemperatureUnit] [varchar](255) NULL,
[HumidityValue] [varchar](255) NULL,
[HumidityUnit] [varchar](255) NULL,
[PressureValue] [varchar](255) NULL,
[PressureUnit] [varchar](255) NULL,
[WindSpeedValue] [varchar](255) NULL,
[WindSpeedName] [varchar](255) NULL,
[WindDirectionValue] [varchar](255) NULL,
[WindDirectionCode] [varchar](255) NULL,
[WindDirectionName] [varchar](255) NULL,
[CloudValue] [varchar](255) NULL,
[CloudName] [varchar](255) NULL,
[PrecipitationMode] [varchar](255) NULL,
[WeatherNumber] [varchar](255) NULL,
[WeatherValue] [varchar](255) NULL,
[WeatherIcon] [varchar](255) NULL,
[LastUpdateValue] [varchar](255) NULL,
PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, _
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
After Table is created, we will use the http://api.openweathermap.org RESTful API to access and store the Weather Information.
You can see sample weather information returned from query by accessing this link.
SSIS Package
The package is very simple:
- Get List all the ZipCodes
- Loop through each ZipCode and Get Current Weather Information For.
Details:
SELECT DISTINCT [ZipCode] FROM [dbo].[ZipCodes] order by ZipCode Desc
Store results in variable.
Loop through each individual ZipCode
in the ForEachLoop
container.
Map the individual Zip Codes to a ZipCode
variable.
Pass the “loaded” ZipCode
variable in the ForEachLoop
container to the script task so as to pull the weather information for a particular ZipCode
.
Edit the script task. To see the code:
This is the key:
- Build your URL using the
ZipCode
in order to get the result. - I specify USA in the
string
to return only US results. There is much documentation on the openweathermap website on how to search for specific data. - http://api.openweathermap.org/API#search_city
var url = @"http://api.openweathermap.org/data/2.5/weather?q="+ZipCode+",USA&mode=xml";
The two methods in my implementation are the main()
method and the SaveWeatherData()
:
MainMethod
builds URL, makes call to API, and parses out the resulting XML.
SaveWeatherData
method is called by main
method. It takes parameter values and persists them in the database table.
Each time the script tasked is called, the weather data for that ZipCode
be returned and inserted into your table.
#region Help: Introduction to the script task
#endregion
#region Namespaces
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.Net;
using System.IO;
using System.Xml;
using System.Text;
using System.Data.SqlClient;
#endregion
namespace ST_e848ffd56e444ae4a1307413b7ff543d
{
[Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{
string DatabaseName = null;
string ServerName = null;
public void Main(){
string CityID = null;
string CityName = null;
string CoordLong = null;
string CoordLat = null;
string Country = null;
string SunriseStart = null;
string SunriseSet = null;
string TemperatureAvg = null;
string TemperatureMin = null;
string TemperatureMax = null;
string TemperatureUnit = null;
string HumidityValue = null;
string HumidityUnit = null;
string PressureValue = null;
string PressureUnit = null;
string WindSpeedValue = null;
string WindSpeedName = null;
string WindDirectionValue = null;
string WindDirectionCode = null;
string WindDirectionName = null;
string CloudValue = null;
string CloudName = null;
string PrecipitationMode = null;
string WeatherNumber = null;
string WeatherValue = null;
string WeatherIcon = null;
string LastUpdateValue = null;
string ZipCode = Dts.Variables["ZipCode"].Value.ToString();
DatabaseName = Dts.Variables["$Project::DatabaseName"].Value.ToString();
ServerName = Dts.Variables["$Project::ServerName"].Value.ToString();
var url = @"http://api.openweathermap.org/data/2.5/weather?q="+
ZipCode+",USA&mode=xml";
var syncClient = new WebClient();
var content = syncClient.DownloadString(url);
StringBuilder output = new StringBuilder();
using (XmlReader reader = XmlReader.Create(new StringReader(content)))
{
XmlWriterSettings ws = new XmlWriterSettings();
ws.Indent = true;
using (XmlWriter writer = XmlWriter.Create(output, ws))
{
while (reader.Read())
{
if (reader.IsStartElement())
{
switch (reader.Name.Trim())
{
case "city":
CityID = reader.GetAttribute("id");
CityName = reader.GetAttribute("name");
break;
case "coord":
CoordLong = reader.GetAttribute("lon");
CoordLat = reader.GetAttribute("lat");
break;
case "country":
Country = reader.ReadElementString();
break;
case "sun":
SunriseStart = reader.GetAttribute("rise");
SunriseSet = reader.GetAttribute("set");
break;
case "temperature":
TemperatureAvg = reader.GetAttribute("value");
TemperatureMin = reader.GetAttribute("min");
TemperatureMax = reader.GetAttribute("max");
TemperatureUnit = reader.GetAttribute("unit");
break;
case "humidity":
HumidityValue = reader.GetAttribute("value");
HumidityUnit = reader.GetAttribute("unit");
break;
case "pressure":
PressureValue = reader.GetAttribute("value");
PressureUnit = reader.GetAttribute("unit");
break;
case "speed":
WindSpeedValue = reader.GetAttribute("value");
WindSpeedName = reader.GetAttribute("name");
break;
case "direction":
WindDirectionValue = reader.GetAttribute("value");
WindDirectionCode = reader.GetAttribute("code");
WindDirectionName = reader.GetAttribute("name");
break;
case "clouds":
CloudValue = reader.GetAttribute("value");
CloudName = reader.GetAttribute("name");
break;
case "precipitation":
PrecipitationMode = reader.GetAttribute("mode");
break;
case "weather":
WeatherNumber = reader.GetAttribute("number");
WeatherValue = reader.GetAttribute("value");
WeatherIcon = reader.GetAttribute("icon");
break;
case "lastupdate":
LastUpdateValue = reader.GetAttribute("value");
break;
}
}
}
}
}
this.SaveWeatherData(ZipCode, CityID, CityName, CoordLong, CoordLat,
Country, SunriseStart, SunriseSet, TemperatureAvg, TemperatureMin,
TemperatureMax, TemperatureUnit, HumidityValue, HumidityUnit, PressureValue,
PressureUnit, WindSpeedValue, WindSpeedName, WindDirectionValue, WindDirectionCode,
WindDirectionName, CloudValue, CloudName, PrecipitationMode, WeatherNumber,
WeatherValue, WeatherIcon, LastUpdateValue);
Dts.TaskResult = (int)ScriptResults.Success;
}
public void SaveWeatherData(string ZipCode, string CityID, string CityName,
string CoordLong, string CoordLat, string Country, string SunriseStart,
string SunriseSet, string TemperatureAvg, string TemperatureMin,
string TemperatureMax, string TemperatureUnit, string HumidityValue,
string HumidityUnit, string PressureValue, string PressureUnit,
string WindSpeedValue, string WindSpeedName, string WindDirectionValue,
string WindDirectionCode, string WindDirectionName, string CloudValue,
string CloudName, string PrecipitationMode, string WeatherNumber,
string WeatherValue, string WeatherIcon, string LastUpdateValue)
{
try
{
string connectionString = @"Replace Me";
using (SqlConnection conn = new SqlConnection(connectionString))
{
SqlCommand Storproc = new SqlCommand(@"INSERT INTO [dbo].[Weather] (ZipCode, CityID, CityName,
CoordLong,
CoordLat,
Country,
SunriseStart,
SunriseSet,
TemperatureAvg,
TemperatureMin,
TemperatureMax,
TemperatureUnit,
HumidityValue,
HumidityUnit,
PressureValue,
PressureUnit,
WindSpeedValue,
WindSpeedName,
WindDirectionValue,
WindDirectionCode,
WindDirectionName,
CloudValue,
CloudName,
PrecipitationMode,
WeatherNumber,
WeatherValue,
WeatherIcon,
LastUpdateValue)
VALUES (
@ZipCode,
@CityID,
@CityName,
@CoordLong,
@CoordLat,
@Country,
@SunriseStart,
@SunriseSet,
@TemperatureAvg,
@TemperatureMin,
@TemperatureMax,
@TemperatureUnit,
@HumidityValue,
@HumidityUnit,
@PressureValue,
@PressureUnit,
@WindSpeedValue,
@WindSpeedName,
@WindDirectionValue,
@WindDirectionCode,
@WindDirectionName,
@CloudValue,
@CloudName,
@PrecipitationMode,
@WeatherNumber,
@WeatherValue,
@WeatherIcon,
@LastUpdateValue
);", conn);
Storproc.Parameters.AddWithValue("@ZipCode", ZipCode);
Storproc.Parameters.AddWithValue("@CityID", "10");
Storproc.Parameters.AddWithValue("@CityName", CityName);
Storproc.Parameters.AddWithValue("@CoordLong", CoordLong);
Storproc.Parameters.AddWithValue("@CoordLat", CoordLat);
Storproc.Parameters.AddWithValue("@Country", Country);
Storproc.Parameters.AddWithValue("@SunriseStart", SunriseStart);
Storproc.Parameters.AddWithValue("@SunriseSet", SunriseSet);
Storproc.Parameters.AddWithValue("@TemperatureAvg", TemperatureAvg);
Storproc.Parameters.AddWithValue("@TemperatureMin", TemperatureMin);
Storproc.Parameters.AddWithValue("@TemperatureMax", TemperatureMax);
Storproc.Parameters.AddWithValue("@TemperatureUnit", TemperatureUnit);
Storproc.Parameters.AddWithValue("@HumidityValue", HumidityValue);
Storproc.Parameters.AddWithValue("@HumidityUnit", HumidityUnit);
Storproc.Parameters.AddWithValue("@PressureValue", PressureValue);
Storproc.Parameters.AddWithValue("@PressureUnit", PressureUnit);
Storproc.Parameters.AddWithValue("@WindSpeedValue", WindSpeedValue);
Storproc.Parameters.AddWithValue("@WindSpeedName", WindSpeedName);
Storproc.Parameters.AddWithValue("@WindDirectionValue", WindDirectionValue);
Storproc.Parameters.AddWithValue("@WindDirectionCode", WindDirectionCode);
Storproc.Parameters.AddWithValue("@WindDirectionName", WindDirectionName);
Storproc.Parameters.AddWithValue("@CloudValue", CloudValue);
Storproc.Parameters.AddWithValue("@CloudName", CloudName);
Storproc.Parameters.AddWithValue("@PrecipitationMode", PrecipitationMode);
Storproc.Parameters.AddWithValue("@WeatherNumber", WeatherNumber);
Storproc.Parameters.AddWithValue("@WeatherValue", WeatherValue);
Storproc.Parameters.AddWithValue("@WeatherIcon", WeatherIcon);
Storproc.Parameters.AddWithValue("@LastUpdateValue", LastUpdateValue);
conn.Open();
Storproc.ExecuteNonQuery();
conn.Close();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
conn.Close();
}
}
#region ScriptResults declaration
enum ScriptResults
{
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
};
#endregion
}
}
The execution looks like this:
Your results should look like this:
Now, you have detailed weather information with date and zip codes at your disposal. You can tie this with location information in your database or data warehouse to do extensive querying. For example:
- How does rain affect my sales by region
- How does humidity affect sales
- How does cloud cover affect sales
- How does weather affect tips
- How does weather affect Employee productivity
The job can be scheduled to run hourly, daily, weekly or whatever frequency you want.
The sky (pun intended) is virtually the limit on this.
Good luck!
I'm a developer, blogger and all around technology enthusiast. He writes and stays abreast with the latest innovative ideas, news, and trends.