SSIS expressions I often use

expression

If either you are doing your SSIS expressions by hand or using the BIML framework, you’ve came across the expressions and the expression-builder.

This is a helper list, with my most often used, and wich are allways forgotten when I need them, of my commonly used SSIS expressions.

Strings

Filename from fully qualified pathstring

  • RIGHT([FilePath],FINDSTRING(REVERSE([FilePath]),”\\”,1) – 1)

Folderpath from fully qualified pathstring

  • SUBSTRING([FilePath], 1, LEN([FilePath]) – FINDSTRING(REVERSE([FilePath] ), “\\” ,1 ) + 1)

Foldername from fully qualified pathstring

  • TOKEN[FilePath],”\\”,TOKENCOUNT([FilePath],”\\”) – 1)

This is only for SSIS2012 and onwards. The TOKEN and TOKENCOUNT expressions are not in prior versions of SQL Server

For prior versions of SQL Server:

  • SUBSTRING([FilePath],LEN([FilePath]) – FINDSTRING(REVERSE([FilePath]),”\\”,2) + 2,(LEN([FilePath]) – FINDSTRING(REVERSE([FilePath]),”\\”,1)) – (LEN([FilePath]) – FINDSTRING(REVERSE([FilePath]),”\\”,2)) – 1)

Replace empty strings

With SQL Server 2012 the new REPLACENULL function was implemented, making it alot easier to replace empty values.

  • REPLACENULL([ColumnName], [replace value])

For earlier versions of SQL Server

  • LEN([ColumnName]) == 0 ? [replace value] : [ColumnName]
  • ISNULL([ColumnName]) ? [replace value] : [ColumnName]

Date and time

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()

Time from datetime

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()

First day of the current month

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())

Last day of the current month

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()))

And if you realy want the last second of the current month 30-06-2011 23:59:59

  • DATEADD(“s”, -1,DATEADD(“d”, -DAY(GETDATE()) + 1, DATEADD(“m”, 1, (DT_DBTIMESTAMP)(DT_DBDATE)GETDATE())))

Weeknumber of the month

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

Datetime as concatenated string

  • (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)

Få besked om næste indlæg

Skriv dig gerne op til at modtage en mail, ved næste indlæg. Det kan du gøre nedenfor.



Marketing stuff

Our emails contain marketing stuff, so we need to give you some fine quality fine print: brianbonk will use the information you provide on this form to email you with updates and marketing. You can change your mind at any time by clicking the unsubscribe link in the footer of any email you receive from us, or by contacting us at help@brianbonk.dk. We use Mailchimp as our marketing platform. By checking the box to subscribe, you acknowledge that your information will be transferred to Mailchimp for processing, and that we may process your information in accordance with these terms.

Følg mig på Instagram

Leave a Reply

Your email address will not be published. Required fields are marked *

en_USEnglish