Currently, there are two packages as I've read it. The first package calls a complex stored procedure which does bulk of the work to query the data and create an XML output file. Then, second package is called which cranks up the BizTalk Server to "massage" the generated XML file for final output (usually in some form of ASCII for the mainframe). The decision to first create the XML file and then use BizTalk to create the final output was made, as I'm told, for flexibility to change the final output file depending upon each specific vendor's requested format.

Therefore, I'm part of a small team to conduct a feasibility study to determine if DTS or any other solution can handle the creation of different final output files w/o going through BizTalk and the XML overhead.

With my current limited understanding, it appears to me that we should be able to create separate output packages instead of going through XML/BizTalk route. Or if output formats vary widely, we can create a VB app that automate creating/calling DTS packages for increated flexibility.

>>> can it handle more complex business logic easily?
>
>Such as?
>
>-Mike
>
>
>>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.