- This topic has 2 replies, 1 voice, and was last updated 9 years, 3 months ago by
.
-
Posts
-
-
Hi,
I am working on a project that receives a flat file containing thousands of records. I use a stored procedure to do some data validation to the file which assigns certain error codes if any data issues were discovered. Regardless of the outcome of the validation, the data is then sent to a sql server database table. I have no problems with this, but wanted to provide some background info.
Once the table is loaded, I have a 2nd orchestration that uses a stored procedure to determine how many records on this table contain error codes. A file is created that only contains a count of records that contained errors. If the count in this file is greater than 0, I want to pull all the data back out of the table, including the error codes, and send it back to the customer in a flat file format. If the counter = 0, I want to be able to send all the data from this table to another table where further processing outside of BizTalk will take place.
I’ve tried using a decide shape to determine which path to take, based on the error count, but I’m running into all kinds of problems. If the error count > 0, I tried starting a 3rd orchestration that uses a stored procedure to pull all of the data out of the table to send back to the customer, having a receive shape at the beginning of the 3rd orchestration. However, BizTalk does not allow that, since the 2nd orchestration is actually starting it. I also tried to just read the data from the table, and then use a distinguished field on the error code to find out if there was anything in that field, but BizTalk doesn’t like that either, because the error code field is not unique – it’s in a repeating record.
Can anyone provide suggestions on how to accomplish this? I will appreciate any help you can provide.
Thanks!
Linda -
What is the reason for intermediate database tables.
I would tend to remove the intermediate database table, simply receive the file, validate the content and map into a good data message and bad data message and send these to the appropriate locations. All the messages are persisted in the MessageBoxDb. You can use message tracking or BAM to provide visibility into the process.
-
Hi, Greg –
The first table (the intermediate table) contains only var char data types for the data elements. This is so we can retain the customer’s data just as it is sent to us and error codes are assigned as appropriate when invalid data is sent. The validation is handled in a stored procedure that is used to load data to the SQL server database table. We do not want to split up the file the customer sends us according to good & bad records. If just one record in the file contains invalid or incomplete data, we want to send the entire file back to the customer with the error codes. If all records in the file contain complete and valid data, then the data needs to be loaded to another table where processing outside of BizTalk will occur. This table has different data types (dates, numeric, etc…).
Once the validation on the incoming file has been completed, I need to read this intermediate table to see if any records contain errors. If any errors exists, the entire data on this table needs to be sent back to the customer. If all records are free from errors, then the data needs to be sent to the other table.
I have tried both promoting the error code element and making it a distinguished field, so that I can determine what to do with the data, but I receive the following message: “This node can occur potentially multiple times in the instance document. Only nodes which are guaranteed to be unique can be promoted.”
The only thing I could think of to do next was to just count the number of records in the intermediate table that contained a value in the error code column. If the value was greater than 0, then I want to send the file back to the customer with the error codes attached, otherwise, send the data to the other table. This scenario requires me to have a receive shape to count the number of records with error codes, and then a second receive shape that reads the data from the intermediate table to either send data back to the customer if any of it is bad, or send the data on the another table. This is where I get into trouble. I can get the count of the number of records that contained errors, but I don’t know how to proceed from there without having a second receive shape.
I have also tried starting a second orchestration that begins with a receive shape to read the data from the intermediate table, but BizTalk throws an error that the orchestration that is being called can’t begin with a receive shape.
I’m pretty new to BizTalk, so I haven’t figured out all the best practices for getting BizTalk to do what I need it to do. Thanks for your patience and your help!
Linda
-
-
- The forum ‘BizTalk 2004 – BizTalk 2010’ is closed to new topics and replies.