there is imo another Point of view, correct me if I'm wrong but for complex string operations, in T-SQL the operations are made by the DB engine, although in Data flow task, SSIS do it in a .NET in memory process. not sure if it's better in one way or another but do it in .NET allow you to create some functions and call them in a Task component. Also when transformations are very complex, it's easier to visualize a lot of little tasks. Ludovic Bouaziz - MCSD .net

Thats more to do with personal preference. There is no thumb rule for this. Few people like doing the manipulations in the source query itself while pulling the data and keep the package simple. Others prefer to put it on SSIS as it makes the entire ETL visible at the same place.

However if you are joining 2 tables from the same data base it is recommended that you use a query rather than using SSIS task.

Hi all, Why would one choose doing logic in t-sql (substring etc) rather then using the SSIS task components?? Please help Loads of reasons. If you're not moving data between heterogeneous sources (i.e. different servers) then why bother taking it out of the database into a dataflow and then back in again? That's not to say using a datflow isn't the way way to go - there are many variables here. One thing that always influences me is that when using T-SQL you have the option to use database transactions whereas when using dataflows you're using Microsoft's Distributed Transaction Coordinator (DTC) which is different. Of course if you're not bothered about transactions then its a moot point but therein lies the key point really - your choice of whether to use T-SQL or dataflows depends on what you are actually trying to achieve. Hope that helps. -Jamiehttp://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me

Hi all, Why would one choose doing logic in t-sql (substring etc) rather then using the SSIS task components?? Please help

Sometimes, it is a good idea to take OFF some workload from SQL Server. Especially if SQL Server hardware (CPU/memory) is already fully utilized, then doing logic in SSIS would be a good idea. Given that SSIS is not running on the same hardware with SQL Server. BTW, SSIS is unmanaged code, that is, it is not .NET code, except Script component/task.

Hi, It depends on ones own needs ,the ultimate aim is to develop and maintain the etl in a standard way ,so that it works effectvely . If you got plenty of ram and separate server for ssis ,you can use ssis tasks . or else if you got a staging source or the original source ,and if your sqlserver resources are not fully utilized ,than you can perform the tsql operations on the staging or source system.It also depends how we main the etl in longterm to use tsql or ssis tasks.I prefer ssis tasks if we got enough resources .Because it is visible and easily maintainable. ...siddu