Handling Null Date in SSIS Derived Column

To Replace Null Value with Current Date:

ISNULL((DT_DBTIMESTAMP)SUBSTRING((DT_STR,30,1252)modifiedon,1,10)) ? (DT_DBTIMESTAMP)SUBSTRING((DT_STR,30,1252)GETDATE(),1,10) : (DT_DBTIMESTAMP)SUBSTRING((DT_STR,30,1252)modifiedon,1,10)

The below conversion replace null value with 'Null' instead of allowing column to insert default '1900-01-01....' value.

ISNULL((DT_DBTIMESTAMP)SUBSTRING((DT_STR,30,1252)PromisedShipDate,1,10)) ? NULL(DT_DATE) : (DT_DBTIMESTAMP)SUBSTRING((DT_STR,30,1252)PromisedShipDate,1,10)

Configure connection String for Excel Source in Expression property

Configure connection String for Excel Source in Expression property

Step 1: Create variable containg excel file name.

Step 2: Go to Excel Data Source Properties, and in expression select Connection string and give expression as like below:
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\Datafiles\\TestDrive\\Pending\\" + @[User::FileName] + ";Extended Properties=\"EXCEL 8.0;HDR=YES\";"

To get Sales of Current Year, Previous Year, etc using Lag anf Lead functions

Current Year Sales:

SELECT [Measures].[Sales (000's)] on ROws,
{CROSSJOIN([Date].[Fiscal Months].[Fiscal Months].Allmembers,STRTOMEMBER("[Date].[Financial Period].[Year].&["+ CASE WHEN
CINT(FORMAT(NOW(),"MM")) >= 4 THEN FORMAT(NOW(),"yyyy") ELSE  CSTR(CINT(FORMAT(NOW(),"yyyy"))-1) END +"]"))}
on Columns
FROM [Sales]

Result

Previous year Sales

SELECT [Measures].[Sales (000's)] on ROws,
{CROSSJOIN([Date].[Fiscal Months].[Fiscal Months].Allmembers,STRTOMEMBER("[Date].[Financial Period].[Year].&["+ CASE WHEN
CINT(FORMAT(NOW(),"MM")) >= 4 THEN FORMAT(NOW(),"yyyy") ELSE  CSTR(CINT(FORMAT(NOW(),"yyyy"))-1) END +"]").lag(1))}on Columns
FROM [Sales]

SELECT [Measures].[Sales (000's)] on ROws,
{CROSSJOIN([Date].[Fiscal Months].[Fiscal Months].Allmembers,STRTOMEMBER("[Date].[Financial Period].[Year].&["+ CASE WHEN
CINT(FORMAT(NOW(),"MM")) >= 4 THEN FORMAT(NOW(),"yyyy") ELSE  CSTR(CINT(FORMAT(NOW(),"yyyy"))-1) END +"]").lead(-1))}
on Columns
FROM [Sales]

Result

How to display all Labels in X-axis

To display all labels of x-axis, go to Interval property of X-Axis label and give value as '1'.

Named Set for Months in Current Financial Year

ORDER(
StrToMember("[Date].[Financial Period].[Year].&[" +
CASE WHEN CINT(Format(Now(),"MM"))  >= 4 THEN Format(Now(), "yyyy")
ELSE CSTR(CINT(Format(Now(), "yyyy")) - 1) END + "].&[" +
CASE WHEN CINT(Format(Now(),"MM"))  >= 4 THEN Format(Now(), "yyyy")
ELSE CSTR(CINT(Format(Now(), "yyyy")) - 1) END + "]&[1].&[" +
CASE WHEN CINT(Format(Now(),"MM"))  >= 4 THEN Format(Now(), "yyyy")
ELSE CSTR(CINT(Format(Now(), "yyyy")) - 1) END + "]&[1]")
:
StrToMember("[Date].[Financial Period].[Year].&[" +
CASE WHEN CINT(Format(Now(),"MM"))  >= 4 THEN Format(Now(), "yyyy")
ELSE CSTR(CINT(Format(Now(), "yyyy")) - 1) END + "].&[" +
CASE WHEN CINT(Format(Now(),"MM"))  >= 4 THEN Format(Now(), "yyyy")
ELSE CSTR(CINT(Format(Now(), "yyyy")) - 1) END + "]&[" +
CASE WHEN CINT(Format(Now(),"MM"))  >= 4 THEN  CSTR(CINT(DATEPART("q", Now())) - 1)
ELSE  CSTR(CINT(DATEPART("q", Now())) + 3) END + "].&[" +
CASE WHEN CINT(Format(Now(),"MM"))  >= 4 THEN Format(Now(), "yyyy")
ELSE CSTR(CINT(Format(Now(), "yyyy")) - 1) END + "]&[" +
CASE WHEN CINT(Format(Now(),"MM"))  >= 4 THEN CSTR(CINT(Format(Now(),"MM")) - 3)
ELSE CSTR(CINT(Format(Now(),"MM")) + 9) END + "]"
)
,[Date].[Financial Period].CURRENTMEMBER.PROPERTIES("ID", TYPED), DESC)

MDX named Set for Financial Year

Current Financial Year

STRTOMEMBER("[Date].[Financial Period].[Year].&["+
  CASE WHEN CINT(Format(Now(),"MM"))  >= 4 THEN Format(Now(), "yyyy")
  ELSE CSTR(CINT(Format(Now(), "yyyy")) - 1) END + "]")

Last Financial Year

STRTOMEMBER("[Date].[Financial Period].[Year].&["+
  CASE WHEN CINT(Format(Now(),"MM"))  >= 4 THEN Format(Now(), "yyyy") -1
  ELSE CSTR(CINT(Format(Now(), "yyyy")) - 2) END + "]")