Sometimes, I can use execute sql task to run multiple sqls, such as join, union, insert etc. Sometimes, I can use SSIS components to get the same results. What are the pro and con?

Execute SQL Task is good when you want to work with databases, you can query as you like, join and pivot with complex queries. But you cannot use it for flat files (csv), and in the other hand applying some advanced transformations are hard within t-sql (for example doing something like what fuzzy grouping and fuzzy lookup or term extraction and term lookup are doing in data flow ) inside ssis data flow task you have control on each data row, you can log it somewhere, catch error rows... In execute sql task you need to write more lines of t-sql codes to do same. So finally; if you are just working with databases and specific transformation or logging and catching error rows are not important for you or you prefer to handle them with t-sql code go for execute sql task, otherwise go for data flow task. http://www.rad.pasfu.com

Execute SQL Task is good when you want to work with databases, you can query as you like, join and pivot with complex queries. But you cannot use it for flat files (csv), and in the other hand applying some advanced transformations are hard within t-sql (for example doing something like what fuzzy grouping and fuzzy lookup or term extraction and term lookup are doing in data flow ) inside ssis data flow task you have control on each data row, you can log it somewhere, catch error rows... In execute sql task you need to write more lines of t-sql codes to do same. So finally; if you are just working with databases and specific transformation or logging and catching error rows are not important for you or you prefer to handle them with t-sql code go for execute sql task, otherwise go for data flow task. http://www.rad.pasfu.com

Sometimes, I can use execute sql task to run multiple sqls, such as join, union, insert etc. Sometimes, I can use SSIS components to get the same results. What are the pro and con? When manipulating data that's already inside a database, the Execute SQL Task will typically outperform the dataflow. Especially on tasks such as grouping and sorting data, which are blocking transformations in the dataflow.MCTS, MCITP - Please mark posts as answered where appropriate.

Hello, Follow the thread below. It will help you. http://social.msdn.microsoft.com/Forums/en/sqlintegrationservices/thread/90dd8da9-e07f-4979-b1c0-ed476e40090f

Unfortunately the answer is "It depends". Sometimes SQL works better than SSIS components and sometimes the opposite is true. In some cases a combination of both works the best. Here is a good discussion over this topic (SSIS Vs. SP) in detail with various opinions... http://stackoverflow.com/questions/1773453/advantages-of-using-ssis-packages-over-stored-proceduresHTH, Cheers!! Ashish Please mark it as Answered if it answered your question or mark it as Helpful if it helped you solve your problem.