Mar 19, 2015

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

Mar 18, 2015

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.  

Mar 9, 2015

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

Mar 8, 2015

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