title: RESTful API call in SSIS package
body: <p>As part of a SSIS (2005) package I am calling a RESTful API through a script component (VB.NET).  I am getting the records back OK.  The problem I have though is processing the format it comes back in so I can output it (split into the separate output columns) within the data flow and write into a SQL table.  Don't know where to start - anyone got any ideas?  I have no control over the API so am stuck with this format.</p>
  
  <p>Here is the schema as per the API documentation:</p>
  
  <pre><code>{StatusMessage : &lt;string&gt;, Unit : &lt;string&gt; [ { VehicleID : &lt;int&gt;, RegistrationNumber : &lt;string&gt;, DistanceTravelled : &lt;double&gt;} ] }
  </code></pre>
  
  <p>And here is a sample of the data returned (it comes back as a single line of text):</p>
  
  <pre><code>{"VehicleDistance":
     [
      {
         "VehicleId":508767,
         "RegistrationNumber":"BJ63 NYO",
         "DistanceTravelled":0.09322560578584671
      },
      {
         "VehicleId":508788,
         "RegistrationNumber":"BJ63 NYL",
         "DistanceTravelled":6.1591048240661621
      },
      {
        "VehicleId":508977,
        "RegistrationNumber":"PE12 LLC",
        "DistanceTravelled":60.975761413574219
      },
      {
        "VehicleId":510092,
        "RegistrationNumber":"BJ64 FCY",
        "DistanceTravelled":14.369173049926758
      },
      {
        "VehicleId":510456,
        "RegistrationNumber":"BJ63 NYY",
        "DistanceTravelled":4.04599142074585
      },
      {
        "VehicleId":513574,
        "RegistrationNumber":"BL64 AEM",
        "DistanceTravelled":302.150390625
      }
    ],
    "StatusMessage":null,
    "Unit":"imperial",
    "HttpStatus":200
    }
  </code></pre>