Character Map Transform very simple example in SSIS | Demo

Character Map Transform is used to perform different operations on string data.  List of all character map transform:

a. Byte reversal

b. Full width

c. Half width

d. Hiragana

e. Katakana

f. Linguistic casing

g. Lowercase

h. Simplified Chinese

i. Traditional Chinese

j. Uppercase

Character Map Transform very simple example step by step for beginners:

Step 1: Drag and drop any source and a character map transform inside data flow task.  In this example our source adapter is ADO NET Source which pulls data from table tblUser. Then connect the source adapter to character map transform.    

Character MAP Transform Beginner simple example SSIS

Step 2: Double click on character map transformation. It will pop up character map transformation editor.

Input Column:  From drop down choose any string data type columns. We can choose same column multiple times.

Destination:  it can be either new column or in-place change. If you want keep original column then choose new column otherwise choose in-place changed. If same column is chosen multiple times then only one column can have in-place change option.

Operation: Choose any operation from drop down according to your need. At the same time we can choose multiple operations.

Output Alias:  We can rename the output columns. New name of all output columns must be unique.  

Character MAP transform operation 

Step 3:  Now add any destination adapter and execute the package. In this example our destination adapter is ADO NET Destination.

Execute Character Map Transform SSIS

Cache transform very simple example in SSIS | demo

What is cache transform in SSIS? Or Explanation and use of cache transformation

Cache transform is cached dataset for lookup transformation. Let's assume we are loading user information but user data keeps country id and we have to replace country id    by country name while country information are present in some different data source.

Approach 1:  First populate all country data into an intermediate temporary table and while loading user's information perform join operation with temporary country table.  

Audit Transformation SSIS simple example

What is audit transformation in SSIS?

Audit transformation is a component of the data flow task.   It is used to keep the auditing information of package in case of success or failure. This auditing information includes:

a. Execution instance GUID

b. Package ID

c. Package name

d. Version ID

e. Execution start time

f. Machine name

g. User name

h. Task name

i. Task ID

Configuration | Demo of Audit Transformation SSIS by very simple example step by step 1

Step 1: Create any source and destination transformations.

Step 2: Add audit transformation between source and destitution.

Step 3: Double click on Audit transformation.

SSIS Audit Transformation Example

Step 4: Run the package!!

Audit transformation demo 

Sample output:

ntUserID	Package name	Machine name	User name	Execution start time
1	Package	RITESH-PC	Ritesh-PC\Ritesh	2015-03-09 08:43:17.000
1	Package	RITESH-PC	Ritesh-PC\Ritesh	2015-03-09 08:43:17.000
1	Package	RITESH-PC	Ritesh-PC\Ritesh	2015-03-09 08:43:17.000
1	Package	RITESH-PC	Ritesh-PC\Ritesh	2015-03-09 08:43:17.000
2	Package	RITESH-PC	Ritesh-PC\Ritesh	2015-03-09 08:43:17.000
2	Package	RITESH-PC	Ritesh-PC\Ritesh	2015-03-09 08:43:17.000
2	Package	RITESH-PC	Ritesh-PC\Ritesh	2015-03-09 08:43:17.000
2	Package	RITESH-PC	Ritesh-PC\Ritesh	2015-03-09 08:43:17.000

Understand execution Tree and configure | enable in SSIS

What is execution Tree in SSIS?

Execution tree is property of data flow task.  It describes how many data buffers need to be allocated to perform data flow task.  Execution tree is group of transformation which start from source adapter or blocking\semi-blocking transformation and end with first blocking\semi-blocking transformation or destination adapters. For examples:

Understand Execution Tree SSIS

Categorization of different component of data flow task:

Source Adapters	Non-Blocking Transformations	Semi-Blocking Transformations	Blocking Transformations	Destination Adapters
ADO NET Source	Audit	Data Mining Query	Aggregate	ADO NET Destination
Excel Source	Character Map	Merge	Fuzzy Grouping	Data Mining Model Training
Flat File Source	Conditional Split	Merge Join	Fuzzy Lookup	DataReaderDest
OLE DB Source	Copy Column	Pivot	Row Sampling	Dimension Processing
Raw File Source	Data Conversion	Unpivot	Sort	Excel Destination
XML Source	Derived Column	Term Lookup	Term Extraction	Flat File Destination
	Lookup	Union All		OLE DB Destination
	Multicast			Partition Processing
	Percent Sampling			Raw File Destination
	Row Count			Recordset Destination
	Script Component			SQL Server Compact Destination
	Export Column			SQL Server Destination
	Import Column
	Slowly Changing Dimension
	OLE DB Command

How to count total execution tree:

To count execution tree first we have to categorize all the transformation of data flow components in source adapters, non-blocking transformations, semi-blocking transformations, blocking transformation and destination adapters. Follow below rule:

Start of execution tree: Source adapters or Semi-blocking or Blocking transformations

End of execution tree:  Destination adapters or Semi-blocking or Blocking transformations

For example, in below data flow task:

Data Flow task execution Tree

a. Source adapters: ADO NET Source

b. Destination Adapters: ADO NET Destination, ADO NET Destination 1, ADO NET Destination 2

c. Non-Blocking transformations: Multicast, Derived Column

d. Semi-blocking transformations: Union All

e. Blocking Transformations: Aggregate, Sort

In the above data flow task has total 7 execution trees as shown in below diagram:

Count execution Tree SSIS

How to configuration | enable execution tree step by step:

For this we have to configure logging of data flow task for event PipelineExecutionTrees.

Step 1: Right click on control flow task area and click on logging:

Enable execution tree 

Step 2: In configure SSIS Logs window, Click on package (left most) then add any log provider type. In the example we are choosing SSIS log provider for text file.

SSIS Add text log providers

Step 3: Now select only data flow task (left most) then select SSIS log provider for text file.

Configuring execution tree log in data flow task

Click on configuration and browse the path where you want to save log.

Adding text file SSIS execution tree

Then click on details tab.

Step 4: Select only PipelineExecutionTrees event:

Execution tree SSIS pipelineexecutiontrees event

Step 5: Now run your package to get the all execution tree logs. After running it you may get below data in your execution log file:

Begin Path 0

   output "ADO NET Source Output" (134); component "ADO NET Source" (129)

   input "Aggregate Input 1" (145); component "Aggregate" (144)

End Path 0

Begin Path 1

   output "Aggregate Output 1" (146); component "Aggregate" (144)

   input "Multicast Input 1" (449); component "Multicast" (448)

   Begin Subpath 0

      output "Multicast Output 1" (450); component "Multicast" (448)

      input "Derived Column Input" (453); component "Derived Column" (452)

      output "Derived Column Output" (454); component "Derived Column" (452)

      input "ADO NET Destination Input" (220); component "ADO NET Destination" (217)

   End Subpath 0

   Begin Subpath 1

      output "Multicast Output 2" (459); component "Multicast" (448)

      input "Union All Input 1" (461); component "Union All" (460)

   End Subpath 1

   Begin Subpath 2

      output "Multicast Output 3" (464); component "Multicast" (448)

      input "Union All Input 2" (474); component "Union All" (460)

   End Subpath 2

End Path 1

Begin Path 2

   output "Aggregate Output 2" (203); component "Aggregate" (144)

   input "Sort Input" (442); component "Sort" (441)

End Path 2

Begin Path 3

   output "Sort Output" (443); component "Sort" (441)

   input "ADO NET Destination Input" (232); component "ADO NET Destination 1" (229)

End Path 3

Begin Path 4

   output "Union All Output 1" (462); component "Union All" (460)

   input "ADO NET Destination Input" (513); component "ADO NET Destination 2" (510)

End Path 4 

Multiple output of aggregate transformation configuration SSIS

Aggregate transformation or component can have only input but more than one \multiple output. It means there is no need to create multiple data source or multi cast transformation.   In this example we are going to illustrate how to configure two output pipelines in aggregate transformation. Let's assume a table named tblUserMarks with below data:

UserMarksID	ntUserID	vcSubjectName	ntMarks
1	1	SQL Server	30
2	1	SSRS	20
3	1	SSIS	25
4	1	SSAS	15
5	2	SQL Server	32
6	2	SSRS	23
7	2	SSIS	28
8	2	SSAS	16

We to execute below query using Aggregate Transformation:

Query 1:

SELECT

     ntUserID,

     COUNT(*) AS ntTotalCount,

     SUM(ntMarks) AS ntTotalMarks

FROM tblUserMarks

GROUP BY ntUserID

 Query 2:

SELECT

     vcSubjectName,

     AVG(ntMarks) AS ntAvgMarks

FROM tblUserMarks      

GROUP BY vcSubjectName

Step 1: Add any source inside data flow task. In this example our data source is SQL Server. Connect source transformation to database and choose your source table.

Step 2: Drag and drop Aggregate transformation from toolbox.

Step 4:  Double click on Aggregate transformation. It will pop up aggregate transformation editor.

Step 5: Configured the aggregate transformation editor for query 1.

Multiple output Aggregate configuring first query

Step 6: To configure the aggregate for query 2 we will have to click on advanced button which is inside the red rectangle in the above diagram.

Multiple output Aggregate Advance Section

Step 7: Here Aggregate output 1 is for query 1. In the same we are going to configure Aggregate Output 2 for query 2:

Multiple output Aggregate configuration second 

Step 8: Now creating two sql server destinations and executing the package!!

Multiple output Aggregate Running