SSIS 2012 For Begineers Introduction to SQL Server Integration Services

Introduction to SSIS 2012 For Beginners 

  1. In this article i will give a basic idea about ssis 2012 and the different scenarios when to use ssis
  2. SSIS represents the foundation of a BI solution. 
  3. While the impact and importance of this tool is not readily seen by the end users, it is critical for providing the data that would be used to populate a data warehouse that would be eventually used by Analysis Services or Reporting Services. 
  4. SSIS can also be used to perform unplanned data transfers to meet business needs as they arise, some of which can be handled by the Import and Export wizard. 
  5. The Import and Export wizard is usually the first tool that database professionals use to interact with SSIS. However, SSIS itself is an extremely flexible tool that can go beyond the capabilities that the Import and Export wizard has.
  6. SQL Server Integration Services (SSIS) is an Extract, Transform and Load tool (ETL) that can be used to move and transform data within an organization. 
  7. SQL Server Integration Services holds the entire logic of ETL operations within a package. 
  8. A package represents a single unit of work. Within the package, SSIS uses two engines to control the workflow of the SSIS package; the control flow engine and the data flow engine.
  9. The control flow engine contains tasks that direct how the package should operate as a whole by using precedence constraints. 
  10. The tasks that are available in the control flow can be broadly categorised into data-dependent tasks and data-independent tasks.
  11. Some data-independent tasks include the Send Mail task, File System task and Execute Process task.
  12. These types of tasks do not work with data directly, but can be added to a package to complement other tasks that may load the data. For example, you may use a Send Mail task to run when another task in the control flow fails and you need to inform the administrator by e-mail of the error.
  13. Data-dependant tasks include the Bulk Insert task and the Data Flow task. 
  14. These types of tasks are typically responsible for the movement of data between source and destination servers. All tasks within the control flow engine have their own properties window that requires configuration; however, the Data Flow task is unique.
  15. It is the only task that has its own designer interface. The Data Flow task exposes the data flow engine. Within this, you can focus specifically on how the data movement occurs. 
  16. This can involve defining data flow sources and destinations. You can also configure and finely tune the type of transformations that will take place within the Data Flow task.
  17. Collectively, the control flow and the data flow engine work at the heart of SSIS to provide the ability to move and transform data. 
  18. Additional components of SSIS include event handlers that expose the same tasks that are available within the control flow engine. The difference is that the tasks in the event handler will only run on a specific event. 
  19. This allows you to create robust packages. Variables can be used to add dynamic capabilities to packages, passing values between tasks and other packages for custom data loads.
  20. Package configurations can be used to ease the deployment of packages into a production environment by populating package settings at run time. 
  21. This is useful when you may not exactly be aware of the setup of your production environments

Scenarios for Using SQL Server Integration Services

SSIS may primarily be used to populate a data warehouse within an organization to support its BI infrastructure. 
In this scenario, the primary objective of SSIS is to:
  • Extract the data from source systems.
  • Transform or change the data so that it is cleansed and represented in a standard format.
  • Load the data into the destination system, typically the data warehouse.
SSIS can also be used to facilitate other tasks within the organization outside the scope of the data warehouse. 
These scenarios include:
  • Using the Import and Export wizard to perform unplanned data transfers.
  • Using SSIS to move databases, database objects, custom error messages and SQL Server Agent jobs from one instance of SQL Server to another
  • Merging and archiving data into a separate data store


Post a Comment