Here are some tips and shortcuts that you can use inside of the expression builder in ADF's Mapping Data Flows:
Ctrl-K Ctrl-C
: Comments entire lineCtrl-K Ctrl-U
: UncommentF1
: Provide editor help commandsAlt-Down Arrow
: Move current line downAlt-Up Arrow
: Move current line upCntrl-Space
: Show context help
/* This is a
multi-line comment */
// This is a single line comment
TIP: If you put a comment at the top of your expression, it will appear in the transformation text box to document your transformation expressions:
toString(toDate('28/03/2010', 'dd/MM/yyyy'), 'ddMMMyyyy')
= 28Mar2010
This list below is from the Java doc for date formats:
All letters 'A' to 'Z' and 'a' to 'z' are reserved as pattern letters. The following pattern letters are defined:
Symbol Meaning Presentation Examples ------ ------- ------------ ------- G era text AD; Anno Domini; A u year year 2004; 04 y year-of-era year 2004; 04 D day-of-year number 189 M/L month-of-year number/text 7; 07; Jul; July; J d day-of-month number 10 Q/q quarter-of-year number/text 3; 03; Q3; 3rd quarter Y week-based-year year 1996; 96 w week-of-week-based-year number 27 W week-of-month number 4 E day-of-week text Tue; Tuesday; T e/c localized day-of-week number/text 2; 02; Tue; Tuesday; T F week-of-month number 3 a am-pm-of-day text PM h clock-hour-of-am-pm (1-12) number 12 K hour-of-am-pm (0-11) number 0 k clock-hour-of-am-pm (1-24) number 0 H hour-of-day (0-23) number 0 m minute-of-hour number 30 s second-of-minute number 55 S fraction-of-second fraction 978 A milli-of-day number 1234 n nano-of-second number 987654321 N nano-of-day number 1234000000 V time-zone ID zone-id America/Los_Angeles; Z; -08:30 z time-zone name zone-name Pacific Standard Time; PST O localized zone-offset offset-O GMT+8; GMT+08:00; UTC-08:00; X zone-offset 'Z' for zero offset-X Z; -08; -0830; -08:30; -083015; -08:30:15; x zone-offset offset-x +0000; -08; -0830; -08:30; -083015; -08:30:15; Z zone-offset offset-Z +0000; -0800; -08:00; p pad next pad modifier 1 ' escape for text delimiter '' single quote literal ' [ optional section start ] optional section end # reserved for future use { reserved for future use } reserved for future use
'This is my string.' + ' This is my new string.' = This is my string. This is my new string
[ ]{2}|\.
,' ')^(\d+)
, 1)true()
Use it in your alter row filter will allow all rows to match that condition. Good for Upsert. No need to use 1==1.
Or, if you want inequality (1==0):
false()
Alter Row will tag rows with each matching rule. Check for row tags based on Alter Row matching rules:
isUpdate(), isUpsert(), isDelete(), isInsert()
When you are working in the ADF Data Flow UI, you can see the metadata as you construct your transformations. The metadata is based on the projection of the source plus the columns defined in transformations. However, in some instances, you do not get the metadata due to schema drift, column patterns, or dynamic transformations like Pivot that create column names on the fly. In that case, you byName():
toString(byName('mynewcol'))
Soundex(columnname)
isNull (col1, 'somevalue')
or
coalesce(expression)
After your Lookup transformation, you can use subsequent transformations to inspect the results of each matched row by using the expression function isMatch()
to make further choices in your logic based on whether or not the Lookup resulted in a row match or not.
regexReplace(mystring,`^a-zA-Z\d\s:`,'')
toString(toTimestamp('12/31/2016T00:12:00', 'MM/dd/yyyy\'T\'HH:mm:ss'), 'MM/dd /yyyy\'T\'HH:mm:ss')
Note that to include string literals in your timestamp output, you need to wrap your conversion inside of a toString().
Here is how to convert seconds from Epoch to a date or timestamp:
toTimestamp(seconds(1575250977))
DateReported2 = CASE WHEN DateReported is null THEN DateReported WHEN YEAR(DateReported) = 1899 THEN NULL ELSE DateReported End ...
Solution:
case(year(DateReported) != 1899, DateReported)
To get Row Counts in Data Flows, add an Aggregate transformation, leave the Group By empty, then use count(1)
as your aggregate function.
To get distinct rows in your Data Flows, use the Aggregate transformation, set the key(s) to use for distinct in your group by, then choose First($$)
or Last($$)
as your aggregate function using column patterns.
When you have column names that include special characters or spaces, surround the name with curly braces.
{[dbo].this_is my complex name$$$}
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.