And because the expression builder of SSIS is quite worthless, the opensource addon SSIS Expression Editor & Tester could be very useful.
Filename from pathstring
This expression reverses the string to find the position of the last \ and uses that number (minus 1) in the Right expression:
- RIGHT([FilePath],FINDSTRING(REVERSE([FilePath]),"\\",1) - 1)
- RIGHT(@[User::FilePath],FINDSTRING(REVERSE(@[User::FilePath]),"\\",1) - 1)
Folderpath from pathstring
This expressions reverses the string to find the position of the last \ and deduct that number of the total length. After that you can use that number in a substring.
- SUBSTRING(@[User::FilePath], 1,
LEN(@[User::FilePath]) - FINDSTRING(REVERSE(@[User::FilePath] ), "\\" ,1 ) + 1
)
Foldername from the pathstring
This expression finds the last and second last / in the filepath and uses the those positions to find the foldername.
- SUBSTRING(@[User::FilePath],
LEN(@[User::FilePath]) - FINDSTRING(REVERSE(@[User::FilePath]),"\\",2) + 2,
(LEN(@[User::FilePath]) - FINDSTRING(REVERSE(@[User::FilePath]),"\\",1)) - (LEN(@[User::FilePath]) - FINDSTRING(REVERSE(@[User::FilePath]),"\\",2)) - 1
)
In SSIS 2012 it's a lot easier/shorter with the token expression:
- TOKEN(@[User::FilePath],"\\",TOKENCOUNT(@[User::FilePath],"\\") - 1)
If you want to give a default value for empty columns, you can check the length or you can check for NULL or you can even check both:
- LEN([STRING_COLUMN]) == 0 ? "default value" : [STRING_COLUMN]
- ISNULL([STRING_COLUMN]) ? "default value" : [STRING_COLUMN]
- ISNULL([STRING_COLUMN]) || LEN([STRING_COLUMN]) == 0 ? "default" : [STRING_COLUMN]
Leading zeros
These two expression will add up to 5 leading zeros to a number. So "123" becomes "00123":
- RIGHT(("00000" + [STRING_COLUMN]), 5)
- REPLICATE("0", 5 - LEN([STRING_COLUMN])) + [STRING_COLUMN]
Date from datetime
If you want to remove the time element in a datetime object, you should cast it to DT_DBDATE. But because that datatype is very inconvenient to use, you should cast it back to the original datatype. That will set the time to 0:00.
- (DT_DATE)(DT_DBDATE)@[User::datetimeVariable]
- (DT_DATE)(DT_DBDATE)[datetimeColumn]
- (DT_DBTIMESTAMP)(DT_DBDATE)GETDATE()
If you want to remove the date element in a datetime object, you should cast it to DT_DBTIME. And optional cast it to a string.
- (DT_STR,8,1252)(DT_DBTIME)@[User::datetimeVariable]
- (DT_STR,8,1252)(DT_DBTIME)[datetimeColumn]
- (DT_STR,8,1252)(DT_DBTIME)GETDATE()
If you want to get the first day of the current month, you take the current datetime and deduct the current day number (minus 1). Optional you can remove the time part:
- DATEADD("d", -DAY(GETDATE()) + 1, GETDATE())
- (DT_DBTIMESTAMP)(DT_DBDATE)DATEADD("d", -DAY(GETDATE()) + 1, GETDATE())
If you want to get the last day of the current month, you add 1 month and deduct the current day number. Optional you can remove the time part:
- DATEADD("d", -DAY(GETDATE()), DATEADD("m", 1, GETDATE()))
- (DT_DBTIMESTAMP)(DT_DBDATE)DATEADD("d", -DAY(GETDATE()), DATEADD("m", 1, GETDATE()))
- DATEADD("s", -1,DATEADD("d", -DAY(GETDATE()) + 1, DATEADD("m", 1, (DT_DBTIMESTAMP)(DT_DBDATE)GETDATE())))
Weeknumber of the month (see)
1-june-2012 is weeknumber 23 in the year, but weeknumber 1 of the month june 2012.
- (DATEPART("ww",[YourDate]) - DATEPART("ww",DATEADD("d", -DAY([YourDate]) + 1, [YourDate]))) + 1
This is excellent collection. My one cent will be the expression to create a dynamic file with date/timestamp:
ReplyDelete(DT_STR, 4, 1252)DATEPART("yyyy", @[System::StartTime]) +
RIGHT("0" + (DT_STR, 2, 1252)DATEPART("mm", @[System::StartTime]), 2) +
RIGHT("0" + (DT_STR, 2, 1252)DATEPART("dd", @[System::StartTime]), 2) +
RIGHT("0" + (DT_STR, 2, 1252)DATEPART("hh", @[System::StartTime]), 2) +
RIGHT("0" + (DT_STR, 2, 1252)DATEPART("mi", @[System::StartTime]), 2) +
RIGHT("0" + (DT_STR, 2, 1252)DATEPART("ss", @[System::StartTime]), 2)
Still useful in late 2015! Thanks for saving me the typing (and searching for a datepart parameter reference)
DeleteCurrent time stapmt in proper date format
Delete(DT_STR, 4, 1252)DATEPART("yyyy", GetDate()) + "-"+
RIGHT("0" + (DT_STR, 2, 1252)DATEPART("mm", GetDate()), 2) + "-"+
RIGHT("0" + (DT_STR, 2, 1252)DATEPART("dd", GetDate()), 2) +" "+
RIGHT("0" + (DT_STR, 2, 1252)DATEPART("hh", GetDate()), 2) +":"+
RIGHT("0" + (DT_STR, 2, 1252)DATEPART("mi", GetDate()), 2) +":"+
RIGHT("0" + (DT_STR, 2, 1252)DATEPART("ss", GetDate()), 2)
A workaround I needed to do for...
ReplyDelete"RIGHT(@[User::FilePath],FINDSTRING(REVERSE(@[User::FilePath]),"\\",1) - 1)"
...to keep Expression Builder from complaining about a "-1" in my RIGHT() statement, was to give User::FilePath a dummy default value of "\\blah.txt" so that a "\\" is found, even pre-runtime (at runtime, this is of course replaced by actual file paths from my ForEach enumerator)
(I added the blah.txt part too because I expanded on this function to also strip out the .txt part because I just wanted filename w/o extension)