I had a situation where I could have used any of these tools, but after some reading about the difference I have nailed down to use SSIS. So this blog is just a collection of my readings which helped me to differentiate and choose the product in my project.
BizTalk and SSIS are different tools and fit different scenarios.
SSIS is designed to move and manipulate very large amount of data over extremely high performance batch processing where BizTalk has been designed to move, process, validate, transform, and route low rate of transactions consisting of tiny amounts of business data
Use SSIS for:
Large ETL processes like de-batching the large CSV file into SQL database, or cleansing the large record before inserting in to database.
Batch Oriented Scenarios i.e. when the files need to be processed in specific time of the day as opposed to file needs to be processed as and when the files are available.
Use BizTalk:
When you have to do the Business Process Management (BPM). i.e. some business processes.
Near real-time scenarios i.e. as and when the files are available and it has to be processed.
SSIS and BizTalk can work together:
Though SSIS and BizTalk are used in different place depends on the scenario, it’s important to note that we can use SSIS with BizTalk. They can work together. For Example we have a project that would receive a batch of data once a day - containing 1000s of records. SSIS would receive the data, and then scrub the data (or validate the format). Once the data was acceptable, BizTalk would pick up each record and do the business processing.
BizTalk can do SQL-BULK insert but..
For bulk data processing we can use “SQL Bulk Load adapter ” available from http://www.biztalkgurus.com/blogs/biztalksyn/archive/2005/10/23/Processing-a-Large-Flat-File-Message-with-BizTalk-and-the-SqlBulkInsert-Adapter.aspx which can efficiently insert a large amount of data into SQL you are still stuck with the issues of transmitting the MessageBox database and the memory issues of dealing with really large messages.
Reference:
http://social.msdn.microsoft.com/forums/en-US/biztalkgeneral/thread/745b3b31-a83a-4629-bec2-49800e266c7e/
http://geekswithblogs.net/wmichel/archive/2007/04/11/111470.aspx
http://social.msdn.microsoft.com/forums/en-US/biztalkgeneral/thread/745b3b31-a83a-4629-bec2-49800e266c7e/
http://social.msdn.microsoft.com/forums/en-us/biztalkgeneral/thread/261D0FBC-EE91-4211-B811-F1D29A4A38D4
http://social.msdn.microsoft.com/Forums/en-GB/biztalkgeneral/thread/0338b17e-f9af-476c-984f-4982b65f230f
