How to build a dynamic connection string in SSIS with variable number of whitespaces

user_11738260 0
Viewed 73

I am trying to build a dynamic connection string in SSIS using the Expression builder. The files I want to connect to have a naming convention that ends with the current date in the format - Oct 10 2019. The problem I have, is that when the day part of the date is single digits, there are two whitespaces between the month part and the day part. e.g - Extract Oct__9 2019.

The expression I have so far only works for single-digit days:

@[User::OpenCasesLoadDir] + "Open Exception Reporting Cases- Extract "+

(MONTH(getdate()) == 1 ? "Jan" : MONTH(getdate()) == 2 ? " Feb" : MONTH(getdate()) == 3 ? "Mar" :
  MONTH(getdate()) == 4 ? "Apr" : MONTH(getdate()) == 5 ? "May" : MONTH(getdate()) == 6 ? "June" :
  MONTH(getdate()) == 7 ? "July" : MONTH(getdate()) == 8 ? "Aug" : MONTH(getdate()) == 9 ? "Sep" :
  MONTH(getdate()) == 10 ? "Oct" : MONTH(getdate()) == 11 ? "Nov" : MONTH(getdate()) == 12? "Dec":"")

+ "  " + (DT_STR,4,1252)DAY( DATEADD( "dd", -1, getdate() )) + " " + (DT_STR,4,1252)YEAR( DATEADD( "dd", -1, getdate() )) + ".csv"

Is there way to adapt this expression so that there are two leading white spaces for single-digit days and one leading white space for double digit days?

etl ssis expression sql-server
2 Answers

Function fragment you requested 

RIGHT(" " + (DT_WSTR,2)DAY(GETDATE()), 2)

How it works?
It gets num of days the current date and then - converts it to string with max length of 2. Then it prepends the space and gets 2 characters from the right side of the string. If day number is a single-digit day, the prepended space is selected, otherwise - not.

user_10106220

Try using the following expression:

@[User::OpenCasesLoadDir] + "Open Exception Reporting Cases- Extract " +
(MONTH(getdate()) == 1 ? "Jan" : MONTH(getdate()) == 2 ? " Feb" : MONTH(getdate()) == 3 ? "Mar" :
 MONTH(getdate()) == 4 ? "Apr" : MONTH(getdate()) == 5 ? "May" : MONTH(getdate()) == 6 ? "June" :
 MONTH(getdate()) == 7 ? "July" : MONTH(getdate()) == 8 ? "Aug" : MONTH(getdate()) == 9 ? "Sep" :
 MONTH(getdate()) == 10 ? "Oct" : MONTH(getdate()) == 11 ? "Nov" : MONTH(getdate()) == 12? "Dec":"")
+  RIGHT("  " + (DT_WSTR,2)DAY(GETDATE()), 3) 
+ " " +(DT_STR,4,1252)YEAR( DATEADD( "dd", -1, getdate() )) + ".csv"

Using this expression:

  • If day is a two digits value, then only one leading space is inserted.
  • If it is a one digit value, two leading spaces are inserted.

Changes made:

I replaced 

"  " + (DT_STR,4,1252)DAY( DATEADD( "dd", -1, getdate() ))

With

RIGHT("  " + (DT_WSTR,2)DAY(GETDATE()), 3)
user_70312300
Related