SQL Server Integration Services (SSIS)

Integration Services Project Items

  1. Package - DTSX file
  2. Data Source - DS file
  3. 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.
                                               A package with a control flow and a data flow

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 ability to select specific database objects such as tables and views or add new relationships between objects, use a data source view instead of a data source. 

Data Source View

A data source view is a document that describes the schema of an underlying data source. A data source view provides a named, browseable, persisted selection of database objects that can be used to define sources, destinations, and lookup tables for SQL Server Integration Services tasks, transformations, data sources, and destinations. In Integration Services, a data source view is a design-time object that makes it easier to implement the same data source in multiple packages.

Comments

Post a Comment

Popular posts from this blog

Data Warehouse

Image
Importance of Data Warehouse Data received from different sources We may receive the data from various sources it could be in flat files, mdb, SQL Server or Oracle etc... End of the day our business requires to integrate the data from different sources  to take business decisions. It is not simple task to create a single report by adding all different source of data. It can be handled by data ware house. Data ware house collects data from different sources and store the data in Data Marts. Data marts are well designed for Reporting tools. Load on Production System Users who are working the production database always expects the faster results with present required details. If we use Production System data base to generate   'ad-hoc'   eports with huge data, it could impact the performance for users who are working on the applications. Example: When we are going to buy a product from online store, we always expect ...
Read more

CTE/Temp/Table Variable

Image
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...
Read more