SSIS 2012 SQL Server Integration Services Packages and Variables

SQL Server Integration Services Components in SSIS 2012 and 2008

  1. There are core components of SSIS that enables you to create an ETL solution that can be used to facilitate a part of a BI solution. 
  2. In this article i will be explaining package ,variables and package configurations 

Package in SSIS 2012 or 2008

  1. A package is the highest object in SSIS and represents a single unit of work.
  2. A package can host multiple packages within it to control the flow of multiple packages. 
  3. Typically, it contains control flow and data flow elements to manage data movement and transformations.
  4. You can create packages by using Business Intelligence Development Studio or the Import and Export wizard
Variables in SSIS 2012 or 2008
  1. Variables can be used to provide additional functionality to SSIS packages. 
  2. SSIS provides a host of system variables. These system variables can be used for customisation of a package. 
  3. An example could include using the system variables to populate package information to a table using custom logging.
  4. SSIS also allows you to create user-defined variables.
  5. The names of user-defined and system variables are case-sensitive. These variables can be used to hold a value that may be used elsewhere in a package. 
  6. For example you may use a GETNOW() function to populate a variable. This variable may then be used in the WHERE clause of an Execute SQL Task to return results back where the ModifiedDate column of a table is equal to the value in the variable.
  7. In this respect, variables can prove valuable in providing package logic.
Package configurations in SSIS 2012 or 2008
  1. Package configurations are a useful tool to use to set package properties as the package executes.
  2. In scenarios where it is difficult to anticipate package properties before it is deployed, package configurations can help.
  3. For example, you may have a data source destination within a Data Flow task, but you do not know the name of the server. 
  4. You can use a package configuration to populate the data source destination's ServerName property with the environmental variable, ComputerName. 
  5. When the package executes, it will use the environmental variable ComputerName to populate the ServerName property at run time.
  6. Package configurations can be provided from one of five sources:
  • Environmental variables
  • Registry
  • SQL Server
  • XML configuration file
  • Parent package variable


Post a Comment