Figured it out.
The error message is the one from the .webpart file:
<?xml version="1.0" encoding="utf-8"?>
<webParts>
<webPart xmlns="http://schemas.microsoft.com/WebPart/v3">
<metaData>
<!--
The following Guid is used as a reference to the web part class,
and it will be automatically replaced with actual type name at deployment time.
-->
<type name="7F8C4D34-6311-4f22-87B4-A221FA8735BA" />
<importErrorMessage>Cannot import Project Filter.</importErrorMessage>
</metaData>
<data>
<properties>
<property name="Title" type="string">Project Filter</property>
<property name="Description" type="string">Provides a list of Projects that can be used to Filter other Web Parts.</property>
</properties>
</data>
</webPart>
</webParts>
The problem is that the original .webpart file was created on a 32-bit system with Visual Studio Extensions for WSS installed.
However as I'm now on a 64-bit machine VSEWSS is unavailable, and I believe that results in the above GUID not being substituted as I am not using those deployment tools.
Replacing the GUID with the full type name works.
So if you encounter the error message from your importErrorMessage node, then check that your type node in the .webpart file looks more like this (unrelated example):
<type name="TitleWP.TitleWP, TitleWP, Version=1.0.0.0, Culture=neutral, PublicKeyToken=9f4da00116c38ec5" />
This is in the format:
Class, Namespace, Version, Culture, PublicKey
You can grab that easily from the web.config file associated with your SharePoint instance, as it will be in the safe controls list.
Best Answer
A connection is probably not needed, all logic could be contained in the webpart (plus maybe custom editorpart). If you make the webpart configurable by end users it needs to be personalizable at the least.
There are sites (like Yahoo weather) that have javascript API's to get weather info based on zipcode / location. To get a user's geographical location, you need an IP to location database.
You could use MaxMind's GeoLite City free database, it maps IP addresses to geographical locations. Because it's free it is not accurate up to a meter, but for weather i'd say accurate enough. The database is updated monthly and comes in both binary and csv format (so you could import the csv into a SQL database). More info here.
Once you have a geographical location you can use Yahoo's weather API to get the info. Then parse and style the result.
P.S. GeoLite City is just an example, there are similar products out there, this is just the one I found googling, I Have no affiliation with them or anything like that.