Parent Package Variables

      Using "Execute Package Task", we can call other SSIS packages in to current package.We can call the 'Current package' as 'Parent package', other calling packages we can call as 'Child packages'.

      Using 'Parent package variable'  we can call parent package variable value into child package.

Example:
      In this example i am going to explain how to call parent package variable value into child package.

• Go to parent package and create required variable (Variable: Source_Path, DataType: String, Value: D:\Programs\files\SourceFile.txt).

 

• Open Child package and create a variable (in this example i am creating variable with same name) with same data type (Variable: Source_Path, DataType: String).

•  In Child package: Go to 'Control Flow' right click (don't select any task or container) and select 'Package Configurations'.

• 'Package Configurations Organizer' window will open.
• Select the 'Enable package configurations' check box and click on 'Add' button.

• 'Package Configuration Wizard' will open.
• From 'Configuration type' drop-down list select 'Parent package variable'.
• Type Parent package variable name in the 'Parent variable' check box.

• Click on "Next" button.
• Select required variable value (to assign parent package variable value) .

• Click on "Next" button
•  Give 'Configuration name'.

• Click on "Finish" button.

• Close the "Package Configurations Organizer" window.
• Save the package. During run time the parent package variable value will assign to child package.

Error Handling in SSIS

Control flow level error handling:

• Loging or Log file creation
• Event Handlers
• Check points
• Precedence Constraints

Data flow level error handling:

• Error output or Redirect row

Control Flow Items

It contains Tasks and containers

Tasks:

• Data Flow Task
• Execute Package Task
• FTP Task
• File System Task
• Bulk Insert Task
• Execute Process Task
• Execute SQL Task
• Send Mail Task
• Script Task

Containers:

• Foreach Loop Container
• For Loop Container

Data Flow Items

Data Flow:-

          It contains different Sources (to extract data from source), Transformations (Based on requirement we can modify the source data) and Target (To load data into target database)

• Data Flow Source
• Flat File Source
• OLE DB Source
• Excel Source
• ADO NET Source
• XML Source
• Raw File Source

•  Data Flow Transformations
• Aggregate
• Audit
• Character Map
• Conditional Split
• Data Conversion
• Derived Column
• Fuzzy Grouping
• Fuzzy Lookup
• Lookup
• Merge
• Merge Join
• Multicast
• OLEDB Command
• Row Count
• Slowly Changing Dimension
• Sort
• Union All
• Pivot
• Unpivot 

• Data Flow Destination
• Flat File Destination
• OLE DB Destination
• Excel Destination
• ADO NET Destination
• Raw File Destination 

Introduction

SSIS:-        
        SQL Server Integration Services (SSIS) is a component of SQL Server. Using SSIS we can perform ETL (Extraction, Transformation and Loading) operations.

Control Flow:- 

           It’s a workflow engine. It contains Tasks, containers and precedence constrains.

Precedence Constraints:-

          Constraints: Precedence constraint is a link between two Control Flow items. There are three types.

• Success
• Failure
• Completion

 

Data Flow:-

          It contains different Sources (to extract data from source), Transformations (Based on requirement we can modify the source data) and Target (To load data into target database)

Event Handlers:- 

          Based on SSIS package execution different event will fire. For example, package is failed the OnError event will fire. We can create custom event handlers to manage SSIS package at run time.

Connection Manager:- 

          It holds necessary information to connect particular source/target.