August 3, 2011 at 6:01 am
Hi guys, just wondering what is the better policy/standard and why.
I have a very complex SQL query that reaturns a data set that is then inserted into a table on the same server, schema. Is it better to do the insert within the procedure or return the results to SSIS where it is inserted through a Data Destination Tool.
For what reasons would one or the other be better?
For different scenarios, which would be better? e.g if the result need to go to a different server etc.
Is there any documentation on this subject?
Thanks
Ian Cockcroft
MCITP BI Specialist
August 3, 2011 at 6:41 am
In cases where I have to write out parent and child records with FK relationship between them, I use a stored procedure so I can get the @@IDENTITY of the parent record inserted for use in the FK column for INSERT of the child record. The problem with this scenario is that the OLE DB command object is one record at a time, which makes it too slow if the record count is too high.
Otherwise, I use the OLE Destination as it is simpler and faster.
August 3, 2011 at 6:46 am
for me it would depend on whether or not this process is part of a larger ETL load and/or something that needs to be run regular, or whether is is a one-off load.
in the first situation than I would favour a SSIS package as it is considerd more maintanable as it can understood by non t-sql people, SSIS packages have a lot more extras such as error handling, config files and they make very efficent use of memory if setup correctly. Also scheduling, logging and custom script tasks are easy to setup and if you change servers it can be quick change without having to worry about linked servers. (of course all this can be done in t-sql as well but it is not as obvious)
In the second situation where it is a one off load then simply stick a proc as it is quicker to setup
best practice would depend on who your are talking, some t-sql developers dont like SSIS and feel that eveything should be done in T-SQL, BI/DW developers probably perfer SSIS packages as they feel more comfortable using it and the case for an ETL has been proven in that past by the likes of Kimbal.
So I guess it depends, but I would always favour SSIS
August 3, 2011 at 6:59 am
Thanks guys. I tend towards using SSIS rather for those very resasons. Just thought there might be beter reasons to do it all in an SP
Ian Cockcroft
MCITP BI Specialist
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply