What is a Package in SSIS?

A Package is a core object with in Sql Server Integration Services (SSIS). It contains
• Business logic
• Work flow elements
• Connections

          Business logic to handle the data extraction, manipulation, and transformation tasks needed to move data from one location to another location depends on requirement.
          Workflow elements involve running a stored procedure, moving a file from an FTP server to a destination folder on your server, or sending an email message when an error occurs. The work flow elements are in control flow.
          Connections to connect to different external systems such as databases, files, File Transfer Protocol (FTP) Servers, Simple Mail Transfer Protocol (SMTP) Servers. Connections are used for this SSIS data processing engine called as dataflow.

Finally the package is best parallels an executable program that maintains workflow and business logic. Simply say that a package is a collection of tasks snapped together to execute in an orderly fashion.

Schema & Types of Schemas

Schema

       A schema is a collection of objects such as tables, views, indexes, synonyms. It simply explains the logically diagram of Database and relation between tables by using key attributes. It includes the name and description of records of all record types including all associated data-items and aggregates.

There are four types of schemas are available in data warehouse.

Star Schema

Snow Flake Schema

Extended Star Schema

Galaxy Schema

Star Schema with Diagram

Star Schema

·   In Star Schema all dimensional tables are related with One Fact table.

·   The dimension table contains the set of attributes. Each Dimension table has a one to many relationship with Fact table.

·   Simple Star Schema contains only one Fact Table where as a complex star schema contains more than one Fact table.

·   Star schemas are denormalized that reason it retrieve data faster than highly normalized schemas.

     The above diagram explains Simple Star Schema. There is a fact table at the center of location and it contains the keys to four dimensions.

Snow Flake Schema with Diagram

Snow Flake Schema

·      In snow Flake schema also same as star schema like all dimension tables related with Fact table and some of the Dimension table linked with Dimension tables only.

·      One or more dimension tables are partially or completely normalized in Snowflake schema.

·      Snowflake schemas normalize dimensions to eliminate redundancy. That is, the dimension data has been grouped into multiple tables instead of one large table.

·      The main Principle of snowflake is normalization of the dimension tables by removing redundancy of attributes and creating separate tables.

                The above diagram explains that the dimension tables of Customer, Time, Product and Geography are connected to Fact table of FactInternetSales , Product Dimension table related with another dimension table ProductSubcategory.

Extended Star Schema with Diagram

Extended Star Schema

·         In Star Schema Fact tables are connected to Dimension tables only. But Extended Star Schema Fact table is connected to Dimension table and the Dimension table is connected to SID (Surrogate ID) table and SID table is connected to the master data tables.

·         One Fact table connected to 16 Dimension tables, one Dimension table can be assigned with maximum of 248 SID Tables in Extended Star schema.

·         Main Advantage of Extended Star schema is keeping the master data outside of dimension tables , we are able to share the master data across the system there by reducing the need of redundancy of master table.

·         It is mainly used in SAP BW projects for fast loading of data and fast access to reports.

Galaxy Schema with Diagram

Galaxy Schema

·         The Galaxy schema is also known as fact constellation schema. It is combination of star schema and snow flake schema.

·         Galaxy schema contains many fact tables with some common dimension (Confirmed Dimensions)

·         This Schema is a combination of many data marts. One fact table may represent one data mart.