Useful SSIS Expressions

Search Expressions

• If you want to find a specific text (e.g. 'Value') in a word ( underValue1), you could use following expression:
      FINDSTRING(col, "value", 1) > 0
• If you want to find a specific text (e.g. 'Value') at the beginning of a word (Value1), you could use following expression:
     FINDSTRING(col, "value", 1) == 1
• If you want to find a specific text (e.g. 'value') at the end of a word (underValue), you could use following expression:
     REVERSE(LEFT(REVERSE(col), X)) == "Value"

Assign NULL values (tested in version 2012 )

    (DT_STR, 4, 1252)NULL(DT_STR, 4, 1252)

Character replace expressionsUse hex notations to remove special characters in a fields of a Data Conversion transformation.

• TRIM(REPLACE(ColumnName,"\x0009","")) - Remove horizontal
• TRIM(REPLACE(ColumnName,"\x000D","")) - Remove carriage
• TRIM(REPLACE(ColumnName,"\x000A","")) - Remove line feeds
• TRIM(REPLACE(ColumnName,"\x00A0","")) - Remove Non breaking space (HTML tag - &nbsp)
  
  Hex codes added in SSIS expression should have 6 characters that should start with "\x" and end with 4 characters. Therefore we might need to add extra 0s. (e.g to replace "&" we should use REPLACE(ColumnName,"\x0026","") )
  
  Following websites can be used to find the hex code for known characters.
  http://www.ascii-code.com/
  
  

SSAS [Period Utility] calculations + Accumulated (Addictive) figures

The following calculated members return accumulated figures for a given measure and selected level in period dimension (you need to have a period (time) utility dimension in the cube in order to create this member).
YTD (Year To Date Calculation)
CREATE MEMBER CURRENTCUBE.[Period Utility].[Period Accumulations].[YTD (Operational)]
AS NULL,
FORMAT_STRING = "#,#.0",
VISIBLE = 1;    
SCOPE ([Period Utility].[Period Accumulations].[YTD (Operational)]);  
    this = SUM(
               PeriodsToDate(
                   [Period].[Operational Year - Quarter - Month].[Year],
                   [Period].[Operational Year - Quarter - Month].CurrentMember                   
               ), ([Measures].CurrentMember, [Period Utility].[Period Accumulations].DefaultMember)
           );  
END SCOPE;   Following MDX can be used to validate figures.
SELECT
(
{[Measures].[Stg - Measure Name]} * {[Period Utility].[Period Accumulations].[YTD (Operational)]}
) 
On Columns,
Non Empty {
[Period].[Operational Year - Quarter - Month].[Month].members
} On Rows
From [Cube Name]
 









QTD (Quater To Date Calculation)
CREATE MEMBER CURRENTCUBE.[Period Utility].[Period Accumulations].[QTD (Operational)]
AS NULL,
FORMAT_STRING = "#,#.0",
VISIBLE = 1;    
SCOPE ([Period Utility].[Period Accumulations].[QTD (Operational)]);  
    this = SUM(
               PeriodsToDate(
                   [Period].[Operational Year - Quarter - Month].[Quarter],
                   [Period].[Operational Year - Quarter - Month].CurrentMember                   
               ), ([Measures].CurrentMember, [Period Utility].[Period Accumulations].DefaultMember)
           );  
END SCOPE;   

Following MDX can be used to validate figures.
SELECT
(
{[Measures].[Stg - Measure Name]} * {[Period Utility].[Period Accumulations].[QTD (Operational)]}
) 
On Columns,
Non Empty {
[Period].[Operational Year - Quarter - Month].[Month].members
} On Rows
From [Cube Name]