Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Importing the OFAC SDN list via XML


Importing the OFAC SDN list via XML

Author
Message
Tim OPry
Tim OPry
SSC-Enthusiastic
SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)

Group: General Forum Members
Points: 160 Visits: 247

For those not familiar with the OFAC (Office of Foreign Assets Control), they maintain a list of Specially Designated Nationals (SDN) in ASCII and now XML with an XSD layout that financial institutions check against.

This list is updated aperiodically (I've only seen additions, never deletions or updates).

I am wanting to automate the updating of this list into a SQL table.

To view the XML/XSD go here: http://www.treas.gov/offices/enforcement/ofac/sdn/index.shtml

Unfortunately, they do not provide a webservice type of interface, simply the XSD/XML.

Originally, I manually created the SQL table and then used DTS to import the data. Since they can update the XSD, I was looking to automate this process a bit more. Has anyone already tackled this task in a more automated fashion that would be willing to share their methodology?





Site Owners
Site Owners
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10085 Visits: 1
No one has responded to this topic yet. Even if you don't have a complete answer, the original poster will appreciate any thoughts you have!
pcecereatgmaildotcom
pcecereatgmaildotcom
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 36
my name is Pablo Cecere.
I have to deal with the OFAC list to import it to SQL Server.
If you didnt do it yet, we can work together.
Pablo
Tim OPry
Tim OPry
SSC-Enthusiastic
SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)

Group: General Forum Members
Points: 160 Visits: 247
Pablo,
I posted a query on Guru.com and found someone that had already written a C# app to import the XML data into SQL. They gave me a very reasonable quote and it works very well. For the time it saved, its a no-brainer.

His contact info if you want: Mark Gerlach [mark AT efgtech DOT com]
(modified email for those nasty spam bots)



pcecereatgmaildotcom
pcecereatgmaildotcom
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 36
I tried contacting this guy, but he didnt answer my emails.
Can you try to contact him? Or help me get this ?
Thanks a lot
Pablo

Happy New Year
Tim OPry
Tim OPry
SSC-Enthusiastic
SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)

Group: General Forum Members
Points: 160 Visits: 247
I will send him an email with a link to this forum posting and ask him to contact you.



pcecereatgmaildotcom
pcecereatgmaildotcom
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 36
Tim, after importing the list ...
did you programmed a Stored Procedure or something to match the names?
Do you match exact names, or did something different?

Thanks
Pablo
Happy New Year!!
Tim OPry
Tim OPry
SSC-Enthusiastic
SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)

Group: General Forum Members
Points: 160 Visits: 247
The OFAC data is imported from the XML file the USTD provides to its own database (multiple tables). The import program drops/recreates the tables each time.

The main identity table (sdnentry) has a unique ID that stays the same from version to version from the USTD.

I have a separate table to maintain the one to many relationship between our clients and the potential matches. So each time the OFAC data is updated and we compare against our current client list, we know which ones were potential matches before and only need to check/verify any new matches. I actually run the check once each day as one of our daily processes. This compares any new clients that were added during the day.

As for what I use to compare - the data in the OFAC list is 'ugly' to be polite, so doing a straight string compare would miss anything unless they matched exactly. So, I use a simply SOUNDEX methodology. Nothing special - but a heck of a lot better than what the USTD/SEC websites provide.

To do the SOUNDEX comparison, I use the TSQL DIFFERENCE function with a value of 4. I have found that gets any matches that are close and accounts for the most common english misspellings in names. Its not sophisticated, but it works.

I did find a commercial library (no longer have the name) that worked exceptionally well- but they wanted a huge sum to license (over 25k min) and it was very restrictive in its use.



pcecereatgmaildotcom
pcecereatgmaildotcom
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 36
I am working on the import process. Hopefully finished by tomorrow.
Do you want to see the way I am doing it.
It is a stored procedure that checks if the SDN list has been updated, and the imports the new list if necessary.

Interested?
Tim OPry
Tim OPry
SSC-Enthusiastic
SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)

Group: General Forum Members
Points: 160 Visits: 247
I appreciate the offer, but no need - our methdology is customized to our needs and works well.



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search