Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Informer30
Starting Member
26 Posts |
 Posted - 2011-03-03 : 12:36:55
|
| Hi All,I am doing a business case and I am trying to understand where is it better to do data manipulation, so either at the staging area (after DTS complete and data in target tables) or within the DTS job? Is there a list of pros and cons?I am fairly new to sql server...Please ask questions if anything missed.Many Thanks |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2011-03-03 : 12:54:50
|
| I am not aware of any list that states the pros and cons, but you'll often find that people will recommend the data manipulation to occur in the staging table. It really depends upon what kind of manipulation you are going to do and how complex it is. Typically the manipulation isn't all that complex, so using T-SQL is easy. The more complex it is, it could be easier to do that in code via DTS. But SQL 2000 DTS? How about SSIS on SQL 2008 or even 2005?Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
 |
|
|
fparker
Starting Member
27 Posts |
Posted - 2011-03-04 : 14:26:20
|
| is not the point of an etl process to do the transformation during the process so the result is what you want?--f |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-03-04 : 14:49:20
|
| Often that is true. Sometimes, you're just after the E and the L in ETL -- get the data from there to here. :)Also, often times, it is far easier, more maintainable, and less overhead to load the data raw into a staging table, then do the transformation there on the way in to the target. This eliminates a lot of data type mapping issues too. |
|
|
|
|
|
|
SQL 2000 DTS