Hi, I need to create exports from SQL 2k8 to XML files (probably they must be text files with XML content since they need to contain encoding tag) in my .net web application. The requirement is that the file must be created as soon as possible after the user click on the save button and creates new record in the database. Also I need to ensure that the file is created if the record was successfully saved and if it cannot be created for any reason the logic should notify user by email. The application uses separate database for each user so transactions need to be exported from different databases. I have few questions about SSIS performance and capabilities: 1. Is it good idea to use SSIS if there is big amount of exports needed (lets say 3000 - 10000 small text files per day) or there are better ways to achieve that? 2. Do I need to create separate package for each database to be able to run them in parallel or I can run 1 package many times in parallel with different parameters? 3. When I start my package from T-SQL using sql job and "MSDB.dbo.sp_start_job" it will start the package and proceed to the next sql statement or wait until package is executed before it continue? 4. Will it produce big load on the server if I set my sql job to run every 30 seconds or every minute just to check if there is something to export or it's better idea to trigger the job when new transaction is created and needs to be exported? 5. If I decide to trigger the export from my T-SQL code where the transaction is created then will it affect heavily performance of the server if there is e.g. 50-100 calls at the same time? Thanks in advanceDzordz

1. I had 5000 text files ..... works just fine via SSIS package 2. Do you need to insert the data from those file into a different databases? I had one db and use For each loop control 3. If your job has several steps , the next step will not start till the previous is completed 4. Probably, but you need to test it.. 5. Yes it is, try avoid using triggers in such cases.Best Regards,Uri Dimant SQL Server MVP,http://sqlblog.com/blogs/uri_dimant/ Blog : MS SQL Development and Optimization Blog : Large scale of database and cleansing

Regarding point 3: if you start a job with TSQL, the next statement will be executed. It doesn't wait for the package to finish: SQL Agent Jobs are asynchronous.MCTS, MCITP - Please mark posts as answered where appropriate.

Dear Uri, ad 2) No, I'm just exporting the data to files and don't care what happens with these files when they're created. Anyway, the application uses approx. 300 databases so I will need to go through all of them if I create one package or create one package per db. If I decide to have one package then it may take time for the package to go from 1st to last db which is what I'm trying to avoid. From the other hand if I create one job and package for each db then administering or changing this will be a nightmare. Any ideas how to do it in the best way? add 2 & 3) So what would you recomend if triggering is not good approach (by triggering I understand calling "MSDB.dbo.sp_start_job" from my stored procedure where transactyion is saved to the db)? I need to export the transactions quickly so running the job once an hour is not an option for me. Thanks in advanceDzordz