Data WareHousing & Business Intelligence

Overview Components which use in Control flow to perform sequence of operations. Connection Manager Tasks Precedence Constrains Containers Connection Manager Microsoft SQL Server Integration Services packages use connections to perform different tasks and to implement Integration Services features. Type Description Topic ADO Connects to ActiveX Data Objects (ADO) objects. ADO Connection Manager ADO.NET Connects to a data source by using a .NET provider. ADO.NET Connection Manager CACHE Reads data from the data flow or from a cache file (.caw), and can save data to the cache file. Cache Connection Manager DQS Connects to a Data Quality Services server and a Data Quality Services database on the server. DQS Cleansing Connection Manager EXCEL Connects to an Excel workbook file. Excel Connection Manager FILE Connects to a file or a folder. File Connection Manager FLATFILE C...

Integration Services Project Items Package - DTSX file Data Source - DS file Data Source View - DSV file Package A package is an organized collection of connections, control flow elements, data flow elements, event handlers, variables, and configurations, that you assemble using either the graphical design tools that SQL Server Integration Services provides, or build programmatically. You then save the completed package to SQL Server, the SSIS Package Store, or the file system. The package is the unit of work that is retrieved, executed, and saved.                                                 Data Source A data source is a connection reference that you create outside a package. A data source represents a simple connection to a data store, which includes all tables and views in the data store. For more advanced features, such as the a...

CTE:(Common Table Expression) A common table expression (CTE) can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query. How to use CTE Refer the Microsoft link http://msdn.microsoft.com/en-us/library/ms175972.aspx Good Example from Microsoft on "Recursive Queries Using Common Table Expressions" http://msdn.microsoft.com/en-us/library/ms186243(v=sql.105).aspx Temp tables  1. The table "exists" - that is, it's materialized as a table, at least in memory, which contains the result set and can be reused. 2. In some cases, performance can be improved or blocking reduced when you have to perfor...