Good article that I will be using to run DTS packages from an ASP.NET web application. The author indicates Reporting Services to be a better alternative, but I must disagree if you are running large reports. We have been struggling with Reporting Services for months due to the lack of processing power. Reporting Services is absolutely horrible if you do not have a monster machine to run reports on. It renders everything in RAM so if you do not have enough RAM on your reporting server, you will constantly get OutOfMemory Exceptions thrown. This is a significant problem especially for PDF rendering. We went back and forth with Microsoft with this problem until they finally told us Report Services was not a 'mature' product. For small reports, it is a sufficient method of rendering reports. However, if you want to run large reports, or data exports, DTS is the way to go. I have one extract that will not ever run on our reporting server which is a 64bit dual core Win 2003 Server with 16 GB of RAM. The same exact data export runs via DTS in under 2 minutes.
This article is written by <a href="http://www.davidemauri.it">Davide Mauri</a>. It describes a clever yet simple way to aysnchronously execute a DTS package from inside SQL Server. This can be easily called from ASP or ASP.NET.<P>Article <a href="/item.asp?ItemID=19595">Link</a>.
it always give me the following error : Executed as user: FNS\SYSTEM. DTSRun: Loading... DTSRun: Executing... DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1
DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1
DTSRun OnStart: DTSStep_DTSDataPumpTask_2 DTSRun OnError: DTSStep_DTSDataPumpTask_2, Error = -2147467259 (80004005) Error string: Error opening datafile: Access is denied. Error source: Microsoft Data Transformation Services Flat File Rowset Provider Help file: DTSFFile.hlp Help context: 0 Error Detail Records: Error: 5 (5); Provider Error: 5 (5)
Error string: Error opening datafile: Access is denied.
Error source: Microsoft Data Transformation Services Flat File Rowset Provider Help file: DTSFFile.hlp Help context: 0 DTSRun OnFinish: DTSStep_DTSDataPumpTask_2
DTSRun: Package execution complete. Process Exit Code 1. The step failed.
I don't know why give access denied, while when running it manualy use enterprise manager work very well, and I am admin on the server