Lexis/Nexis & CarFax Integration Progress

As much as I would love to say that I have completed the integration with OSSI and Lexis/Nexis I have to admit that this project appears to be progressing slower than I had hoped. Despite the pace there is definitely progress. For almost two weeks now I have spent my time examining the DHSMV schema for the ECrash report and reviewed the specific data requirements.  As a side note, there is a great deal of information provided by FL DHSMV in regards to the ECrash report, and the crash report as a whole. I was able to answer most all of my question with the information provided here.

I have to admit that my original task of importing crash data based on the DHSMV schema was expanded and now includes exporting crash data to XML to meet the same schema. I was told that since I am already doing an import might as well reverse it to share the data with CarFax. CarFax offers a number of benefits to participating agencies who provide their jurisdictions crash data in a supported XML format, such as that outlined by FL DHSMV.

I originally viewed this second endeavor more favorably than the import, believing it to be much easier.  I immediately formed a plan of attack and got to work, those non-technical people PLEASE skip this.  My initial try was to create a dataset in an Integration Services script task that mirrored the schema provided.  Since datasets in .NET are persisted as XML my plan was to cheat the system import my data into the dataset and then use the WriteXml method.  Ta Da!!  I could than spend the next 4-5 weeks emailing, texting, and blogging about how busy I was on the import.  Unfortunately I was soon reminded that datasets do not support all schemas, and as the crash schema is particularly complex I never stood a chance.

I have since regrouped from that minor setback and I am back in the game.  I am once again focusing on the data mapping from the relational database to the ECrash schema.  In order to attempt to speed the process up I was able to create a relational database based on the crash schema, (I KNOW!!  PRETTY KEWL!! Stick around if you want the trick)  Using this I will be able to more easily and quickly map the ETL process both to and from. 

Once the mapping is done, hopefully next week, I will begin testing on a small scale basis, one report at a time.  This phase will be critical in also charting the business processes, specifically in the mobile reporting.  I am somewhat confident, what can I say the whole dataset idea was a kick in the ego, that once completed the export should progress much more quickly.  As the data mapping will be complete I will only need to focus on the means to transform the relational data to the xml hierarchical structure.

I am willing to share all work and code so if you provide an email I will forward and get you what I have.