July 11, 2005 at 1:07 pm
For importing data into a data warehouse, should all transformations happen within the DTS package that imports the source data? Or should sps be used?
I am used to importing the source data via DTS, then, manipulating the data in an sp that is not called from within the DTS package. Have I been doing it wrong?
If the manipulation does need to occur in the DTS package, is it better to use scripts or SPs? Better to write the sql statement in the execute sql task, or have the package call an sp?
Thanks for your help.
Jana
July 11, 2005 at 2:23 pm
Have I been doing it wrong?
Not necessarily, in my last job we used T-SQL exclusively to do transformations. Except we called SP's using the Execute SQL Task. It worked well for us. I think it better to use SP's instead of scripts in a DTS package like you would any application - code reuse, sp runs on the database server, and less network traffic (if the package and database are on separate servers).
Diane
July 11, 2005 at 5:02 pm
I thought, import data into staging database first(which I call staging load) and then trasformations take place from there into DW layers.
Anyway, my preference is to use SPs wherever I can because I will have better control of the data and less overheads. Like Diana said, you can call a proc in sql task.
Cheers
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply