Currently, our company is using BizTalk to extract data from MSSQL database and exporting to some type of text file that is in turn used to import into a mainframe data store.

I'm wasn't involved in the original project but have recently been dragged in to find alternative, more flexible solution. I have been asked to look into possibility of using MS Data Transformation Services (DTS) for replacement system.

Only experience I have of using DTS is to do simple import/export of data and schema to/from different MSSQL servers. I've read some papers on using DTS and creating packages etc. and it appears relatively straighforward but can it handle more complex business logic easily?

If anyone have any past experience to share in regards to using DTS in a similar projects you've worked on I've appreciate it. TIA.