One of the coolest features of DTS is that it can interface with any OLEDB-compatible data source. On the other hand, you need to know which driver will perform best for each particular data source. The complication with various data sources also comes from the fact that each data source provides data in a different shape. Therefore, I had to run different transformation code for each provider, and come up with a custom mapping of columns. Most of the time, I did not know how many columns there were in the initial source file or what the column names would be. Internally, our column names were F1, F2, F3, and so on because any column could contain any type of data. We had a separate table that contained meta data about the data in this table, so when a user wanted a last name field, I looked to see what column this was kept in, and presented them with the correct data.
One consequence was that much of the stored procedure code was generated and executed dynamically. I also let the user change a data type of any field to accommodate any specific needs. For example, if a user originally had a date field in a character format and then wanted to do any kind of date filtering, they could change the data type of that field. I checked to ensure that all the data in that column fit into the new data type, and if it did, I altered the table.
Another problem I encountered was with dBase files. I initially used the Microsoft Jet dBase driver to access all the dBase files. When I tried to manipulate the format of the file, I received some very user-unfriendly messages that told me that I couldn't perform the operation. I found that I had to use the MS Visual Fox Pro driver to do the file-format manipulations (adding columns, and so on), so I had to use different drivers, depending on what I was doing with the file.