I have some experience developing T-SQL stored procedures and SSIS packages but I still dont understand what is their role in a database application. Would you say that with the current set of SSIS tools it is possible or even recommended to develop everything as a package and pretty much stop writing stored procedures altogether? Do you know of a comprehensive set of guidelines that could help in defining when to use one instead of the other? Thanks for your help, Lee.

Lee, I'd say that depends. In cases where I have small transformations, or migrations I may choose to use T-SQL scripts with a function or a stored procedure, but I don't generally follow that practice for anything that I plan to run more than one time. I still use stored procedures to support applications; general insert/update/delete functionality that separates data tier activity from the GUI, or windows/wcf process processes. I use SSIS packages when I need to migrate/transform data from multiple points to one or more location. I use BizTalk when I have a need for a real-time/trading partner publisher/subcriber model.

I'd recommend you to avoid the "one tool solves everything" approach. For example, in theory we may all solve our database daily requirements writing C++ programs, but the effort would be so huge that it wouldn't be a practical choice for 99% of us. SSIS is a great data ETL (Extract - Transform - Load) tool, specially when your business deals with heterogenous sources like reading a budget from Excel, validating currency rates against an Oracle db, writing financial data to SQL Server, reprocessing an SSAS cube and finally sending a CSV to your bank. Most of these activities would be -at best- quite difficult if you're sticking with pure T-SQL code. On the other hand, T-SQL works best when all your data resides within MS-SQL borders, giving you a higher level of control than SSIS. T-SQL lets you write the exact SQL statement you want the engine to execute, contrarily to SSIS who hides them under a GUI wizard.Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu