- This topic has 11 replies, 1 voice, and was last updated 9 years, 3 months ago by
.
-
Posts
-
-
I was directed to this site as somewhere I can find some answers regarding BizTalk and Spread for BizTalk (by FarPoint themselves).
I am using BizTalk 2009 and Spread for BizTalk to import and process Excel files. I created the schema and pipeline and published the project to BizTalk. I then created a receive port and send port. To test, the send port simply saves the messages to another directory as XML. Everything worked great. My test file has 10 data rows, the resulting XML document has 10 detail records.
My next step, which is my real need, is to create a send port which sends the data into a database (SQL Server). Thus, I went back, created a schema for a stored procedure, and then created a map which maps my schema to my stored proc schema. I then republished to BizTalk.
I then configured a second send port (by first importing the binding) as follows:
- Type = WCF-Custom
- Send Pipeline = PassThruTransmit
- ReceivePipeline = CrawfordReceivePL
- Filter = BTS.receiverPortName -> My recieve Port Name
- Outbound Map = My source schema
When I now process the XLS file, I receive the following error:
There was a failure executing the response(receive) pipeline: “CrawfordTestProject.CrawfordReceivePL, CrawfordTestProject, Version=1.0.0.0, Culture=neutral, PublicKeyToken=2a1607f4a050e392” Source: “Spreadsheet disassembler (Spread for BizTalk)” Send Port: “WcfSendPort_SqlAdapterBinding_TypedProcedures_dbo_CrawfordTest” URI: “mssql://.//Claims_Risk_Management_Staging?InboundId=CrawfordConnection” Reason: Parsing the incoming stream failed because Spread for BizTalk was unable to find a schema that matched the format of the incoming stream.
I have tried adding additional Filters (using BTS.MessageType) but I still receive the same error. I’m hoping someone can point me in the right direction. I have a handful of XLS files I need to run through BizTalk.
Thanks, Scott
-
Hi Scott,
A couple of questions for you:
1) Did you validate and test the map that maps your Spread schema to your Stored Procedure schema, to make sure that the output is what you expect?
2) The error you are getting is from the Receive Pipeline that is disassembling the message from your spreadsheet into XML format. After you deployed your map (assuming it is in a separate project), did you check to see if your Spread schema is still published in the BizTalk application? Check the Global Cache Assembly (GAC) to make sure that both your map and schema assemblies are still there.
I don’t believe the problem you are facing is specific to FarPoint Spread for BizTalk, and is related to deployment and configuration issues in BizTalk. Let me know how you make out on this one.
Daniel.
-
Hi Daniel,
Thank you for your response.
When I validate the mapping (right mouse click on the .btm and select Validate Map) I get the following:
Output validation error: The ‘http://schemas.microsoft.com/Sql/2008/05/TypedProcedures/dbo:line_of_coverage_type_code_value’ element is invalid – The value ‘cov_major_cov_major_0’ is invalid according to its datatype ‘String’ – The actual length is greater than the MaxLength value.
Output validation error: The ‘http://schemas.microsoft.com/Sql/2008/05/TypedProcedures/dbo:claim_nbr’ element is invalid – The value ‘id_claim_no_id_claim_no_0’ is invalid according to its datatype ‘String’ – The actual length is greater than the MaxLength value.
Output validation error: The ‘http://schemas.microsoft.com/Sql/2008/05/TypedProcedures/dbo:claim_status_code_value’ element is invalid – The value ‘status_type_status_type_0’ is invalid according to its datatype ‘String’ – The actual length is greater than the MaxLength value.
Output validation error: The ‘http://schemas.microsoft.com/Sql/2008/05/TypedProcedures/dbo:incident_state_code_value’ element is invalid – The value ‘id_state_id_state_0’ is invalid according to its datatype ‘String’ – The actual length is greater than the MaxLength value.
Why I’m not sure why I would get these errors…
Scott
-
Sorry, not VALIDATE the map, but I get those errors when I TEST the map (right mouse click on the .btm and select Validate Map).
-
-
OK, looking at the inputfile.xml from testing the map, for it doesn’t look like it is pulling in the values:
<ns0:id_id>10</ns0:id_id><ns0:id_client_id_client>10</ns0:id_client_id_client><ns0:id_claim_no_id_claim_no>id_claim_no_id_claim_no_0</ns0:id_claim_no_id_claim_no><ns0:id_claimid_id_claimid>id_claimid_id_claimid_0</ns0:id_claimid_id_claimid><ns0:id_brfile_id_brfile>id_brfile_id_brfile_0</ns0:id_brfile_id_brfile><ns0:clmt_name_clmt_name>clmt_name_clmt_name_0</ns0:clmt_name_clmt_name><ns0:clmt_age_clmt_age>10</ns0:clmt_age_clmt_age><ns0:clmt_wage_clmt_wage>10</ns0:clmt_wage_clmt_wage><ns0:clmt_len_clmt_len>10</ns0:clmt_len_clmt_len><ns0:cov_major_cov_major>cov_major_cov_major_0</ns0:cov_major_cov_major><ns0:id_occur_date_id_occur_date>1999-05-31T13:20:00.000-05:00</ns0:id_occur_date_id_occur_date><ns0:pyear_pyear>pyear_pyear_0</ns0:pyear_pyear><ns0:id_close_date_id_close_date>1999-05-31T13:20:00.000-05:00</ns0:id_close_date_id_close_date><ns0:id_reopen_date_id_reopen_date>1999-05-31T13:20:00.000-05:00</ns0:id_reopen_date_id_reopen_date><ns0:id_report_date_id_report_date>1999-05-31T13:20:00.000-05:00</ns0:id_report_date_id_report_date><ns0:status_type_status_type>status_type_status_type_0</ns0:status_type_status_type><ns0:ctl_emp_name_ctl_emp_name>ctl_emp_name_ctl_emp_name_0</ns0:ctl_emp_name_ctl_emp_name><ns0:fin_ind_tot_fin_ind_tot>10</ns0:fin_ind_tot_fin_ind_tot><ns0:fin_ind_pd_fin_ind_pd>10</ns0:fin_ind_pd_fin_ind_pd><ns0:fin_ind_os_fin_ind_os>10</ns0:fin_ind_os_fin_ind_os><ns0:fin_med_tot_fin_med_tot>10</ns0:fin_med_tot_fin_med_tot><ns0:fin_med_pd_fin_med_pd>10</ns0:fin_med_pd_fin_med_pd><ns0:fin_med_os_fin_med_os>10</ns0:fin_med_os_fin_med_os><ns0:fin_exp_tot_fin_exp_tot>10</ns0:fin_exp_tot_fin_exp_tot><ns0:fin_exp_pd_fin_exp_pd>10</ns0:fin_exp_pd_fin_exp_pd><ns0:fin_exp_os_fin_exp_os>10</ns0:fin_exp_os_fin_exp_os><ns0:fin_slv_pd_fin_slv_pd>10</ns0:fin_slv_pd_fin_slv_pd><ns0:fin_sub_pd_fin_sub_pd>10</ns0:fin_sub_pd_fin_sub_pd><ns0:fin_rec_tot_fin_rec_tot>10</ns0:fin_rec_tot_fin_rec_tot><ns0:fin_tot_tot_fin_tot_tot>10</ns0:fin_tot_tot_fin_tot_tot><ns0:fin_tot_pd_fin_tot_pd>10</ns0:fin_tot_pd_fin_tot_pd><ns0:fin_tot_os_fin_tot_os>10</ns0:fin_tot_os_fin_tot_os><ns0:loc_lvl1_loc_lvl1>loc_lvl1_loc_lvl1_0</ns0:loc_lvl1_loc_lvl1><ns0:loc_lvl1_t_loc_lvl1_t>loc_lvl1_t_loc_lvl1_t_0</ns0:loc_lvl1_t_loc_lvl1_t><ns0:id_state_id_state>id_state_id_state_0</ns0:id_state_id_state><ns0:ctl_cause_ctl_cause>ctl_cause_ctl_cause_0</ns0:ctl_cause_ctl_cause><ns0:ctl_cause_t_ctl_cause_t>ctl_cause_t_ctl_cause_t_0</ns0:ctl_cause_t_ctl_cause_t><ns0:ctl_nature_ctl_nature>ctl_nature_ctl_nature_0</ns0:ctl_nature_ctl_nature></ns0:Sheet1_Record>Which I don’t understand because when I validate the file schema instance, the resulting XML looks fine.Thoughts?-
Hi Scott,
Sorry for not responding sooner but it’s a hectic week for me at work…
A good sample of input XML file for your map to test and validate would be to get the XML message coming from your Initial Receive port (after your original Excel data has been parsed into XML format by the Spread pipeline). You can generate such a message by placing a Send port in your Orchestration after the initial Receive port and declare its type of your Spread schema, build and deploy your map project, then in the Administration console, create a new port of with a Transport Type of File and a Send Pipeline type of XMLTransmit.
Daniel.
-
HI Daniel,
I do appreciate your responses.
This is long before I even get to BizTalk. I have spent they walking through this step by step to see where it might be, so let me see if I can explain my process.
1) Using the Spreadsheet schema wizard, create a schema based on the .xls file. Once created, I validate the schema then validate the instance. Both validate successfully. The resulting xml looks like this:
<id_id>2</id_id>
<id_client_id_client>15113</id_client_id_client><id_claim_no_id_claim_no>0105-073757-001</id_claim_no_id_claim_no><id_claimid_id_claimid>SIS 151130105073757001</id_claimid_id_claimid><id_brfile_id_brfile>0105-073757</id_brfile_id_brfile><clmt_name_clmt_name>ANDRADE REIGOBER</clmt_name_clmt_name><clmt_age_clmt_age>23</clmt_age_clmt_age><clmt_wage_clmt_wage>520</clmt_wage_clmt_wage><clmt_len_clmt_len>12</clmt_len_clmt_len><cov_major_cov_major>WC</cov_major_cov_major>2) Create a pipeline, using the Spreadhseet disassemblere and setting the Document Schema to the schema I created in step 1.
3) Add an Adapter Service (Generated Item) that creates a schema for my insert stored procedure.
4) Add a Map. The source schema is my file schema and the Destination schema is the stored procedure schema. I then map a few string columns and save the map.
5) I then validate the map. This is successful.
6) I then TEST the map. This fails. I look at the file used for the test map, and it looks like this:
<ns0:id_id>10</ns0:id_id><ns0:id_client_id_client>10</ns0:id_client_id_client><ns0:id_claim_no_id_claim_no>id_claim_no_id_claim_no_0</ns0:id_claim_no_id_claim_no><ns0:id_claimid_id_claimid>id_claimid_id_claimid_0</ns0:id_claimid_id_claimid><ns0:id_brfile_id_brfile>id_brfile_id_brfile_0</ns0:id_brfile_id_brfile><ns0:clmt_name_clmt_name>clmt_name_clmt_name_0</ns0:clmt_name_clmt_name><ns0:clmt_age_clmt_age>10</ns0:clmt_age_clmt_age><ns0:clmt_wage_clmt_wage>10</ns0:clmt_wage_clmt_wage><ns0:clmt_len_clmt_len>10</ns0:clmt_len_clmt_len><ns0:cov_major_cov_major>cov_major_cov_major_0</ns0:cov_major_cov_major>Why are the values for the elements the NAME of the element and not the actual values? This is why I am getting the error i received earlier:
Output validation error: The ‘http://schemas.microsoft.com/Sql/2008/05/TypedProcedures/dbo:line_of_coverage_type_code_value’ element is invalid – The value ‘cov_major_cov_major_0’ is invalid according to its datatype ‘String’ – The actual length is greater than the MaxLength value.
cov_major is only 10 characters long. But because it is bringing in the element name as the value, it is failing.
I am lost at this point. BizTalk isn’t even in the picture yet and i need to find out why the map test is failing, or more specifically, why it is using the element names for the string values and the number 10 for numerical values.
-
-
Ok, the image did not show up in my previous post, so click on my avatar and look for it in My Files link, the name of the image is Map.png
I hope this helps you troubleshoot your Map testing and validation…
-
OK, that worked. I can now test the map. I have now published it BizTalk, created my send and receive ports, but i’m back to the same error when I try and run a file through:
There was a failure executing the response(receive) pipeline: “CrawfordTestProject.ReceivePipeline1, CrawfordTestProject, Version=1.0.0.0, Culture=neutral, PublicKeyToken=2a1607f4a050e392” Source: “Spreadsheet disassembler (Spread for BizTalk)” Send Port: “WcfSendPort_SqlAdapterBinding_TypedProcedures_dbo_CrawCustom” URI: “mssql://.//Claims_Risk_Management_Staging?InboundId=CrawConnection” Reason: Parsing the incoming stream failed because Spread for BizTalk was unable to find a schema that matched the format of the incoming stream.
I cannot seem to find why I am getting this error. This can’t be THAT difficult…
-
Hi Scott,
Use the BizTalk Administration Console to troubleshoot why this error is happening, you should see a suspended instance of your Orchestration, and look for the message type schema that is causing this to fail.
The error message “Send Port: “WcfSendPort_SqlAdapterBinding_TypedProcedures_dbo_CrawCustom” URI: “mssql://.//Claims_Risk_Management_Staging?InboundId=CrawConnection” should give you a clue: are you missing the schema that binds to your sql stored procedure (generated by the WCF adapter)?
Daniel.
-
-
-
-
-
-
-
-
-
-
- The forum ‘BizTalk 2004 – BizTalk 2010’ is closed to new topics and replies.