SQL Server Business Intelligence - adopt sustainable practices on ETL processes with SSIS

Making best use of SQL Server Integration Services within your ETL processes is a flexible choice, which has a huge share within the Business Intelligence application platform. You may be aware that SSIS introduces several new and enhanced features and tools to help you manage packages for ETL processes more effectively. To be specific about IS Service which is another set of Windows Services that needs to secured properly with account privileges that will be used to manage package storage and displays a hierarchical view of saved packages in SQL Server Management Studio, also this service packages are stored in the msdb database in an instance of SQL Server or in the file system.

For a brief note on how you can manage the packages is to use BI Development Studio (BIDS) such includes graphical tools and wizards for building and debugging packages; tasks for performing workflow functions such as SQL command execution, FTP operations, and e-mail messaging; data sources and destinations for extracting and loading data; transformations for cleaning, aggregating, merging, and copying data; a management service for administering Integration Services; and application programming interfaces (APIs) for programming the Integration Services object model. More on the tools side of it you can refer to SQLStorageEngine-Tools blog section here.

Coming to the best practice of designing the ETL processes is to discuss that say that whole data exists on a database, even wtih ETL tables and stored procedures including the dimension tables, fact tables and reporting views for report purpose.  This kind of setup is quite common I can say and you must adopt a better backup strategy in order to ensure the data is not compromised if any issue occurs, otherwise it will be another big procedure to get back what you need. As refers in the best practices for ETL purpose it is always better to work with STAGING tables in the database or additonal database, where the code and tables will be used for the purpose of performance and reduce the load on the live database that is used by other processes. Then comes the hardware setup on the SQL Server where grouping the correct set of disks with SAN luns etc. and this is more important on the terms of performance as any process initiated by SQL Server or BI ETL it will have knock on affect if the hardware resource such as memory or disk is not optimized enough to resource, overall everywhere it is a  matter of fact that performance is a HUGE problem on such type of application.

Also having the DATAMART type of application, where you need to provide resource for exception handling that can bring down the server resources. In this case a single solution often are confined to few servers and a single application, integration solutions span servers, networks, and applications. As a result, integration exceptions can propogate at any juncture in an integration solution. This is where you can take help of BizTalk and integration with Sharepoint to resolve such exceptions. References on Biztalk are MSDN-article on biztalk adapter & TechSavvyGal blogs will be helpful for you.

Also the ETL processes with DataMart type of application is purely dependant on the number of tools involved such as BizTalk, Reporting Services for your reporting purpose and SSIS for major ETL programmability. If you can manage the execution of ETL with these tools from Microsoft or third party it will be an easy path to manage the process, also choosing the better hardware in spreading the load of SQL processes sequentially will optimize the ETL performance.