locked
Should not Generating Output Files when we dont have data in sql tables or sql db RRS feed

  • Question

  • Hi...

    I need a logic to be implement in my orchestration when we dont have data in sql db tables the files should not generate as empty files in output in biztalk. If we have data only we should generate files as output.

    in simple...i should not generate empty files in biztalk, when there is no data in sql tables....

    please tell me the logic which will help to me.....to stop getting empty files.

    thanks in advance

    Thursday, August 14, 2014 9:33 AM

Answers

  • refer http://msdn.microsoft.com/en-us/library/ms256103(v=vs.110).aspx for how the count function works.

    Declare an orchestration variable recount as type int

    When you get the response from the WCF-SQL Receive, you can use the recount = xpath(ResponseMessage.parameters, "count(<xpath for your record node>)")

    After this add a decide shape and add the condition "recount != 0" as expression. In that branch add your send port.

    Regards.

    • Marked as answer by Babloo K Tuesday, August 19, 2014 4:25 AM
    Thursday, August 14, 2014 11:07 AM
  • If you're using WCF-SQL then you do not have an option to delete polled data (this was an option available in the legacy SQL Adapter). To handle this scenario you should write a stored procedure to get the data for you. In doing so the SP should delete the data from the table.

    Then you should use the SP to retrieve the data and insert into other tables.

    Regards.

    • Marked as answer by Babloo K Tuesday, August 19, 2014 4:25 AM
    Monday, August 18, 2014 11:39 AM
  • I suspect some issues with your XPATH, if possible can you post a complete sample message or better still you can use the Online XPATH tester @http://www.xpathtester.com/xpath to validate the path.

    as a suggestion I always use the shorter version of the XPATH where I generally omit the namespace. So your xpath reduced to

    (/*[local-name()='SelectResponse']/*[local-name()='SelectResult']/*[local-name()='RH_MIDDLEWARE_CAFM']

    would work just as well (as long as it is correct).

    Regards.

    • Marked as answer by Babloo K Friday, September 19, 2014 8:59 AM
    Friday, September 19, 2014 7:51 AM

All replies

  • In the orchestration how do you come to know that there is no data in SQL?

    If your orchestration is using a SP to query data then in the result, use XPATH and count() to identify if you have results or not. Use a decide shape and put the send port in the branch where the count of records is NOT 0.

    Regards.

    Thursday, August 14, 2014 10:50 AM
  • thanks for your reply....

    can you guide me detailly how to write in decide shape for this table.....i want just......if count is 0 file should not be generated and if count is >0 file should be generated.....

    thanks in advance

    Thursday, August 14, 2014 10:58 AM
  • refer http://msdn.microsoft.com/en-us/library/ms256103(v=vs.110).aspx for how the count function works.

    Declare an orchestration variable recount as type int

    When you get the response from the WCF-SQL Receive, you can use the recount = xpath(ResponseMessage.parameters, "count(<xpath for your record node>)")

    After this add a decide shape and add the condition "recount != 0" as expression. In that branch add your send port.

    Regards.

    • Marked as answer by Babloo K Tuesday, August 19, 2014 4:25 AM
    Thursday, August 14, 2014 11:07 AM
  • First, what exactly do you mean by empty file?  The File Adapter actually won't write an empty file.

    Second, what exactly do you mean by "dont have data in sql db tables"?  Are you Polling?  The WCF SQL Adapter will not create a message for an empty result set.

    If you are querying the database with a Two-Way Operation, you have to explain exactly what no data is in this case.

    Thursday, August 14, 2014 12:02 PM
    Moderator
  • Thanks a lot for your reply shanky.....

    one more problem.....the data is generated in one (RH_CAFM)table after running biztalk the data will pushed into two tables Workorders and workordersunapproved tables....

    here there are four records in First table....after biztalk triggering the all 4  records are generated in two tables...but the data is not deleting after moving those records into two tables....

    next time when i trigger in biztalk in the files we are getting the same output which are stayed in first table....

    can you suggest me something about this...

    thanks in advance shanky......

    Monday, August 18, 2014 11:04 AM
  • If you're using WCF-SQL then you do not have an option to delete polled data (this was an option available in the legacy SQL Adapter). To handle this scenario you should write a stored procedure to get the data for you. In doing so the SP should delete the data from the table.

    Then you should use the SP to retrieve the data and insert into other tables.

    Regards.

    • Marked as answer by Babloo K Tuesday, August 19, 2014 4:25 AM
    Monday, August 18, 2014 11:39 AM
  • Thank You Shanky........
    Tuesday, August 19, 2014 4:26 AM
  • Hi Shanky..

    I need help on this code "recount = xpath(ResponseMessage.parameters, "count(<xpath for your record node>)")"

    this code i have to write in EXPRESSION shape before DECIDE shape right????

    here response message means.... what we have declared as schema message in orchestration that schema name should be given?

    again can you be clear on parameters... what parameters????

    Thanks In Advance
    Friday, September 19, 2014 2:54 AM
  • Hi Johns...

    here we have Given FILE adapter for send and recieve ports.... but we are generating empty files.... if we dont have data in SQL Table....

    here we have used  WCF SQL and  SELECT Operation for generating schema and Data is polling.......

    we have used WCF CUSTOM Adapter in the ports. After select operation response comes from the SQL Table if we dont have data it is triggering the other part of orchestration and generating the empty file in the output...

    Friday, September 19, 2014 4:13 AM
  • Yes the expression has to be entered in an expression shape before the decide.

    The "ResponseMessage" is the BizTalk Message that has been received by the orchestration. If this Message is defined as a multi-part type and the name of the body part is parameters then in the XPATH you'd use "ResponseMessage.parameters", if however, ResponseMessage directly defines the message then you'd use only "ResponseMessage".

    Regards.

    Friday, September 19, 2014 6:17 AM
  • hi Shanky.

    I have tried with your code in my project as "(Recount = xpath(Msg_CAFM_Table_response, "count(/*[local-name()='SelectResponse' and namespace-uri()='http://schemas.microsoft.com/Sql/2008/05/TableOp/dbo/RH_MIDDLEWARE_CAFM']/*[local-name()='SelectResult' and namespace-uri()='http://schemas.microsoft.com/Sql/2008/05/TableOp/dbo/RH_MIDDLEWARE_CAFM']/*[local-name()='RH_MIDDLEWARE_CAFM' and namespace-uri()='http://schemas.microsoft.com/Sql/2008/05/Types/Tables/dbo'])");)" but  still it is  generating empty files...

    please help me.....thanks in advance


    • Edited by Babloo K Friday, September 19, 2014 7:16 AM
    Friday, September 19, 2014 7:14 AM
  • I suspect some issues with your XPATH, if possible can you post a complete sample message or better still you can use the Online XPATH tester @http://www.xpathtester.com/xpath to validate the path.

    as a suggestion I always use the shorter version of the XPATH where I generally omit the namespace. So your xpath reduced to

    (/*[local-name()='SelectResponse']/*[local-name()='SelectResult']/*[local-name()='RH_MIDDLEWARE_CAFM']

    would work just as well (as long as it is correct).

    Regards.

    • Marked as answer by Babloo K Friday, September 19, 2014 8:59 AM
    Friday, September 19, 2014 7:51 AM
  • If you're getting an empty response then of course the XPATH will return 0. So try and paste the received message in this forum.

    Regards.

    Friday, September 19, 2014 8:38 AM
  • Many Manyy Thanx Shanky...Finally executed........

    i need you support for further in Biztalk....

    Friday, September 19, 2014 8:50 AM