Recently a client submitted the requirement of matching an incoming SSN to a Member ID on a 834 EDI document. They also requested we create a text file to report any members that did not have a match. Our solution was to create an orchestration to handle all of the requirements.

The first task was to create a map for the source 834 to the destination schema. Since this is not the focus of this blog post, I will not go into any details of the mapping, but focus on the requirement in question.

In the map we needed to do the matching of the SSN to the Member ID. This was handled by using an external assembly that calls a stored procedure. The stored procedure does the entire member matching and inserts pertinent data on the member that does not have a member id match. In our scenario, if a match exists, we return the member ID. If a match does not exist, we return ‘no match’, and insert the member’s SSN and Birthdate into a table that contains non-matches.

For the . txt file output we also used an external assembly to call a stored procedure to create our file. This stored procedure accepts 1 input of our filename and uses the “bcp” utility http://msdn.microsoft.com/en-us/library/ms162802.aspx to copy our table information into a .txt file.

Our orchestration ended up looking something like this: