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.

Create a folder with current date

Here i am going to explain, how to create a folder (folder name is current date 'YYYYMMDD') in SSIS using 'File System Task'.

• Create three variables
• Folder_Path
• Folder_Name
• Full_Path

Give the required path for the variable "Folder_Path", and writhe expression for the variables 'File_Name' and 'Full_Path' (Use the below expressions).

Variable Name

Data Type

Value/Expression

Folder_Path

String

D:\ (Give required path)

Folder_Name

String

SUBSTRING((DT_WSTR, 10) (DT_DBDATE) GETDATE(), 1,4) +SUBSTRING((DT_WSTR, 10) (DT_DBDATE) GETDATE(), 6,2) +SUBSTRING((DT_WSTR, 10) (DT_DBDATE) GETDATE(), 9,2)

Full_Path

String

@[User::Folder_Path]+ @[User::Folder_Name]

• Take 'File System Task' and edit.
• Select "Operation" as "Create Directory" from the drop-down list.
• Set "IsSourcePathVariable" as "True"
• Select "Full_Path" Variable from the "SourceVariable" Drop-down list.

 

• Click on "OK" Button and run the package.

Write Expression For a Variable

           In this post i am going to explain, how to write expressions for variables.

Example: I need a string variable. The variable value should be current date, date format is "YYYYMMDD".

• Create a variable (Sample_Date) with string data type.

 

•  Select the variable and press "F4", then variable properties will open.

 

•  Set "EvaluateAsExpression" value as "True".
• Go to Expressions and write the below mentioned expression for current date.

Expression: 
SUBSTRING((DT_WSTR, 10) (DT_DBDATE) GETDATE(), 1,4)
+SUBSTRING((DT_WSTR, 10) (DT_DBDATE) GETDATE(), 6,2)
+SUBSTRING((DT_WSTR, 10) (DT_DBDATE) GETDATE(), 9,2)

• Save the SSIS package then we can see the variable value.