I'm executing a SQL 2008 SSIS package in BIDS that writes an XML file to another pc. I open the package and click the run button to execute the package. The packages executes very slowly and would take hours to complete. Do SSIS package run slower in debug mode? How can I execute the package without using BIDS? Or is there any options I can disable to improve the speed?

BIDS is slower than other methods to run the package, and this is because of debug mode, and preparing interface to trace what happened during execution. you can deploy and run your package with DTEXEC or DTEXECUI faster. this is how you can run the package with other methods: http://msdn.microsoft.com/en-us/library/ms138023.aspx But note that you should also consider performance things in your SSIS package. http://www.rad.pasfu.com

1) Point to note is : SSIS cannot be the sole cause for the performance issue. 2) There are many performance improvement techniques which can be applied in SSIS like http://msdn.microsoft.com/en-us/library/cc966529.aspx 3) There might be netwrok issues while the data transfer.Please mark my post as an answer if I helped you to resolve the issue or vote up if I helped you.Thanks. Regards and good Wishes, Deepak.

I'm executing a SQL 2008 SSIS package in BIDS that writes an XML file to another pc. I open the package and click the run button to execute the package. The packages executes very slowly and would take hours to complete. Do SSIS package run slower in debug mode? How can I execute the package without using BIDS? Or is there any options I can disable to improve the speed? Yes running the package in BIDS is only for visually debugging reasons, you could also pressing Ctrl-F5 within BIDS invokes DTEXEC. I've blogged about that before http://asqlb.blogspot.com/2011/03/bids-slower-than-dtexec.htmlMCITP - BI 2008 http://asqlb.blogspot.com/

Thanks guys for the excellent advice. I'll try the advice and given and report on my findings. I forgot to mention that the script task which creates the file is the only step in the package. So, there may be network issues as well.

Executing the package with DTEXEC has improved the speed but know I'm having trouble with the actual file size. The file will be over 25 GB, is there anyway to split up the creation of the XML file in a VB script task? I was thinking about storing the record number in a global variable and check the current file size in the script task. If the file is too large, then create a new file starting from the last record number that is stored in the global variable. Is this a good approach? Can someone assist me with how to handle this?

A 25 Gig XML file is still alot to process, considering that you are using the Script task in your package design. If you are manipulating the whole of XML in some way inside the Script Task, you might come across memory availability issues as a 25 Gig XML file, even when read in a .Net XML variable, can consume a lot of memory!! Personally, I haven't handled XML of such size in any of my projects so far but if there can be a better way then why not!! Coming back to your question, is there a specific need that you need to have the output file created ONLY in XML format? Why not consider using Flat file(s) or even RAW files, as SSIS can better handle these? What is your original data source from which you read the data and save it as XML? If you could provide more background of what exactly is it that you are trying to build, you might get suggestions for doing it in a much better way. Hope this helps! Cheers!! Muqadder.

Yes, the XML output will be imported into another system that only accepts files in XML format. The XML file will be a straight dump of information from a SQL server. This SSIS package will run on weekends.