I am a newbie at biztalk and this is my first assigned project. I need your recommendation on the current project I am handling. The project will unzip file from the ftp site that contains flat files and pdf files. Flat files uses only one schema, only with different order numbers . Through this order numbers, project will query if this order number exist in SQL database, if it exist, it will pick up the pdf file that has the same filename as the flatfile and upload it in the database. Otherwise, will have to send the list of failed orders within the day via email. Report should contains order details which is in the flat file plus the file name of the file that failed...

Where I am now at the project?

I created a custom pipeline that will unzip file from the ftp site and save it on a file location, seperated txt and pdf files into different folders.

I also created a receive pipeline that will dissemble the flat files.. and send the xml files into another file location.. let's say xml folder.. 

Since I don't have the stored proc yet..  what I did was tried to combined the received messages into one file and send it to another file location by using a flat file assembler in a send pipeling and save it as report.txt.. I'm assuming I can use this later.. but the problem I encountered here is I don't know how to get the file name of the order that failed and assign it to an element in a report schema I created...I tried promoting filename element and use bts.global...filename.. but It's not working..

where should I go from here.. 

What is the best approach I could use on this project.. I can't figure out how I can pick up the pdf file and upload it via sql.. then get all orders that failed to upload within the day and send it via email...

Appreciate any help from you guys!

Q.. Are there multiple orders in the falt file. ? will each order in a specific flat file will have the same pdf assigned to it.

like  Flat file 1 .txt

      X123,Order1,Test

      X124,Order2,Test2

What should happen if Order1 exists and Order2 not, adn what if both doesnot exist in SQL ??

Need these details to understand ur process.

there's only one order inside each flat file.. and each flat file have a pdf file which have the same file name.. thanks

Create a process around ur pdf file something like, a process that takes in filename and returns u the pdf file associated with it. (Call it A)

After u have unzipped the files in folder, ur orch can be bound to ur flat file location which would then start the process.

In Orch read the file name with msg(FILE.ReceivedFileName), exract the filename.

Map ur order schame to sp schema to call sql result. Based on the result call ur service A to get the pdf and then send it to sql using sp2. If sql result is false u can send the notification.

If u want to have an aggregated notification email, then u will have to implement an aggregator pattern around ur email notification.

Hi Annabelleee,

I have couple of questions, on the base of that we can give you proper approach.

Q1. Is Flat file contains any value or it just contain only order number?

Q2. Upload PDF on database, do you want to upload the data from pdf file or just only the file name?

thanks.. will check on this first but still open for other options..