We will have about 200 files (csv, excel, PDF, screen scrape) that all need to go into a SQL db. So there will be a unique procedure for most data sources
Only 2 ideas we have so far are:
Write code to programmatically load each data source in as needed and write code to insert as needed (this is the simple way but maybe most time consuming)
Write an XML map for each file that maps column from the source to columns/tables of the destination SQL DB.. But then writing code to interpret this custom XML mapping file could get complex?
Any other tools or methods we should consider? I though maybe SSIS could help somehow ? This seems to be the type of project BizTalk was made for right? But that is too expensive..
As Pondlife mentioned, in real world solutions, programmatic solutions usually become more and more difficult to maintain and support as the full complexity of the requirements is uncovered. This is often not obvious up front.
I would choose a good ETL tool - SSIS is usually the best choice at present on the balance of typical criteria. Then you need to budget an amount of man-days to work through each input. Probably the quickest you will achieve is 0.5 man-days per file (including design, build and unit testing) for a very simple input.
You can save some time by copying your first package as a starting point for the others.
With "raw" inputs like this I typically start each package by just loading the data unaltered into a Staging table. At this point I load every column as unicode text. Then subsequent Data Flows or packages can pick that data up and deliver it. This approach really speeds debugging, testing and auditing - once you trust your file load you can query the Staging table with SQL.
BTW the SSIS package is in fact an XML document that describes the input, transformation and output requirements - similar to your point 2.