NHTSA Web Calls - Issues on Server

[johnca]

I am hoping someone can assist.

I am polling the NHTSA website for VIN information. The script I am using came from the NHTSA website. It works great locally with the desktop client but when I try to run it on the server it spits out an Error: Unknown error in MSXML. I've found but one instance where a possible fix is suggested by making a change in IE settings, but it didn't work. I also had them add the base URL to the trusted sites in IE. I do not have access to the server so I have to ask the admins to do anything I find and is cumbersome at best.

Maybe someone has tried, or is willing to try to get this to work on their server. I've attached a sample doc with 12 VIN's to query. I have >300K VIN's to process initially, then only new ones afterwards, so you see the need to run on the server. If I have to run it in batches I will.

V/r,

John

[Brett_Bleess]

John, my best hunch as to what is actually going on here would be the following, this likely has to do with the NHTSA site taking exception to the service making the request, think of it in terms of sites that have the robot check etc., I am betting NHTSA may have something that protects them from things like this as well, so you may want to see if you can get hold of someone on their side to determine if there may be an issue with a service account making all these requests etc., and if so, if they have any means for you to work around things...  Best I have for the moment.

Regards,
Brett

[johnca]

Excellent idea Brett. I will reach out to them and see what they say.

V/r,

John

[Brett_Bleess]

John, if you get confirmation I was on the right track, can you post what you find out and then mark that as the solution for us?

Cheers,
Brett

[johnca]

I contacted the  DOT folks and their response was that the decode on their end is pretty basic and that the issue is probably on the BI side. The error itself seems to point to Microsoft; "Unknown error in MSXML". Myself? I still suspect it is an issue with IE settings or some security thing with the OS on the server it is running. I will continue to work with our IT to find a solution.

My alternative method is to run eight documents fetching ~40k records each. From a desktop workstation it takes about 13 hours to complete, but it does work. 🙂

V/r,

John

[johnca]

I made some adjustments to the script and retrieval time dropped to under 3 hours for 40k VIN decodes. I had several of them running simultaneously and all finished under 3 hours.

I'm planning on opening an internal ticket and having the admins figure out why the server isn't allowing it. So far they've been hands off but I may be able to force the issue now.

[Brett_Bleess]

Thanks for the additional info John, am curious to see what you find in the end, do shout back if you find there may be something on our side that we can look into, but it seems you have a few more things you want to dig into first.  I believe you are on the right track especially since you did get things to run.  One thing just popped into my head though, am wondering if maybe something like Desktop Heap Space size may have something to do with this one?  

Desktop Heap Size & Number of QVB Engines for Distribution... 

That might be worth a shot, but if this is happening in Desktop client as well, I am not sure it will be in play then.  I cannot think of anything else at the moment.

Cheers,
Brett

[johnca]

I'm just trying to get 1 document working in dev, and is not an issue on my desktop computer. I can open Task Manager and see each processor running. On the server may be another story but will address those issue then.

I will certainly provide an update here when I get it working on the server...

Thanks for the insights with the administration documents.

[johnca · Accepted solution]

Solved!

I was able to get this working by changing the format from XML to JSON. 

Here is what I tried first and did not work. I first create a list of VIN's and then...

FOR Each vVIN in FieldValueList('VIN')

VIN:
LOAD
VariableId,
Variable,
ValueId,
Value,
'$(vVIN)' as VIN

FROM [https://vpic.nhtsa.dot.gov/api/vehicles/DecodeVin/$(vVIN)?format=xml] (XmlSimple, Table is [Response/Results/DecodedVariable])
Where Match(VariableId,5,18,26,27,28,29);

NEXT vVIN

After making the change this worked;

FOR Each vVIN in FieldValueList('VIN')

VIN:
LOAD
VariableId,
Variable,
ValueId,
Value,
'$(vVIN)' as VIN

FROM [https://vpic.nhtsa.dot.gov/api/vehicles/DecodeVin/$(vVIN)?/format=json] (XmlSimple, Table is [Response/Results/DecodedVariable])
Where Match(VariableId,5,18,26,27,28,29);

NEXT vVIN