Exporting sp result to flat file using XML schema

Hi there,

To put in context, we are using BizTalk to get the result from a stored proc and export it to a text file using a XML schema (XSD). The XSD includes formatting info such as the justification, padding, etc. We are moving this process to SSIS and we want to reuse the BizTalk schemas.

Is it possible?

Thanks
Frantz

Just curious, why the move from BizTalk to SSIS? They each have their strengths, and I think for what you are doing, BizTalk is the way to go.|||

Phil, the decision to move from BizTalk to SSIS has been taken at an architectural level.
I think it's about performance issues. BizTalk generates XML during the process taking huge amounts of resources. I'm not sure...

Whether it should be done in SSIS or BizTalk... I think SSIS is way more faster at exporting data to a text file. The sp generates the data, SSIS creates the file. It's pretty straightforward. The only thing is we don't want to re-enter the whole formatting info since it's already in XSD files and wondering if it can be reused.

Thanks

|||I can't speak for BizTalk because I don't support that product, but I thought that is what it is designed for -- to move files around, among other things.

SSIS is great for working on the data as it passes through. Have you participated in the BizTalk forums at all? I'm not saying that one way or another is better, it's just that I think you'll be doing plenty of rework in moving exclusively to SSIS.|||

I have no experience with BizTalk at all...

I have already re-wrote a module initially created in BizTalk. The guys said it would take dozens of minutes for the process to complete and with DTS it generates the same files in a matter of minutes. The resulting flat files are up to 100MB so in XML format it was something like twice the size.

I don't know if it was bad design but they are experiencing really bad performance with BizTalk and their decision is already made...
Thanks anyways.

|||Why XML? Is the next system expecting an XML file? That seems to be an issue, to me, but again, it's the cards you've been dealt.|||

Phil Brammer wrote:

Why XML? Is the next system expecting an XML file? That seems to be an issue, to me, but again, it's the cards you've been dealt.

I have no idea! They're telling me it has to go through XML because it's the only way BizTalk can communicate between systems. I have never used BizTalk, I don't know if they have it completly wrong... I couldn't tell.

|||

fleo wrote:

I have no idea! They're telling me it has to go through XML because it's the only way BizTalk can communicate between systems. I have never used BizTalk, I don't know if they have it completly wrong... I couldn't tell.

They are partially right. The output of a BizTalk message is XML. If the destination system can't handle that XML file, BizTalk will have to convert it. No biggie. But they should be creating an output file appropriate for your use, I would believe. Again though, the performance issues are probably a question for the BizTalk forum.|||

Hi Phil,
The only appropriate output file is a flat file. There's no need to go through XML.

Suppose I create the best BizTalk orchestration possible for exporting a set of data to a text file, would it be as fast as a SSIS package?

My guess is that SSIS outperforms BizTalk on the benchmark.

|||

fleo wrote:

Hi Phil,
The only appropriate output file is a flat file. There's no need to go through XML.

Suppose I create the best BizTalk orchestration possible for exporting a set of data to a text file, would it be as fast as a SSIS package?

My guess is that SSIS outperforms BizTalk on the benchmark.

I would hope BizTalk is comparable. It is designed to move data from system to system. SSIS is designed to process data, not necessarily to move it.