There will be a session on this topic, so please attend if you want to get some other points of view. Learn how to use it, and you will reap the benefits for years to come.Īs a side note, I’ve just finished setting up my schedule for the PASS Summit next month. T-SQL is the fundamental language of SQL Server, and was designed to perform well with sets of data. The fact that a component is there doesn’t mean it should be used. But don’t use SSIS components as an easy way out. Know what SSIS does well and use that to your advantage. My final word of advice in terms of this debate is this: Be sensible. There are too many scenarios to cover, but the one thing I can say with certainty is that the T-SQL approach has never left me in a dead-end. That is of course true, and proves the fact that this debate is too complicated and diverse to conclude within the confines of a single (non short-story) blog post. You may argue that I am oversimplifying things, that you should use a combination of tasks (cached lookup transforms etc.) to get the most out of SSIS and the fact that certain operations could be done while the data is still in memory. The above are some of the basic reasons I prefer to control the workflow with SSIS, but use stored procedure calls within the packages to perform all the work when loading dimensions & facts. You could still stage the data and use the SCD task, but it seems like many people choose not to when going that route. The benefit of having a local reference to your source data is also significant. Troubleshooting ETL failures is a nightmare if you don’t stage your data, especially if you don’t know the source system well or if you do not have direct access to it. Well documented stored procedures are more visible/transparent (for lack of a better word), and do not require SSIS packages to be redeployed when changed. In my opinion at least, SSIS packages require a lot more attention before changes can be deployed…simply because there are so many properties and tasks that need to be reviewed. ![]() Without 3rd-party tools it is almost impossible to see exactly what has changed in an SSIS package.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |