.Net Technologies and Data Conversion

[Tim]

I am researching a potential solution for my company. Currently we
convert a new client's dataset into our own data structures using Fox
Pro and then Sql (Our data is in SQL). There are a lot of field
mappings going on in the first part of the conversion. I would like
this to be done through the business analyst and not the developer
since this is cost effective. Here is a basic example of the mapping
would be as follows:

Client Field Our Field
Birth Date ------------- RC_BDate
SSN ---------------- RC_SSN



The next hurdle is the inline functions used in fox. I would need to
store the various functions and display them to the analyst in the
appropriate situations. For example

Client Field Our Field Function Needed
Birth Date ------------- RC_BDate Convert date format
(‘01011989') to Sql DateTime
SSN ---------------- RC_SSN
ClaimTypeCode ------------ RC_Claim_Type_Lc_ID Convert Client
Code into internal codes (Generic)


Once I have all the relationships mapped (fields mappings with the
functions needed to manipulate the data) I need to dynamically build a
sql script to convert the client data into another table. I want this
all done within the application.

My questions are:
1.) How can visual studio .net help me with this solution?
2.) How do I utilize ADO.Net to make my life easier and maintainable?
3.) Windows App or Web?
4.) Would Web Services be useful?
5.) Is building the Sql script and executing it in Sql 2000 a good
approach?


Tim

 

[Nick Malik]

First off, take a look at Biztalk. The BTS mapper is very good at the kind
of row-by-row data conversions that you discuss.
Analysts can use (and modify) biztalk maps. The reduces maintenance costs.

The "hard" logic can be placed into a small set of .NET components that you
can wire in to Biztalk, and make available to your analyst. The would be
"protected" from the hard stuff.

Hope this helps,
--- Nick Malik
Biztalk Bum