What Garmin, or Should I Call Them GRRRRRRRman Doesn't Tell You

We have a Nuvi 40LM Garmin GPS that's supposed to have lifetime map replacement. Well, that ain't worth to much if you can't get their Garmin Express, or should I call it Garmin Exasperating, device syncing software to recognize your device.
I tried plugging the GPS into 3 different PCs, with Windows 8.1, 7 and XP, using 3 different USB cables, including a brand new official Garmin cable out of desperation.
I was always able to get both the device drive and MicroSD card inside of it recognized as drives in Windows Explorer, but the Garmin Express would never open up as it was supposed to, the moment we plugged the device in, nor would it ever find the device when we opened it manually.
We tried everything, including older Garmin Map Updaters and the MyGarmin interface on their site.
It wasn't until I wrote their customer service for help (Being they don't deign to offer after hours phone support).
I was shocked to get an email back a few hours later and DAD GUM if they didn't actually give me workable advice, so since it worked for us, here it be for you.

Personally, I think you can get it to work just by removing that GarminDevice xml file in the Garmin folder. I'd try that first.

I also first backed up all the files I removed from the device folders. It was about 1.7GB.

The following is quote/unquote verbatim from their email to me:

When the device is connected to the computer what do you see on the screen of your device? If the device displays the Garmin logo with a picture of the device connected to the computer tower, you should be able to go into the nuvi drive and remove unnecessary files from the device. I have included these steps below. 

1. Connect your GPS to the computer and allow the computer time to find the device

2. Open the Garmin drive, (PC: under "Computer" or "My Computer" Mac: On the Garmin Drive mounted on the desktop)

3. Inside the Garmin drive you will find several folders, you will want to go into each and remove any files you will not be using.

          Help - Remove any language that you will not be using - You will want to keep American_English file

          Keyboards - Remove any keyboards other then keyboards you will be using as well as the EN_US file

          Text - Remove any text files that you will not be using, please keep any that begin with EN_GB

          Voice - Remove any voice files that you will not be using, please keep any American English Voices

          Garmin - inside of the Garmin Folder, delete the GarminDevice or GarminDevice.XML Folder

4. Once the files have been removed, disconnect the GPS from the computer and allow it to power on.

5. When the device is loaded to the main 'Where To/View Map" screen allow it to wait there for appx. 30 seconds 

6. After 30 seconds, reconnect the unit to the computer and Garmin Express should find the device. 

If your device is not displaying the Garmin logo with the picture of the device connected to the computer tower, please connect the device to a different USB port on the computer. If you are using a desktop, please connect the device to the USB port on the back of the computer. If the device is still not connecting to the computer please check the cable to make sure it is the Garmin branded cable. If you look at the end that connects to the computer, below the USB plug the Garmin logo should be embossed on the casing below. If you do not see the Garmin logo on this cable, please try to use a different USB cable. Although these cables look similar and fit they can often transfer data differently. 

If you are still having trouble with your device connecting to the computer, please follow the steps below to perform a soft reset. 

1. Connect the device to the computer or vehicle power cable

2. Press and hold on the power button for 10-15 seconds

3. Release the power button and the device should power back on.

SSIS Overwriting Excel file destination and Fixing 64 bit Run Issue

I had a problem getting an SSIS package to write correctly to an Excel destination.
The first issue was that the Excel Connection Manager couldn't work in 64 bit mode, which my operating system and the SQL server running the package are.
This required a little fix in both the dtsx package, and the SQL server agent job running it.

The first step of fixing the dtsx package came from:
http://merlecarr.wordpress.com/2011/02/12/the-excel-connection-manager-is-not-supported-in-the-64-bit-version-of-ssis/

1: Open the project properties window (Not the package properties, but its parent project).
2: Go to Debugging beneath Configuration Properties.
3: Set Run64BitRuntime to False.

Then in your Server Agent job's properties, open to edit the step(s) running any such package and:
1: Open Advanced beneath the "Select a page" menu on the left.
2: Choose the "Exectution options" tab.
3: Check "Use 32 bit runtime".

Now that this worked, the new problem was that by default, the Excel destination just gets appended to, not overwritten. If you need to always refresh the destination file data, you have to first run a task to wipe the slate clean before importing the new data.

Ultimately this thread provided most of the answer:
http://social.msdn.microsoft.com/Forums/sqlserver/en-US/e26692b8-8044-4813-b643-0c181a1ea4fe/deleting-records-in-an-excel-sheet-using-ssis

1: Create a blank template of your destination Excel file to preserve your column setup.
2: Place a File System Task ahead of your Data Flow Task that imports the destination data.
3: In that file system task, set Operation to "Copy File".
4: Set your Destination Connection to the destination file, with OverwriteDestination set to True.
4: Set Source Connection to your template file.

That should be all it takes, but wouldn't it be amazing if MicroSoftInTheHead would update their @#$%^&* Jet Engine to provide a simple checkbox for overwriting existing data in the Excel Destination data flow? Yeah, right, like we'll ever see that in our lifetime.

C# class with Methods to Get Json or Xml Data Into List

Below is a class of columns returned by a web service API, with methods to get that data in either Json or Xml form, and send that data into a List of the class properties.

The controller ActionResult (Site is .Net MVC) using this class either uses:

BusInfo bi = new Models.BusInfo();
            IEnumerable model = bi.getDataX();//gets data via xml from web service
            ViewData["PageTitle"] = "Business Information";
            return View(model);

or

BusInfo bi = new Models.BusInfo();
            IEnumerable model = bi.getDataJ();//gets data in json form from web service
             ViewData["PageTitle"] = "Business Information";
            return View(model);

depending on whether I want to get Json or Xml.

I have 3 Xml retrieval methods. The default one used above places the Xml data in a DataSet, then to the list.
The 2nd, getDataX2, has no middleman. Data goes from Xml to list, via XmlReader.
The 3rd, getDataX3, converts Xml to Json, thence to list.

The class is below:

using System;
using System.Collections.Generic;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using System.Data;
using System.Web;
using System.Web.Mvc;
using System.Net;
using System.Runtime.Serialization.Json;//used in getDataJ
using System.Xml;
using System.Xml.Linq;

namespace SiteName.Models
{
    public class BusInfo
    {
        public string title { get; set; }
        public string url { get; set; }
        public string description { get; set; }
        public string keywords { get; set; }
        public string category { get; set; }

        readonly string baseUrij = "http://api.sba.gov/rec_sites/all_sites/keywords.json";//json
        readonly string baseUrix = "http://api.sba.gov/rec_sites/all_sites/keywords.xml";//xml

        public BusInfo()
            : base()
        {

        }

        //this constructor is needed if using getDataX2 because:
        //we need an alternative while looping through the reader, to setting a BusInfo instance for each group of properties
        //in order to Add to List because the list ends up with all members set to the value of the last iteration
        //ie, bi.Add(b) just puts a reference to 'b' in each row when set inside the reader. change b, change every member of bi
        //instead we're setting local variables that are fed directly into bi.Add as a new BusInfo() for each iteration
        public BusInfo(string t, string u, string d, string k, string c)
        {
            title = t;
            url = u;
            description = d;
            keywords = k;
            category = c;
        }
        
        public List getDataJ()//for json source, json to list
        {

            string uri = baseUrij;
            WebRequest wr = WebRequest.Create(uri);
            WebResponse wrs = wr.GetResponse();
            DataContractJsonSerializer jsonSerializer = new DataContractJsonSerializer(typeof(List));
            List bi = (List)jsonSerializer.ReadObject(wrs.GetResponseStream());
            return bi;

        }//end getDataJ

        public List getDataX()//for xml source xml to dataset to list
        {
            //parent node in dataset becomes 'sites_id' in place of 'sites site'
            string uri = baseUrix;
            HttpWebRequest wr = (HttpWebRequest) WebRequest.Create(uri);
            wr.Method = "GET";
            List bi = new List();
            DataSet ds = new DataSet();
            ds.ReadXml(uri);
            if (ds != null)
            {
                if (ds.Tables.Count > 0)
                {
                    foreach (DataTable dt in ds.Tables)
                    {
                        foreach (DataRow dr in dt.Rows)
                        {
                            BusInfo b = new BusInfo();                            
                            foreach (DataColumn dc in dr.Table.Columns)
                            {
                                switch (dc.ColumnName)
                                {
                                    case "title":
                                        b.title = dr[dc.ColumnName].ToString().Trim();
                                        break;
                                    case "url":
                                        b.url = dr[dc.ColumnName].ToString().Trim();
                                        break;
                                    case "description":
                                        b.description = dr[dc.ColumnName].ToString().Trim();
                                        break;
                                    case "keywords":
                                        b.keywords = dr[dc.ColumnName].ToString().Trim();
                                        break;
                                    case "category":
                                        b.category = dr[dc.ColumnName].ToString().Trim();
                                        break;
                                    default:
                                        break;
                                }                               
  
                            }
                            bi.Add(b);
                            
                        }
                    }
                }
            }
            return bi;

        }//end getDataX

        public List getDataX2()//for xml source xml to list direct - no middleman
        {
            List bi = new List();
            string uri = baseUrix;
            string em = "";
            XmlReaderSettings settings = new XmlReaderSettings();
            settings.IgnoreComments = true;
            settings.DtdProcessing = DtdProcessing.Parse;
            int bc = 0;
            bool runBiAdd = false;
                
            try
            {
                using (XmlReader reader = XmlReader.Create(uri, settings))
                {
                    //cannot use an instance of BusInfo inside reader - must use local variables
                    string btitle = "";
                    string burl = "";
                    string bdescription = "";
                    string bkeywords = "";
                    string bcategory = "";
                    while (reader.Read())
                    {                       
            
                        if (reader.NodeType == XmlNodeType.Element)
                        {
                            runBiAdd = false;
                            switch (reader.Name)
                            {
                                case "title":
                                    bc++;
                                    btitle = reader.ReadString().ToString();
                                    runBiAdd = true;
                                    break;
                                case "url":
                                    bc++;
                                    burl = reader.ReadString().ToString();
                                    runBiAdd = true;
                                    break;
                                case "description":
                                    bc++;
                                    bdescription = reader.ReadString().ToString();
                                    runBiAdd = true;
                                    break;
                                case "keywords":
                                    bc++;
                                    bkeywords = reader.ReadString().ToString();
                                    runBiAdd = true;
                                    break;
                                case "category":
                                    bc++;
                                    bcategory = reader.ReadString().ToString();
                                    runBiAdd = true;
                                    break;
                                default:
                                    break;
                            }
                            if (runBiAdd = true && bc == 5)
                            {
                                bi.Add(new BusInfo(btitle,burl,bdescription,bkeywords,bcategory));//see notes by constructor above
                                bc = 0;
                            }                                
                        }                                
                    }                                
                }                                 
            }
            catch (Exception ex)
            {
                em = "An Error Occured: " + ex.Message;
                bi.Clear();
            } 
            
            return bi;
        }//end getDataX2

        //gets xml into json, then into list
        //have to know structure of xml schema
        public List getDataX3()
        {
            List bi = new List();
            string uri = baseUrix;
            bi.Clear();
            string em = "";
            XmlReaderSettings settings = new XmlReaderSettings();
            settings.IgnoreComments = true;
            settings.DtdProcessing = DtdProcessing.Parse;
            XmlDocument doc = new XmlDocument();
            try
            {
                using (XmlReader reader = XmlReader.Create(uri, settings))
                {
                    while (reader.Read())
                    {
                        doc.Load(reader);
                    }
                }
                
                string json = XMLToJson.XmlToJSON(doc);
                
                JObject jo = JObject.Parse(json);
                foreach (var v in jo["sites"]["site"])
                {
                    BusInfo b = new BusInfo();
                    b.title = v["title"].ToString();
                    b.url = v["url"].ToString();
                    b.description = v["description"].ToString();
                    b.keywords = v["keywords"].ToString();
                    b.category = v["category"].ToString();
                    bi.Add(b);

                }
            }
            catch (Exception ex)
            {
                em = "An Error Occured: " + ex.Message;              
            } 
            return bi;
        }//end getDataX3

     }
  
}

Using XmlReader and XmlReaderSettings in C3 Asp.Net MVC

I already had methods for retrieving both Json and Xml formatted data from an API web service.
What I still couldn't get to work was getting the data in Xml and converting it cleanly to Json.
Problem thus far are the child nodes and the fact that the conversion methods kept leaving escape slashes in the Json string, despite running it through Regex.Unescape.

And in truth, there may be no logical reason why I should need to do such a conversion, since my other 2 methods cleanly placed data from both formats into my webgrid's class.

My Xml method did it by first reading all the data into a dataset, from whence the data went into my class List feeding the webgrid.

I wanted another way of dealing with the Xml, for better, worse, or just for variety and learning sake.
Thanks to this great blog article by Saiful Alam, at http://asp-net-example.blogspot.com/2009/05/xmlreadersettings-how-to-use.html, I got my 2nd Xml to List method.

public List getDataX2()//for xml source xml to list direct - no middleman
        {
            List bi = new List();
            string uri = baseUrix;
            string em = "";
            XmlReaderSettings settings = new XmlReaderSettings();
            settings.IgnoreComments = true;
            settings.DtdProcessing = DtdProcessing.Parse;
            int bc = 0;
            bool runBiAdd = false;
                
            try
            {
                using (XmlReader reader = XmlReader.Create(uri, settings))
                {
                    //cannot use an instance of BusInfo inside reader - must use local variables
                    string btitle = "";
                    string burl = "";
                    string bdescription = "";
                    string bkeywords = "";
                    string bcategory = "";
                    while (reader.Read())
                    {                       
            
                        if (reader.NodeType == XmlNodeType.Element)
                        {
                            runBiAdd = false;
                            switch (reader.Name)
                            {
                                case "title":
                                    bc++;
                                    btitle = reader.ReadString().ToString();
                                    runBiAdd = true;
                                    break;
                                case "url":
                                    bc++;
                                    burl = reader.ReadString().ToString();
                                    runBiAdd = true;
                                    break;
                                case "description":
                                    bc++;
                                    bdescription = reader.ReadString().ToString();
                                    runBiAdd = true;
                                    break;
                                case "keywords":
                                    bc++;
                                    bkeywords = reader.ReadString().ToString();
                                    runBiAdd = true;
                                    break;
                                case "category":
                                    bc++;
                                    bcategory = reader.ReadString().ToString();
                                    runBiAdd = true;
                                    break;
                                default:
                                    break;
                            }
                            if (runBiAdd = true && bc == 5)
                            {
                                bi.Add(new BusInfo(btitle,burl,bdescription,bkeywords,bcategory));//see notes by constructor above
                                bc = 0;
                            }                                
                        }                                
                    }                                
                }                                 
            }
            catch (Exception ex)
            {
                em = "An Error Occured: " + ex.Message;
                bi.Clear();
            } 
            
            return bi;
        }//end getDataX2


One issue I didn't consider while making this method, was the bugaboo when I attempted to keep reusing an instance of my BusInfo class, to add to my List during the reader's iterations.
As those of you who are already laughing at me already knew (however many "those-of-you"s there are), within the scope of this code, setting properties of a BusInfo instance for each iteration of the reader, in order to then add to the list, eg: BusInfoList.Add(BusInfoInstance), ends up changing all previous items in the list to the latest, and not so greatest, property values added to the newest item.
To fix this, I gave the class a constructor override with the 5 class properties as parameters, hence giving me the list.Add line "bi.Add(new BusInfo(btitle,burl,bdescription,bkeywords,bcategory));"

The constructors I added to the class:

public BusInfo()
            : base()
        {
        }
       
        public BusInfo(string t, string u, string d, string k, string c)
        {
            title = t;
            url = u;
            description = d;
            keywords = k;
            category = c;
        }