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

Question

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?

Ferdipux · Accepted Answer · 2019-10-11 09:38:35Z

1

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.

edited Oct 11, 2019 at 9:38

answered Oct 11, 2019 at 5:25

Ferdipux

5,2891 gold badge22 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Hadi · Accepted Answer · 2019-10-11 20:54:10Z

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)

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related