- This topic has 6 replies, 1 voice, and was last updated 8 years, 4 months ago by
.
-
Posts
-
-
Hey everybody,
first of all here's what i've got:
I'm reading a flatfile into BTS2006 which works just fine. Now I wrote a SPROC which compares the data from the flatfile with Values from a MS SQL2005 DB.
Currently I use the DBFunctoids from http://blogs.msdn.com/ebattalio/archive/2006/02/16/DBFunktoids.aspx to call the storedProcedure which also works.
Now i want to store the response in different tables according to a value returned in the SPROC.
What do you guys suggest is a good solution to do this?
-
Hi Anonymous,
Maybe you should just load a table with the file and do all the work inside the sproc. No reason to fracture all this logic back and forth between the database and the map, is there?
– weak architect
-
hmm sounds good .. the only problem would be that the functoid only takes 9 parameteres and I got at least 12 …. popably an own functoid would be the best solution?
-
-
I would suggest using an orchestration to help with the logic instead of trying to return data using the map. Of course, I'm not saying it won't work, but I've run into similar issues. I created a sproc and send the data out a port then use the response(s) for other decisions. It's easier to regenerate a schema if things change than to rewrite the custom functoid everytime you need more parameters IMHO.
If you write the functoid, you should look at the performance differences and make an educated decision. I think it makes sense to do simple lookups in the map but leave wholesale data exchange to a request-response port. Then an orchestration can string it together.
-
BizTron – He has to take records from a file, compare them with values in a table and move the results to another table.
It appears that the file data only has to get into the database and then all of the compare and table load can be done there.
Wouldn't it be easier to DTS the file in to SQL Server and then trigger a sproc to do the compare with the first table and the insert into the second table?
-wa
-
Without knowing the entire solution and where BizTalk and SQL Server may fit into the bigger picture, I'm trying to restrict my line of thought to what I've seen and/or how I would approach this from a BizTalk angle. I spend hours and sometimes days try to convince customers NOT to use BizTalk when it makes no sense…especially when I either have to support the effort or build the solution. It ends up being a waste of time, effort and money…so I see where you're coming from.
That said, I've seen DTS used where either it shouldn't be or where it performed poorly when it was not expected to do so. I'm leaving those suggestions and decisions to the architects responsible since they know far more than I about the current project. I like using sprocs for various reasons. It can hinge on where the maintenance falls and the comfort level of the folks responsible at the end of the day. Back to the point, I think, my suggestion…if using BizTalk…is to NOT do table inserts in the map, and use Orchestration for the logic. However, I won't dispute anything you suggest because you have the knowledge to make that assessment, and I'm only looking at BizTalk. Sorry for being long winded.
I hope to see how this is ultimately resolved.
-
-
-
-
-
- The forum ‘BizTalk 2004 – BizTalk 2010’ is closed to new topics and replies.