I am new to Biztalkl but have years of both EDI & ETL experience in my last position. I have been taked with setting up a BizTalk job to process an inbound text file as follows:
1. Convert .txt file to .xml file (this is done)
2. Validate each record (line) of file is valid by checking one field against a table
3. "Throw out" bad records whose value in the aforementioned one field doesn't match
4. Send "good" records to temp/holding table
5. Run query against data in temp/holding table to confirm if patient first and last name match.
6. If patient name matches, add data to system
7. If patient name does not match, add as new
Some details on the file are: First and Last name only--no SSN, no DOB, no address will be given. Also, once we have gotten through the patient matching, the data in the file will be inserted into 3 other tables. lastly, The file has perhaps 12 columns of data total.
Right now I am considering calling a SQL query to validate the records (Step 2) and also do the patient queries, lookups and inserts (Steps 5-7).
What I am looking for from you guys is confirmation my logic is sound in using SQL, or would it be faster to perform these steps in BizTalk ??
Thanks in advance--Tom