Function & SSIS Expression to Retrieve MonthNumber in YYYYMM Format

TSQL Function
E.g. 1. Following function can be used to return Month number in YYYYMM format for a month name in "MON-YYYY", "MON YYYY", "MON/YYYY" Formats.

CREATE FUNCTION dbo.[Retrieve MonthNumber(YYYYMM)](@MonthName Varchar(50), @MonthNameFormat Varchar(50))
RETURNS INT AS
BEGIN
    DECLARE @MonthNumber INT;
    SET @MonthName = UPPER(@MonthName);
    RETURN CASE
        WHEN UPPER(@MonthNameFormat) = 'MON-YYYY' OR UPPER(@MonthNameFormat) = 'MON YYYY' THEN
            CASE
                WHEN LEFT(@MonthName, 3) = 'JAN' THEN  CAST(RIGHT(@MonthName,4) + '01' AS INTEGER)
                WHEN LEFT(@MonthName, 3) = 'FEB' THEN  CAST(RIGHT(@MonthName,4) + '02' AS INTEGER)
                WHEN LEFT(@MonthName, 3) = 'MAR' THEN  CAST(RIGHT(@MonthName,4) + '03' AS INTEGER)
                WHEN LEFT(@MonthName, 3) = 'APR' THEN  CAST(RIGHT(@MonthName,4) + '04' AS INTEGER)
                WHEN LEFT(@MonthName, 3) = 'MAY' THEN  CAST(RIGHT(@MonthName,4) + '05' AS INTEGER)
                WHEN LEFT(@MonthName, 3) = 'JUN' THEN  CAST(RIGHT(@MonthName,4) + '06' AS INTEGER)
                WHEN LEFT(@MonthName, 3) = 'JUL' THEN  CAST(RIGHT(@MonthName,4) + '07' AS INTEGER)
                WHEN LEFT(@MonthName, 3) = 'AUG' THEN  CAST(RIGHT(@MonthName,4) + '08' AS INTEGER)
                WHEN LEFT(@MonthName, 3) = 'SEP' THEN  CAST(RIGHT(@MonthName,4) + '09' AS INTEGER)
                WHEN LEFT(@MonthName, 3) = 'OCT' THEN  CAST(RIGHT(@MonthName,4) + '10' AS INTEGER)
                WHEN LEFT(@MonthName, 3) = 'NOV' THEN  CAST(RIGHT(@MonthName,4) + '11' AS INTEGER)
                WHEN LEFT(@MonthName, 3) = 'DEC' THEN  CAST(RIGHT(@MonthName,4) + '12' AS INTEGER)
                ELSE
                    0
            END
        WHEN UPPER(@MonthNameFormat) = 'MM/YYYY' OR UPPER(@MonthNameFormat) = 'MM-YYYY' THEN
            CASE
                WHEN LEFT(@MonthName, 2) = '01' THEN  CAST(RIGHT(@MonthName,4) + '01' AS INTEGER)
                WHEN LEFT(@MonthName, 2) = '02' THEN  CAST(RIGHT(@MonthName,4) + '02' AS INTEGER)
                WHEN LEFT(@MonthName, 2) = '03' THEN  CAST(RIGHT(@MonthName,4) + '03' AS INTEGER)
                WHEN LEFT(@MonthName, 2) = '04' THEN  CAST(RIGHT(@MonthName,4) + '04' AS INTEGER)
                WHEN LEFT(@MonthName, 2) = '05' THEN  CAST(RIGHT(@MonthName,4) + '05' AS INTEGER)
                WHEN LEFT(@MonthName, 2) = '06' THEN  CAST(RIGHT(@MonthName,4) + '06' AS INTEGER)
                WHEN LEFT(@MonthName, 2) = '07' THEN  CAST(RIGHT(@MonthName,4) + '07' AS INTEGER)
                WHEN LEFT(@MonthName, 2) = '08' THEN  CAST(RIGHT(@MonthName,4) + '08' AS INTEGER)
                WHEN LEFT(@MonthName, 2) = '09' THEN  CAST(RIGHT(@MonthName,4) + '09' AS INTEGER)
                WHEN LEFT(@MonthName, 2) = '10' THEN  CAST(RIGHT(@MonthName,4) + '10' AS INTEGER)
                WHEN LEFT(@MonthName, 2) = '11' THEN  CAST(RIGHT(@MonthName,4) + '11' AS INTEGER)
                WHEN LEFT(@MonthName, 2) = '12' THEN  CAST(RIGHT(@MonthName,4) + '12' AS INTEGER)
                ELSE
                    0
            END
    ELSE
        0
    END
END;
GO


Following qeury can be used to test / execute above function.
SELECT dbo.[Retrieve MonthNumber(YYYYMM)]('01/2015', 'MM/YYYY')

E.g. 2. Following function can be used to return Month number in YYYYMM format for a month name in "### Mon  YYYY" format.

CREATE FUNCTION [dbo].[GetMonthID] (
@Period VARCHAR(50))
RETURNS INT
AS
BEGIN
DECLARE @Year VARCHAR(50) = 0;
DECLARE @Month VARCHAR(50)= 0;
DECLARE @MonthID INT = NULL;
  
SELECT @Year = SUBSTRING( @Period, CHARINDEX('20',@Period, 1) , 4)
SELECT @Month = CASE 
WHEN @Period LIKE '%JAN%' THEN '01' 
WHEN @Period LIKE '%FEB%' THEN '02' 
WHEN @Period LIKE '%MAR%' THEN '03' 
WHEN @Period LIKE '%APR%' THEN '04' 
WHEN @Period LIKE '%MAY%' THEN '05' 
WHEN @Period LIKE '%JUN%' THEN '06' 
WHEN @Period LIKE '%JUL%' THEN '07' 
WHEN @Period LIKE '%AUG%' THEN '08' 
WHEN @Period LIKE '%SEP%' THEN '09' 
WHEN @Period LIKE '%OCT%' THEN '10' 
WHEN @Period LIKE '%NOV%' THEN '11' 
WHEN @Period LIKE '%DEC%' THEN '12' 
END
IF ISNUMERIC(@Year + @Month) = 1
SET @MonthID = CAST(@Year + @Month AS INT);
ELSE 
SET @MonthID = NULL

RETURN @MonthID

END;

Query
SELECT dbo.GetMonthID([Time Period NAME]) FROM dbo.TableName

SSIS Expression
Following expression can be used to return Month number in YYYYMM format for a month name in "MON-YYYY", "MON YYYY", "MON/YYYY" Formats.
(DT_I4)(SUBSTRING(Month,4,2) + RIGHT(Month,2) +
    (    FINDSTRING(Month,"JAN",1) > 0 ? "01" :
        FINDSTRING(Month,"FEB",1) > 0 ? "02" :
        FINDSTRING(Month,"MAR",1) > 0 ? "03" :
        FINDSTRING(Month,"APR",1) > 0 ? "04" :
        FINDSTRING(Month,"MAY",1) > 0 ? "05" :
        FINDSTRING(Month,"JUN",1) > 0 ? "06" :
        FINDSTRING(Month,"JUL",1) > 0 ? "07" :
        FINDSTRING(Month,"AUG",1) > 0 ? "08" :
        FINDSTRING(Month,"SEP",1) > 0 ? "09" :
        FINDSTRING(Month,"OCT",1) > 0 ? "10" :
        FINDSTRING(Month,"NOV",1) > 0 ? "11" :
        FINDSTRING(Month,"DEC",1) > 0 ? "12" :
        "00"
        )
    )

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/
  
  

SSIS 2005 - Read data from an excel 2007 file and format it

In order to read data from an Excel 2007 file, create an OLE DB connection and add file path and the file name as the server name.


Read data from [CountryOverview] sheet.
If you select "Table name or View name" as the data access mode, you could select the sheet name which you wish to read from the drop down menu. Instead of that, as you can see in the image below, a SQL query can be writted to read data from excel sheet.