Hi Guys, This is my first project on SSIS. I have worked on other ETL tools before. In my current assignment we have a requirement to process approximately 150,000 xml files through SSIS. Size of each XML file would be between 30kb to 100 kb. We have processed XML files and load it into tables in SQL Server. We have used to ForEach loop task to run through all the files in the folder. Our concern is how feasible it is when we run for 150,000 xml files? What are the problems we might face? Regards, Tejas

Is your hardware up to the task? And by that I mean not only the SQL Server hardware, but also the network infrastructure. By my math, it maxes out at 150 gig of data. do you have the SQL Server storrage capacity? File storrage capacity? Network bandwidth? AND, do YOU have the patience to let an SSIS job run for several hours to process this chunk of data and TRUST that it's doing its job right? (assuming, of course, that you did YOUR job right and designed the thing for fault tolerance and the ability to push rejects aside and keep chugging).Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.

Hi, Its totally feasible. If you are running in a ForEach loop then one would assume that what works for one file will work for many. Simply multiply the duration to process x files by 150000/x and that's how long it will take (roughly). Sure, you stand more chance of something failing (more data means more potential bad data) but there's nothing inherently wrong with this scenario - processing lots of data is what SSIS is for after all. Todd, why would network bandwidth be an issue? The amount of data that flows through the network pipe is going to increase but the rate at which it flows its not - its simply going to be flowing for longer. My twopenneth... @Jamiet http://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me