lundi 11 mai 2015

RESTful API call in SSIS package

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.

Here is the schema as per the API documentation:

{StatusMessage : <string>, Unit : <string> [ { VehicleID : <int>, RegistrationNumber : <string>, Description : <string>, VehicleIcon : <string>, LocationText : <string>, Latitude : <double>, Longitude : <double>, Heading : <int>, Speed : <int>, LastEventDateTime : <yyyy-mm-ddThh:mm:ss>, VehicleInitials : <string>, LastEventDescription : <string>, IgnitionState : <int>} ] }

And here is a sample of the data returned (it comes back as a single line of text):

{"Vehicles":[{"VehicleId":512862,"RegistrationNumber":"BJ11 XXX","Description":"Mercedes Sprinter","VehicleIcon":"Vehicle10.png","LocationText":"Sample Address","Latitude":1,"Longitude":-1,"Heading":0,"Speed":0,"LastEventDateTime":"2015-05-11T09:43:39","VehicleInitials":"","LastEventDescription":"Stationary with Ignition OFF at Sample Address Since 09:43","IgnitionState":0,"OdometerReading":0,"OdometerReadingUnit":"imperial","OdometerReadingDate":"0001-01-01","OdometerReadingEnteredBy":"Unknown","ServiceRenewalDate":"0001-01-01","ServiceRenewalOdometerReading":0,"InsuranceRenewalDate":"0001-01-01","RoadTaxRenewalDate":"0001-01-01","MotRenewalDate":"0001-01-01","UserDefinedValue1Name":"","UserDefinedValue1Date":"0001-01-01","UserDefinedValue2Name":"","UserDefinedValue2OdometerReading":0,"DriverIdEnabled":false},{"VehicleId":508992,"RegistrationNumber":"BJ12 ZZZ","Description":"T27","VehicleIcon":"Vehicle11.png","LocationText":"Sample Address 2","Latitude":1,"Longitude":-1,"Heading":0,"Speed":0,"LastEventDateTime":"2015-05-07T15:32:51","VehicleInitials":"T27","LastEventDescription":"Stationary with Ignition OFF at Sample Address Since 07/05/2015 15:32","IgnitionState":0,"OdometerReading":0,"OdometerReadingUnit":"imperial","OdometerReadingDate":"0001-01-01","OdometerReadingEnteredBy":"Unknown","ServiceRenewalDate":"0001-01-01","ServiceRenewalOdometerReading":0,"InsuranceRenewalDate":"0001-01-01","RoadTaxRenewalDate":"0001-01-01","MotRenewalDate":"0001-01-01","UserDefinedValue1Name":" ","UserDefinedValue1Date":"0001-01-01","UserDefinedValue2Name":" ","UserDefinedValue2OdometerReading":0,"DriverIdEnabled":false},{"VehicleId":509203,"RegistrationNumber":"BJ11 CCC","Description":"T02","VehicleIcon":"Vehicle11.png","LocationText":"Sample Address","Latitude":1,"Longitude":-1,"Heading":0,"Speed":0,"LastEventDateTime":"2015-05-11T13:30:22","VehicleInitials":"T02","LastEventDescription":"Stationary with Ignition OFF at Sample Address Since 13:30","IgnitionState":0,"OdometerReading":0,"OdometerReadingUnit":"imperial","OdometerReadingDate":"0001-01-01","OdometerReadingEnteredBy":"Unknown","ServiceRenewalDate":"0001-01-01","ServiceRenewalOdometerReading":0,"InsuranceRenewalDate":"0001-01-01","RoadTaxRenewalDate":"0001-01-01","MotRenewalDate":"0001-01-01","UserDefinedValue1Name":"","UserDefinedValue1Date":"0001-01-01","UserDefinedValue2Name":"","UserDefinedValue2OdometerReading":0,"DriverIdEnabled":false}],"StatusMessage":null,"Unit":"imperial","HttpStatus":200}

Aucun commentaire:

Enregistrer un commentaire