Extract Data From A Table & Save As An XML File

Hello Everyone,
I am trying to extract data from from my Hit list and save as a file in XML
format.
I create a stored proc called it as stProblemClients
I wrote: Select * from clients where status = 'Not Dead'
I set a schedule stProblemClients to run daily so I canmonitor my contracted
jobs
I need the output to be saved in an XML format. Are there any small utility
out there? or any way pieces of code I can mimic?
Let me know if you need to resolve a non technical problem.
Grascia.
Vito CorleoneAre you using SQL Server 2005? Did you look at exposing the stored proc as a
webservice?
Or use FOR XML in the database and use Management Studio to take the
generated XML and save it into a file?
Best regards
Michael
"Vito Corleone" <VitoCorleone@.discussions.microsoft.com> wrote in message
news:982EE5DF-F39B-4582-8FB0-BC8FC324EB56@.microsoft.com...
> Hello Everyone,
> I am trying to extract data from from my Hit list and save as a file in
> XML
> format.
> I create a stored proc called it as stProblemClients
> I wrote: Select * from clients where status = 'Not Dead'
> I set a schedule stProblemClients to run daily so I canmonitor my
> contracted
> jobs
> I need the output to be saved in an XML format. Are there any small
> utility
> out there? or any way pieces of code I can mimic?
> Let me know if you need to resolve a non technical problem.
> Grascia.
> Vito Corleone

Extract Data From A Table & Save As An XML File

Hello Everyone,
I am trying to extract data from from my Hit list and save as a file in XML
format.
I create a stored proc called it as stProblemClients
I wrote: Select * from clients where status = 'Not Dead'
I set a schedule stProblemClients to run daily so I canmonitor my contracted
jobs
I need the output to be saved in an XML format. Are there any small utility
out there? or any way pieces of code I can mimic?
Let me know if you need to resolve a non technical problem.
Grascia.
Vito Corleone
Are you using SQL Server 2005? Did you look at exposing the stored proc as a
webservice?
Or use FOR XML in the database and use Management Studio to take the
generated XML and save it into a file?
Best regards
Michael
"Vito Corleone" <VitoCorleone@.discussions.microsoft.com> wrote in message
news:982EE5DF-F39B-4582-8FB0-BC8FC324EB56@.microsoft.com...
> Hello Everyone,
> I am trying to extract data from from my Hit list and save as a file in
> XML
> format.
> I create a stored proc called it as stProblemClients
> I wrote: Select * from clients where status = 'Not Dead'
> I set a schedule stProblemClients to run daily so I canmonitor my
> contracted
> jobs
> I need the output to be saved in an XML format. Are there any small
> utility
> out there? or any way pieces of code I can mimic?
> Let me know if you need to resolve a non technical problem.
> Grascia.
> Vito Corleone
sql

External proc

Hi ,
I have used some of code in my applicatoin with
xp_cmdshell & osql . IF I have to decide not to use xp_cmdshell for security
reasons . is there any other way to do that .(to interact with OS)
Thanks
Aju
Aju wrote:
> Hi ,
> I have used some of code in my applicatoin with
> xp_cmdshell & osql . IF I have to decide not to use xp_cmdshell for
> security reasons . is there any other way to do that .(to interact
> with OS)
> Thanks
> Aju
Once option is to limit the access the SQL Server Service Account has in
the OS to limit any security issues with using xp_cmdshell.
You can create an extended stored procedure and kick that off.
You can create a job in the SQL Server Agent (programmatically) and have
it execute the SQL.
You can access the FileSystemObject COM object using the sp_OA* methods.
What are you doing in the OSQL script? Can this be something you can do
with T-SQL directly?
David Gugick
Imceda Software
www.imceda.com

External proc

Hi ,
I have used some of code in my applicatoin with
xp_cmdshell & osql . IF I have to decide not to use xp_cmdshell for security
reasons . is there any other way to do that .(to interact with OS)
Thanks
AjuAju wrote:
> Hi ,
> I have used some of code in my applicatoin with
> xp_cmdshell & osql . IF I have to decide not to use xp_cmdshell for
> security reasons . is there any other way to do that .(to interact
> with OS)
> Thanks
> Aju
Once option is to limit the access the SQL Server Service Account has in
the OS to limit any security issues with using xp_cmdshell.
You can create an extended stored procedure and kick that off.
You can create a job in the SQL Server Agent (programmatically) and have
it execute the SQL.
You can access the FileSystemObject COM object using the sp_OA* methods.
What are you doing in the OSQL script? Can this be something you can do
with T-SQL directly?
David Gugick
Imceda Software
www.imceda.com

External proc

Hi ,
I have used some of code in my applicatoin with
xp_cmdshell & osql . IF I have to decide not to use xp_cmdshell for security
reasons . is there any other way to do that .(to interact with OS)
Thanks
AjuAju wrote:
> Hi ,
> I have used some of code in my applicatoin with
> xp_cmdshell & osql . IF I have to decide not to use xp_cmdshell for
> security reasons . is there any other way to do that .(to interact
> with OS)
> Thanks
> Aju
Once option is to limit the access the SQL Server Service Account has in
the OS to limit any security issues with using xp_cmdshell.
You can create an extended stored procedure and kick that off.
You can create a job in the SQL Server Agent (programmatically) and have
it execute the SQL.
You can access the FileSystemObject COM object using the sp_OA* methods.
What are you doing in the OSQL script? Can this be something you can do
with T-SQL directly?
David Gugick
Imceda Software
www.imceda.com

Extended Stored Procedure

When I run the below (sp_OACreate) in SQL Query Analyzer it runs fine. When
I add the same to a stored proc my app hangs and I get a timeout.
Any help is greatly appreciated. I really don't want to create a job that
runs this.
EXEC @.hr = sp_OACreate 'MyLocalEXE.Host', @.object OUT, 4
IF @.hr <> 0 BEGIN EXEC sp_raiseoaerror @.object, @.hr RETURN (-1) ENDHi
At a guess as the application hangs it probably means it can't find the
object, which implies it is not being run in the same context and/or possibl
y
a lack of permissions. You also need to be symin to run sp_OACreate.
John
"Mark - HYPERe" wrote:

> When I run the below (sp_OACreate) in SQL Query Analyzer it runs fine. Wh
en
> I add the same to a stored proc my app hangs and I get a timeout.
> Any help is greatly appreciated. I really don't want to create a job that
> runs this.
> EXEC @.hr = sp_OACreate 'MyLocalEXE.Host', @.object OUT, 4
> IF @.hr <> 0 BEGIN EXEC sp_raiseoaerror @.object, @.hr RETURN (-1) END

Extended Stored Proc write to ERRORLOG?

Is there a way to do something like raiserror using the extended stored
procedure API?
srv_sendmsg gets me the equivalent of PRINT but it doesn't get logged
in the SQL Server Logs.
Any suggestions?
Thank you.How about xp_logevent?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<nimai.malle@.gmail.com> wrote in message
news:1146514341.086831.176790@.j73g2000cwa.googlegroups.com...
> Is there a way to do something like raiserror using the extended stored
> procedure API?
> srv_sendmsg gets me the equivalent of PRINT but it doesn't get logged
> in the SQL Server Logs.
> Any suggestions?
> Thank you.
>|||I'm looking for something that I can call from inside my own extended
stored procedure, from C++.
Thanks.

Extended Stored Proc write to ERRORLOG?

Is there a way to do something like raiserror using the extended stored
procedure API?
srv_sendmsg gets me the equivalent of PRINT but it doesn't get logged
in the SQL Server Logs.
Any suggestions?
Thank you.How about xp_logevent?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<nimai.malle@.gmail.com> wrote in message
news:1146514341.086831.176790@.j73g2000cwa.googlegroups.com...
> Is there a way to do something like raiserror using the extended stored
> procedure API?
> srv_sendmsg gets me the equivalent of PRINT but it doesn't get logged
> in the SQL Server Logs.
> Any suggestions?
> Thank you.
>|||I'm looking for something that I can call from inside my own extended
stored procedure, from C++.
Thanks.

Extended Stored Proc & Progress notification

In my extended stored procedure, I'm trying to return rows as data is
available. The client is expecting to use this data to show progress
notification (10%, 20% etc etc).
Even with variations of the srv_describe, srv_sendrow & srv_senddone
I'm not able to get the client to display the data as it becomes
available. Instead, the rows come all together at the end of the call.
Has anyone succeeded in acheiving this? What am I doing wrong?
Any help appreciated,
Kartik
----
Here is a snippet of the code..
...
srv_describe ( This.srvproc_,
1,
"messages",
SRV_NULLTERM,
SRV_TDS_NVARCHAR, // Dest data type.
(DBINT) 512, // Dest data length.
SRV_TDS_NVARCHAR, // Source data type.
(DBINT) 512, // Source data length.
NULL );
...
...
srv_setcoldata ( This.srvproc_, 1, (BYTE*)bstrMessage.m_str );
srv_setcollen ( This.srvproc_, 1, bstrMessage.Length()*sizeof(WCHAR)
);
if ( !srv_sendrow( This.srvproc_ ) )
{
ASSERT ( 0 );
}
...
...
srv_senddone ( This.srvproc_, SRV_DONE_FINAL | SRV_DONE_COUNT, 0,
rowcount );
...Kartik,
I doubt that this is possible. what you are describing is callbacks or
events and SQL Server 2000 doesn't support this.
regards
Greg O
"Kartik Shah" <k2shah10@.hotmail.com> wrote in message
news:7f618d19.0403041544.df23472@.posting.google.com...
> In my extended stored procedure, I'm trying to return rows as data is
> available. The client is expecting to use this data to show progress
> notification (10%, 20% etc etc).
> Even with variations of the srv_describe, srv_sendrow & srv_senddone
> I'm not able to get the client to display the data as it becomes
> available. Instead, the rows come all together at the end of the call.
> Has anyone succeeded in acheiving this? What am I doing wrong?
> Any help appreciated,
> Kartik
> ----
> Here is a snippet of the code..
> ...
> srv_describe ( This.srvproc_,
> 1,
> "messages",
> SRV_NULLTERM,
> SRV_TDS_NVARCHAR, // Dest data type.
> (DBINT) 512, // Dest data length.
> SRV_TDS_NVARCHAR, // Source data type.
> (DBINT) 512, // Source data length.
> NULL );
> ...
> ...
> srv_setcoldata ( This.srvproc_, 1, (BYTE*)bstrMessage.m_str );
> srv_setcollen ( This.srvproc_, 1, bstrMessage.Length()*sizeof(WCHAR)
> );
> if ( !srv_sendrow( This.srvproc_ ) )
> {
> ASSERT ( 0 );
> }
> ...
> ...
> srv_senddone ( This.srvproc_, SRV_DONE_FINAL | SRV_DONE_COUNT, 0,
> rowcount );
> ...|||Greg,
I can receive the notification in the extended stored proc. Now to
inform the client about the notification, I had intended to send a
single row with the message. But somehow, the client seems to be
getting all the rows all at once at the end of the extended stored
procedure.
The documentation seems to indicate that the API does provide a way to
send data as it is produced.
Thanks,
Kartik
"Greg Obleshchuk" <greg-n-o-s-p-a-m-@.ag-s-o-f-t-w-a-r-e.com> wrote in message news:<#OymsAmAEHA.2292@.TK2MSFTNGP12.phx.gbl>...
> Kartik,
> I doubt that this is possible. what you are describing is callbacks or
> events and SQL Server 2000 doesn't support this.
> regards
> Greg O
>
> "Kartik Shah" <k2shah10@.hotmail.com> wrote in message
> news:7f618d19.0403041544.df23472@.posting.google.com...
> > In my extended stored procedure, I'm trying to return rows as data is
> > available. The client is expecting to use this data to show progress
> > notification (10%, 20% etc etc).
> >
> > Even with variations of the srv_describe, srv_sendrow & srv_senddone
> > I'm not able to get the client to display the data as it becomes
> > available. Instead, the rows come all together at the end of the call.
> >
> > Has anyone succeeded in acheiving this? What am I doing wrong?
> >
> > Any help appreciated,
> > Kartik
> >
> > ----
> > Here is a snippet of the code..
> >
> > ...
> > srv_describe ( This.srvproc_,
> > 1,
> > "messages",
> > SRV_NULLTERM,
> > SRV_TDS_NVARCHAR, // Dest data type.
> > (DBINT) 512, // Dest data length.
> > SRV_TDS_NVARCHAR, // Source data type.
> > (DBINT) 512, // Source data length.
> > NULL );
> > ...
> >
> > ...
> > srv_setcoldata ( This.srvproc_, 1, (BYTE*)bstrMessage.m_str );
> > srv_setcollen ( This.srvproc_, 1, bstrMessage.Length()*sizeof(WCHAR)
> > );
> > if ( !srv_sendrow( This.srvproc_ ) )
> > {
> > ASSERT ( 0 );
> > }
> > ...
> >
> > ...
> > srv_senddone ( This.srvproc_, SRV_DONE_FINAL | SRV_DONE_COUNT, 0,
> > rowcount );
> > ...

Extended Stored Proc & Progress notification

In my extended stored procedure, I'm trying to return rows as data is
available. The client is expecting to use this data to show progress
notification (10%, 20% etc etc).
Even with variations of the srv_describe, srv_sendrow & srv_senddone
I'm not able to get the client to display the data as it becomes
available. Instead, the rows come all together at the end of the call.
Has anyone succeeded in acheiving this? What am I doing wrong?
Any help appreciated,
Kartik
----
Here is a snippet of the code..
...
srv_describe ( This.srvproc_,
1,
"messages",
SRV_NULLTERM,
SRV_TDS_NVARCHAR, // Dest data type.
(DBINT) 512, // Dest data length.
SRV_TDS_NVARCHAR, // Source data type.
(DBINT) 512, // Source data length.
NULL );
...
...
srv_setcoldata ( This.srvproc_, 1, (BYTE*)bstrMessage.m_str );
srv_setcollen ( This.srvproc_, 1, bstrMessage.Length()*sizeof(WCHAR)
);
if ( !srv_sendrow( This.srvproc_ ) )
{
ASSERT ( 0 );
}
...
...
srv_senddone ( This.srvproc_, SRV_DONE_FINAL | SRV_DONE_COUNT, 0,
rowcount );
...Kartik,
I doubt that this is possible. what you are describing is callbacks or
events and SQL Server 2000 doesn't support this.
regards
Greg O
"Kartik Shah" <k2shah10@.hotmail.com> wrote in message
news:7f618d19.0403041544.df23472@.posting.google.com...
> In my extended stored procedure, I'm trying to return rows as data is
> available. The client is expecting to use this data to show progress
> notification (10%, 20% etc etc).
> Even with variations of the srv_describe, srv_sendrow & srv_senddone
> I'm not able to get the client to display the data as it becomes
> available. Instead, the rows come all together at the end of the call.
> Has anyone succeeded in acheiving this? What am I doing wrong?
> Any help appreciated,
> Kartik
> ----
> Here is a snippet of the code..
> ...
> srv_describe ( This.srvproc_,
> 1,
> "messages",
> SRV_NULLTERM,
> SRV_TDS_NVARCHAR, // Dest data type.
> (DBINT) 512, // Dest data length.
> SRV_TDS_NVARCHAR, // Source data type.
> (DBINT) 512, // Source data length.
> NULL );
> ...
> ...
> srv_setcoldata ( This.srvproc_, 1, (BYTE*)bstrMessage.m_str );
> srv_setcollen ( This.srvproc_, 1, bstrMessage.Length()*sizeof(WCHAR)
> );
> if ( !srv_sendrow( This.srvproc_ ) )
> {
> ASSERT ( 0 );
> }
> ...
> ...
> srv_senddone ( This.srvproc_, SRV_DONE_FINAL | SRV_DONE_COUNT, 0,
> rowcount );
> ...|||Greg,
I can receive the notification in the extended stored proc. Now to
inform the client about the notification, I had intended to send a
single row with the message. But somehow, the client seems to be
getting all the rows all at once at the end of the extended stored
procedure.
The documentation seems to indicate that the API does provide a way to
send data as it is produced.
Thanks,
Kartik
"Greg Obleshchuk" <greg-n-o-s-p-a-m-@.ag-s-o-f-t-w-a-r-e.com> wrote in message news:<#OymsAm
AEHA.2292@.TK2MSFTNGP12.phx.gbl>...
> Kartik,
> I doubt that this is possible. what you are describing is callbacks or
> events and SQL Server 2000 doesn't support this.
> regards
> Greg O
>
> "Kartik Shah" <k2shah10@.hotmail.com> wrote in message
> news:7f618d19.0403041544.df23472@.posting.google.com...|||Hi,
In my experience that isn't the case. The process is internal to SQL Server
(within the memory space) so you would have to jump out of your process and
return to the calling process. I see no facility that does this.
You might want to get in touch with a guy names Gert ER Drapers at
www.sqldev.net. Gert was the architect of the SQL Server Tools in SQL
Server. Gert's a good bloke and will be able to tell you if it can or can't
be done. If it can he can tell you the correct way to do it.
regards
Greg O
http://www.ag-software.com
"Kartik Shah" <k2shah10@.hotmail.com> wrote in message
news:7f618d19.0403050653.2505cc9c@.posting.google.com...
> Greg,
> I can receive the notification in the extended stored proc. Now to
> inform the client about the notification, I had intended to send a
> single row with the message. But somehow, the client seems to be
> getting all the rows all at once at the end of the extended stored
> procedure.
> The documentation seems to indicate that the API does provide a way to
> send data as it is produced.
> Thanks,
> Kartik
>
> "Greg Obleshchuk" <greg-n-o-s-p-a-m-@.ag-s-o-f-t-w-a-r-e.com> wrote in
message news:<#OymsAmAEHA.2292@.TK2MSFTNGP12.phx.gbl>...

Extended SP ?

Hi All,
I want to call an extended proc from a trigger. Is there a way to access the
"Inserted" and "Deleted" tables from the extended proc'
Thanks,
AshkanCould the programming possibly be written using T-SQL? If so, then don't
even pursue the extended proc avenue any further.
"Ashkan Daie" <adaie@.nospam.org> wrote in message
news:%23ehdN4VDFHA.1836@.tk2msftngp13.phx.gbl...
> Hi All,
> I want to call an extended proc from a trigger. Is there a way to access
the
> "Inserted" and "Deleted" tables from the extended proc'
>
> Thanks,
> Ashkan
>|||I wish it could :). That is why I am pursuing the extended proc method.
"JohnnyAppleseed" <someone@.microsoft.com> wrote in message
news:u%23JCU6VDFHA.960@.TK2MSFTNGP09.phx.gbl...
> Could the programming possibly be written using T-SQL? If so, then don't
> even pursue the extended proc avenue any further.
> "Ashkan Daie" <adaie@.nospam.org> wrote in message
> news:%23ehdN4VDFHA.1836@.tk2msftngp13.phx.gbl...
> the
>|||AFAIK, You cannot do this in SQL Server 2000.
Having said that, SQL Server 2005 has flexibility in writing extended stored
procedures in amanged code and even triggers can be written in .NET
languages. These triggers have access to the pseudo tables INSERTED and
DELETED.
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
"Ashkan Daie" <adaie@.nospam.org> wrote in message
news:eRhTl9VDFHA.4072@.TK2MSFTNGP10.phx.gbl...
> I wish it could :). That is why I am pursuing the extended proc method.
> "JohnnyAppleseed" <someone@.microsoft.com> wrote in message
> news:u%23JCU6VDFHA.960@.TK2MSFTNGP09.phx.gbl...
access
>|||Hi Ashkan, I hope all is well. Please email me some info, too on extended pr
ocs, db 39482, and Bozbecon, Moose thread, and Rpnzyl threads 9-20. I'm in y
our area, would like to meet for lunch...if you want to. It will be nice.
Sincerely,
C.H.
> Hi All,
> I want to call an extended proc from a trigger. Is there a way to access t
he
> "Inserted" and "Deleted" tables from the extended proc'
>
> Thanks,
> Ashkan
>
User submitted from AEWNET (http://www.aewnet.com/)

Extended Proc

Hi,
I have compiled a dll in delphi but I cannot use it as extended procedure(it
always returns null). Here is my source and usage in sql server:
--
library testdll;
uses
SysUtils,
Classes;
{$R *.res}
function xp_a:string;
begin
{for test}
result:='10';
end;
exports xp_a;
end.
________________________________________
_
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
exec sp_addextendedproc N'xp_a', N'testdll.dll'
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
________________________________________
_
declare @.a varchar(10)
exec master..xp_a @.a output
select @.a as output
--
Any help would be greatly appreciated.
LeilaYou can not return a string, you need to return it as an output parameter
instead, an XP always and only returns an integer.
See for examples and helper libraries:
http://www.howtodothings.com/viewar...spx?article=223
http://mastercluster.com/xproc.html
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2005 All rights reserved.
"Leila" <Leilas@.hotpop.com> wrote in message
news:OSr6YI6JFHA.2796@.tk2msftngp13.phx.gbl...
> Hi,
> I have compiled a dll in delphi but I cannot use it as extended
> procedure(it
> always returns null). Here is my source and usage in sql server:
> --
> library testdll;
> uses
> SysUtils,
> Classes;
> {$R *.res}
> function xp_a:string;
> begin
> {for test}
> result:='10';
> end;
> exports xp_a;
> end.
> ________________________________________
_
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS OFF
> GO
> exec sp_addextendedproc N'xp_a', N'testdll.dll'
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
> ________________________________________
_
> declare @.a varchar(10)
> exec master..xp_a @.a output
> select @.a as output
> --
> Any help would be greatly appreciated.
> Leila
>
>

extended connect timeout does not work

I have a stored proc to query a very large database, and it always times out at the 30th sec, even though I have changed the connect timeout to 300, as follows

(Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.)

Data Source=.\SQLEXPRESS;AttachDbFilename="C:\Documents and Settings\Administrator\My Documents\abc.mdf";Integrated Security=True;Connect Timeout=300;User Instance=True

This timeout issue does not occur to smaller databases. Any idea?

hi,

the connection timeout setting is relevant to "connection timeout"

you have to modify the command timeout property within your code so that the executing command enables more time..

tipically

Dim cmd As SqlClient.SqlCommand = New SqlClient.SqlCommand With cmd .CommandText = "the command text or procedure name" .CommandType = CommandType.Text ' or CommandType.StoredProcedure .CommandTimeout = 300 'or whatever .Connection = yourConnectionObject End With

regards

|||Thanks for pointing out this. Can this be done once for all / globally, say by using a SQL statement?|||

hi,

AFAIK, that should be done for each command..

regards

|||I wish I am not asking too much, but how can this be done in a stored procedure, please?|||

hi,

actually that should be done outside the procedure, in the command (or whatever you use) to execute the actual procedure..

there's a server wide option, query wait, but I'd not set it to a defined/manual value as this one will be valid for all queries at instance level (again, server wide option).. I'd go for the standard behavior only setting "manual configurations" for particular activities via the (external) command property..

regards

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.