KingswaySoft

SSIS Premium Derived Column Component

Easily perform advanced transformations without any custom scripts.

KingswaySoft

Premium Derived Column

The Premium Derived Column Component, part of the SSIS Productivity Pack, expands on the native SSIS Derived Column to give developers more power in SSIS to easily perform advanced transformations without custom scripts. It is offers all the functionality of the SSIS out-of-box Derived Column component and enhances it to create a component with greater potentials, features, and ease-of use.

The Premium Derived Column component is the ultimate component for SSIS developers. With over 294 functions, the Premium Derived Column component allows developers to create advanced expressions easily for SSIS transformations not once possible without custom scripts.

SSIS Premium Derived Column

Try it now for Free

Feature Out-of-Box Derived Column Component
Premium Derived Column Component

Add Columns


Yes


Yes
Utilize Expressions to Transform Data
Yes
Yes
Number of Functions <100 >294
Function Search Bar
No
Yes

Error Handling

  • Fail on error
  • Ignore error
  • Redirect to output
Yes
Yes
Advanced Data Type Detection
No
Yes
Advanced Expression Editor
No


Yes

Quickly build advanced expressions in SSIS.
Expression Validation

No


Yes

Evaluate and validate every expression with one click.
Expression Testing
No


Yes

Enter test values to test expressions prior to execution.

Category Index

Premium Derived Column Functions

Below is a list of function categories included within the Premium Derived Column. You may click on each category to see the list of available functions with a brief description on what it achieves.

Comments (2)

  • /* */ (Multiline Comment)
  • // (Singleline Comment)

Compression Functions (6)

  • DeflateCompress: Returns the «bytes» as compressed DEFLATE bytes.
  • DeflateDecompress: Returns the DEFLATE «bytes» as uncompressed data.
  • GzipCompress: Returns the «bytes» as compressed GZIP bytes.
  • GzipDecompress: Returns the GZIP «bytes» as uncompressed data.
  • Unzip: Returns the «bytes» of the file whose name is «filename» from a zipfile («bytes») whose password is «password».
  • Zip: Returns the «bytes» of a zip file that contains a file with the name of «filename» and contents of «bytes». The zip file is also password protected using the specified «password».

Conversion Functions (17)

  • BinToDec: Converts «binary_expression» to a decimal value.
  • BinToHex: Converts «binary_expression» to a hexadecimal value.
  • BinToOct: Converts «binary_expression» to a octal value.
  • BytesToHex: Converts each byte in «bytes» into a hexadecimal and returns the concatenated value.
  • BytesToString: Converts «bytes» to a string using the provided «code_page».
  • Char: Converts «decimal expression» to a char.
  • DecToBin: Converts «bytes» to a string using the provided «code_page».
  • DecToHex: Converts «bytes» to a string using the provided «code_page».
  • DecToOct: Converts «decimal_expression» to a octal value.
  • HexToBin: Converts «hex_expression» to a binary value.
  • HexToBytes: Converts each hexadecimal in «hex_expression» into a byte and returns a byte array containing all of the bytes.
  • HexToDec: Converts «hex_expression» to a decimal.
  • HexToOct: Converts «hex_expression» to a octal value.
  • OctToBin: Converts «octal_expression» to a binary value.
  • OctToDec: Converts «octal_expression» to a octal decimal.
  • OctToHex: Converts «octal_expression» to a hexadecimal value.
  • StringToBytes: Converts «character_expression» to bytes using the provided «code_page».

Cryptography Functions (33)

  • AdvancedDecryptUsingAES: Returns «bytes» decrypted using the AES algorithm.
  • AdvancedDecryptUsingDES: Returns «bytes» decrypted using the DES algorithm.
  • AdvancedDecryptUsingRC2: Returns «bytes» decrypted using the RC2 algorithm.
  • AdvancedDecryptUsingRijndael: Returns «bytes» decrypted using the Rijndael algorithm.
  • AdvancedDecryptUsingTripleDES: Returns «bytes» decrypted using the TripleDES algorithm.
  • AdvancedEncryptUsingAES: Returns «bytes» encrypted using the AES algorithm.
  • AdvancedEncryptUsingDES: Returns «bytes» encrypted using the DES algorithm.
  • AdvancedEncryptUsingRC2: Returns «bytes» encrypted using the RC2 algorithm.
  • AdvancedEncryptUsingRijndael: Returns «bytes» encrypted using the Rijndael algorithm.
  • AdvancedEncryptUsingDES: Returns «bytes» encrypted using the DES algorithm.
  • ComputeMD5Hash: Returns the MD5 hash of «bytes».
  • ComputeMD5HMAC: Returns the MD5 Hash-based message authentication code (HMAC) of «bytes» using «key».
  • ComputeRIPEMD160Hash: Returns the RIPEMD160 hash of «bytes».
  • ComputeRIPEMD160HMAC: Returns the RIPEMD160 Hash-based message authentication code (HMAC) of «bytes» using «key».
  • ComputeSHA1Hash: Returns the SHA1 hash of «bytes».
  • ComputeSHA1HMAC: Returns the SHA1 Hash-based message authentication code (HMAC) of «bytes» using «key».
  • ComputeSHA256Hash: Returns the SHA256 hash of «bytes».
  • ComputeSHA256HMAC: Returns the SHA256 Hash-based message authentication code (HMAC) of «bytes» using «key».
  • ComputeSHA384Hash: Returns the SHA384 hash of «bytes».
  • ComputeSHA384HMAC: Returns the SHA384 Hash-based message authentication code (HMAC) of «bytes» using «key».
  • ComputeSHA512Hash: Returns the SHA512 hash of «bytes».
  • ComputeSHA512HMAC: Returns the SHA512 Hash-based message authentication code (HMAC) of «bytes» using «key».
  • DecryptUsingAES: Returns «bytes» decrypted using the AES algorithm.
  • DecryptUsingDES: Returns «bytes» decrypted using the DES algorithm.
  • DecryptUsingRC2: Returns «bytes» decrypted using the RC2 algorithm.
  • DecryptUsingRijndael: Returns «bytes» decrypted using the Rijndael algorithm.
  • DecryptUsingTripleDES: Returns «bytes» encrypted using the TripleDES algorithm.
  • EncryptUsingAES: Returns «bytes» encrypted using the AES algorithm.
  • EncryptUsingDES: Returns «bytes» encrypted using the DES algorithm.
  • EncryptUsingRC2: Returns «bytes» encrypted using the RC2 algorithm.
  • EncryptUsingRijndael: Returns «bytes» encrypted using the Rijndael algorithm.
  • EncryptUsingTripleDES: Returns «bytes» encrypted using the TripleDES algorithm.
  • PBKDF2: Derives a byte array key (of length «key_size») from the specified password.

Date/Time Functions (11)

  • DateAdd: Returns «date» with «number» added to the «datepart».
  • DateDiff: Returns the number of date and time boundaries crossed between two specified dates.
  • DatePart: Returns part of «date» as specified by «datepart».
  • DateTimeToUnixTimeStamp: Returns the Unix time stamp equivalent of «date_time» as seconds or milliseconds specified by the «as_seconds_or_milliseconds» parameter. Unix time stamp is the number of seconds or milliseconds since Unix epoch (1970-01-01 00:00:00.000 UTC).
  • Day: Gets the day value from «date».
  • GetDate: Returns the current date of the system.
  • GetUtcDate: Returns the current date of the system in UTC.
  • Month: Gets the month value from «date».
  • TruncateTimeFromDateTime: Returns «date» with the time part removed.
  • UnixTimeStampToDateTime: Returns the DateTime equivalent of «time_stamp». Specify whether the Unix time stamp is in seconds or milliseconds with the «is_seconds_or_milliseconds» parameter. Unix time stamp is the number of seconds or milliseconds since Unix epoch (1970-01-01 00:00:00.000 UTC).
  • Year: Gets the year value from «date».

Encoding Functions (11)

  • DecodeBase64: Returns the Base64 encoded «character_expression», decoded into bytes.
  • DecodeBase64: Returns the Base64 encoded «character_expression», decoded into bytes then converted into string using the provided «code_page».
  • EncodeBase64: Returns «bytes» as a Base64 encoded string.
  • EncodeBase64: Returns «character_expression» as a Base64 encoded string using «code_page» to first get the bytes of «character_expression».
  • FormContentURLDecode: Returns the decoded value of the URL form encoded «character_expression».
  • FormContentURLEncode: Returns «character_expression» as a URL form encoded value.
  • HtmlDecode: Returns the HTML encoded «character_expression» as a decoded string.
  • HtmlEncode: Returns «character_expression» as a HTML encoded string.
  • HtmlEncode: Returns «character_expression» as a HTML encoded string based on the «use_entity_names» parameter.
  • UrlDecode: Returns the URL encoded «character_expression» as a decoded string.
  • UrlEncode: Returns «character_expression» as a URL encoded string.

File I/O Functions (13)

  • FileExists: Returns a boolean if a file exists at the specified «file_path».
  • FileLockedBy: Returns the process name that is currently locking the file specified at «file_path».
  • FolderExists: Returns a boolean if a folder exists at the specified «folder_path».
  • IsFileLocked: Returns a boolean if the file at the specified «file_path» is currently locked.
  • ReadBinaryContent: Returns all of the bytes in the binary file at the specified «file_path».
  • ReadLineCount: Returns the number of lines in the file at «file_path» with code page of «code_page».
  • ReadTextContent: Returns all of the text in the file at the specified «file_path».
  • ReadTextContent: Returns all of the text in the file at the specified «file_path» using the specified «code_page».
  • ReadTextContent: Returns the lines from «starting_line_number» to the end in the file at the specified «file_path» using the specified «code_page».
  • ReadTextContent: Returns the «number_of_lines» starting at «starting_line_number» in the file at the specified «file_path» using the specified «code_page». Speify 0 «number_of_lines» to read to end. Specify a negative «number_of_lines» to read to end excluding the last number of lines.
  • WriteBinaryContent: Writes «bytes» to the binary file at the specified «file_path» and returns true if successful.
  • WriteTextContent: Writes «text» to the file at the specified «file_path» using the specified «code_page» and returns true if successful.
  • WriteTextContent: Writes «text» to the file at the specified «file_path» and returns true if successful.

Mathematical Functions (25)

  • Abs: Returns the absolute value of «numeric_expression».
  • Acos: Returns the angle whose cosine is «numeric_expression».
  • Asin: Returns the angle whose sine is «numeric_expression».
  • Atan: Returns the angle whose tangent is «numeric_expression».
  • Avg: Returns the average value of all provided numeric expressions.
  • Ceiling: Returns the smallest integer that is greater than or equal to «numeric_expression».
  • Cos: Returns the cosine of «numeric_expression» in radians.
  • CosH: Returns the hyperbolic cosine of «numeric_expression» in radians.
  • Exp: Returns the exponential of «numeric_expression».
  • Floor: Returns the largest integer that is less than or equal to «numeric_expression».
  • Ln: Returns the largest integer that is less than or equal to «numeric_expression».
  • Log: Returns the base-10 logarithm of «numeric_expression».
  • Max: Returns the maximum value of all provided numeric expressions.
  • Min: Returns the minimum value of all provided numeric expressions.
  • Power: Returns the result of raising «numeric_expression» to «power».
  • Random: Returns a random number between the values of «min_value» and «max_value» (not including «max_value»).
  • Round: Returns the integer that is closest to «numeric_expression».
  • Sign: Returns the positive (+1), negative (-1), or zero (0) sign of «numeric_expression».
  • Sin: Returns the sine of «numeric_expression» in radians.
  • SinH: Returns the hyperbolic sine of «numeric_expression» in radians.
  • Sqrt: Returns the square root of «numeric_expression».
  • Square: Returns the square of «numeric_expression».
  • Sum: Returns the sum value of all provided numeric expressions.
  • Tan: Returns the tangent of «numeric_expression» in radians.
  • TanH: Returns the hyperbolic tangent of «numeric_expression» in radians.

Null Functions (31)

  • IsNULL: Returns a Boolean result based on whether «expression» is null.
  • NULL(DT_BOOL): Returns NULL.
  • NULL(DT_BYTES): Returns NULL.
  • NULL(DT_CY): Returns NULL.
  • NULL(DT_DATE): Returns NULL.
  • NULL(DT_DBDATE): Returns NULL.
  • NULL(DT_DBTIME): Returns NULL.
  • NULL(DT_DBTIME2): Returns NULL.
  • NULL(DT_DBTIMESTAMP): Returns NULL.
  • NULL(DT_DBTIMESTAMP2): Returns NULL.
  • NULL(DT_DBTIMESTAMPOFFSET): Returns NULL.
  • NULL(DT_DECIMAL): Returns NULL.
  • NULL(DT_FILETIME): Returns NULL.
  • NULL(DT_GUID): Returns NULL.
  • NULL(DT_I1): Returns NULL.
  • NULL(DT_I2): Returns NULL.
  • NULL(DT_I4): Returns NULL.
  • NULL(DT_I8): Returns NULL.
  • NULL(DT_IMAGE): Returns NULL.
  • NULL(DT_NTEXT): Returns NULL.
  • NULL(DT_NUMERIC): Returns NULL.
  • NULL(DT_R4): Returns NULL.
  • NULL(DT_R8): Returns NULL.
  • NULL(DT_STR): Returns NULL.
  • NULL(DT_TEXT): Returns NULL.
  • NULL(DT_UI1): Returns NULL.
  • NULL(DT_UI2): Returns NULL.
  • NULL(DT_UI4): Returns NULL.
  • NULL(DT_UI8): Returns NULL.
  • NULL(DT_WSTR): Returns NULL.
  • ReplaceNULL: Returns the value of «expression_2» if the value of «expression_1» evaluates to NULL.

Operators (22)

  • - (Negate)
  • - (Subtract)
  • ! (Logical NOT)
  • != (Unequal)
  • % (Modulo)
  • & (Bitwise AND)
  • && (Logical AND)
  • () (Parentheses)
  • * (Multiply)
  • / (Divide)
  • ? : (Conditional)
  • ^ (Bitwise Exclusive OR)
  • | (Bitwise Inclusive OR)
  • || (Logical OR)
  • ~ (Bitwise NOT)
  • + (Add)
  • + (Concatenate)
  • < (Less Than)
  • <= (Less Than or Equal To)
  • == (Equal)
  • > (Greater Than)
  • >= (Greater Than or Equal To)

Other Functions (15)

  • IfElse: Returns the «result» whose «condition» evaluates to true. If none of them evaluate to true then «default_result» is returned.
  • In:  Returns a Boolean is any of the <<Items>> are equal to  <<value>>.
  • IncrementalValue: Returns an incremental value starting at «starting_value» and incrementing by «incremental_value» for each row.
  • IncrementalValue(): Returns an incremental value starting at 1 and incrementing by 1 for each row.
  • NotIn: Returns a Boolean is none of the <<Items>> are equal to  <<value>>.
  • RowIndex: Returns the current row index.
  • RowIndex(): Returns the current row index.
  • ShowMessageBox: Pauses execution to show a message box with the specified «message». This function returns a dialog result based on the button clicked, which will resume execution. This function does nothing outside of design time processes.
  • ShowMessageBox: Pauses execution to show a message box with the specified «message» and «caption». This function returns a dialog result based on the button clicked, which will resume execution. This function does nothing outside of design time processes.
  • ShowMessageBox: Pauses execution to show a message box with the specified «message», «caption», and «buttons». This function returns a dialog result based on the button clicked, which will resume execution. This function does nothing outside of design time processes.
  • ShowMessageBox: Pauses execution to show a message box with the specified «message», «caption», «buttons», and «icon». This function returns a dialog result based on the button clicked, which will resume execution. This function does nothing outside of design time processes.
  • ShowMessageBox: Pauses execution to show a message box with the specified «message», «caption», «buttons», and «icon» with the «default_button» selected. This function returns a dialog result based on the button clicked, which will resume execution. This function does nothing outside of design time processes.
  • Sleep: Pauses execution for «milliseconds», then returns the value of «milliseconds».
  • SwitchCase: Returns the «result» whose «case» equals «value_to_compare». If no matches are found «default_result» is returned.
  • WriteValueToVariable: Sets the value of «variable» to the result of «value_expression».

Registry Functions (2)

  • RegistryGetValue: Gets the value of registry key at «key_name» with the name of «value_name».
  • RegistrySetValue: Sets the value of registry key at «key_name» with the name of «value_name» to the result of «value_expression» and the type to «value_type».

Error Handling (7)

  • FireError: Writes an error «message» to the SSIS log.
  • FireInformation: Writes an information «message» to the SSIS log.
  • FireWarning: Writes a warning «message» to the SSIS log.
  • RaiseError: Raises an error with the specified «message».
  • RaiseError: Raises an error with the specified «message» when the evaluated «condition» is true, otherwise returns false.
  • RaiseError: Raises an error with the specified «message» when the evaluated «condition» is true, otherwise returns «default_value».
  • Try: Attempts to return the evaluated «expression», if any errors occur then «default» is returned instead.

Regular Expression Functions (5)

  • RegexFindMatch: Returns the match at the specified position in «character_expression» using the «regular_expression». Returns NULL if the specified match position cannot be found.
  • RegexGetTokenAtPosition: Splits «character_expression» using the «regular_expression» pattern as the delimiter and returns the token at «position».
  • RegexGetTokenCount: Returns the number of tokens when «character_expression» is split by «regular_expression».
  • RegexIsMatch: Returns a Boolean based on if «character_expression» respects «regular_expression».
  • RegexReplace: Returns «character_expression» with all matching occurrences of «regular_expression» replaced with «replace_string».

String Functions (52)

  • Capitalize: Returns «character_expression» with the first character in every word converted to upper case and all other characters converted to lowercase.
  • CodePoint: Returns the Unicode code value of the leftmost character in «character_expression».
  • Contains: Returns a Boolean value representing if «character_expression» contains any occurrences of «search_expression».
  • DateTimeToString: Returns the «date_time» as a string with the format specified by «format_string».
  • EndsWith: Returns a Boolean value representing if «character_expression» ends with «search_expression».
  • EscapeRegex: Returns the «string_expression» with Regex characters escaped.
  • EscapeUri: Returns the «string_expression» with URI characters escaped.
  • EscapeUriDataString: Returns «character_expression» with any character that is not an unreserved character, including percent signs, escaped.
  • EscapeXml: Returns the «string_expression» with XML characters escaped.
  • FindString: Returns the location of the specified occurrence of a string in «character_expression».
  • FormatString: Inserts all of the values into «character_expression» by using curly brackets around an index to refer to a value to be inserted. For example: {0} will be replaced with the first value, {1} with the second, etc.
  • FormUrlEncodedToJson: Returns the form urlencoded «character_expression» as JSON.
  • GenerateJWT: Generates a JSON Web Token (JWT) using the «header_json», «payload_json», «secret».
  • GetItemAtAtPositionInStringArray: Parses «string_array» using «delimiter» and «text_qualifier» and returns the item at «position».
  • GetItemCountInStringArray: Parses «string_array» using «delimiter» and «text_qualifier» and returns number of items.
  • GetTokenAtPosition: Splits «character_expression» by «delimiter» and returns the token at «position».
  • GetTokenCount: Returns the number of tokens when «character_expression» is split by «delimiter».
  • Hex: Returns the hexadecimal value of an integer.
  • InStringArray: Parses «string_array» using «delimiter» and «text_qualifier» and returns a boolean if it contains «value».
  • JoinStringArray: Returns the list of «items» delimited by «delimiter» and qualified by «text_qualifier».
  • Left: Returns the part of «character_expression» that ends at and is to the left of «number» character position counting from the left.
  • Len: Returns the length of «character_expression».
  • Lower: Returns «character_expression» as all lower case characters.
  • LPad: Returns «character_expression» with added spaces on the left to make the length equal to «length».
  • LPad: Returns «character_expression» with «padding_value» added to the left until the length is as close to «length» as possible.
  • LTrim: Returns «character_expression» with no leading spaces.
  • LTrim: Returns «character_expression» with no leading occurrences of «trim_value».
  • NewGuid: Returns a new random GUID.
  • NullifyEmptyString: Returns null if «character_expression» is empty otherwise returns «character_expression».
  • NumberToString: Returns the «number» as a string with the format specified by «format_string».
  • RemoveDiacriticalMarks: Returns the «string_expression» without any diacritical marks (accents). ex: 'éléphant' will return 'elephant'.
  • Replace: Returns «character_expression» with all occurrences of «search_expression» replaced with «replace_expression».
  • Replicate: Returns «character_expression» replicated «times» number of times.
  • Reverse: Returns «character_expression» in reverse order.
  • Right: Returns the part of «character_expression» that ends at and is to the right of «number» character position counting from the right.
  • RPad: Returns «character_expression» with added spaces on the right to make the length equal to «length».
  • RPad: Returns «character_expression» with «padding_value» added to the right until the length is as close to «length» as possible.
  • RTrim: Returns «character_expression» with no trailing spaces.
  • RTrim: Returns «character_expression» with no trailing occurrences of «trim_value».
  • StartsWith: Returns a Boolean value representing if «character_expression» begins with «search_expression».
  • StrCount: Returns the number of occurrences of «search_expression» in «character_expression».
  • StripTagsFromHtml: Returns «source_html» without any HTML tags except for those specified in «except1, except2, except3...».
  • SubString: Returns a section of «character_expression» starting at position «start» and with a length of «length».
  • Token: Splits «character_expression» by «delimiter_expression» and returns the token at «occurance».
  • TokenCount: Returns the number of tokens when «character_expression» is split by «delimiter».
  • Trim: Returns a section of «character_expression» starting at position «start» and with a length of «length».
  • Trim: Returns «character_expression» with no leading or trailing occurrences of «trim_value».
  • UnescapeRegex: Returns the «string_expression» with Regex characters escaped.
  • UnescapeUri: Returns the «string_expression» with URI characters escaped.
  • UnescapeDataString: Returns «character_expression» with escaped URI characters, unescaped.
  • UnescapeXml: Returns the «string_expression» with XML characters escaped.
  • Upper: Returns «character_expression» as all upper case characters.

Type Casts (33)

  • (DT_BOOL): Attempts to cast the expression immediately to the right of the cast to a DT_BOOL.
  • (DT_BYTES): Attempts to cast the expression immediately to the right of the cast to a DT_BYTES.
  • (DT_CY): Attempts to cast the expression immediately to the right of the cast to a DT_CY.
  • (DT_DATE): Attempts to cast the expression immediately to the right of the cast to a DT_DATE.
  • (DT_DBDATE): Attempts to cast the expression immediately to the right of the cast to a DT_DBDATE.
  • (DT_DBTIME): Attempts to cast the expression immediately to the right of the cast to a DT_DBTIME.
  • (DT_DBTIME2): Attempts to cast the expression immediately to the right of the cast to a DT_DBTIME2.
  • (DT_DBTIMESTAMP): Attempts to cast the expression immediately to the right of the cast to a DT_DBTIMESTAMP.
  • (DT_DBTIMESTAMP2): Attempts to cast the expression immediately to the right of the cast to a DT_DBTIMESTAMP2.
  • (DT_DBTIMESTAMPOFFSET): Attempts to cast the expression immediately to the right of the cast to a DT_DBTIMESTAMPOFFSET.
  • (DT_DECIMAL): Attempts to cast the expression immediately to the right of the cast to a DT_DECIMAL.
  • (DT_FILETIME): Attempts to cast the expression immediately to the right of the cast to a DT_FILETIME.
  • (DT_GUID): Attempts to cast the expression immediately to the right of the cast to a DT_GUID.
  • (DT_I1): Attempts to cast the expression immediately to the right of the cast to a DT_I1.
  • (DT_I2): Attempts to cast the expression immediately to the right of the cast to a DT_I2.
  • (DT_I4): Attempts to cast the expression immediately to the right of the cast to a DT_I4.
  • (DT_I8): Attempts to cast the expression immediately to the right of the cast to a DT_I8.
  • (DT_IMAGE): Attempts to cast the expression immediately to the right of the cast to a DT_IMAGE.
  • (DT_NTEXT): Attempts to cast the expression immediately to the right of the cast to a DT_NTEXT.
  • (DT_NTEXT, «format_string»): Attempts to cast the expression immediately to the right of the cast to a DT_NTEXT using the «format_string» to format the result.
  • (DT_NUMERIC, «precision», «scale»): Attempts to cast the expression immediately to the right of the cast to a DT_NUMERIC.
  • (DT_R4): Attempts to cast the expression immediately to the right of the cast to a DT_R4.
  • (DT_R8): Attempts to cast the expression immediately to the right of the cast to a DT_R8.
  • (DT_STR): Attempts to cast the expression immediately to the right of the cast to a DT_STR.
  • (DT_STR): Attempts to cast the expression immediately to the right of the cast to a DT_STR using the «format_string» to format the result.
  • (DT_TEXT): Attempts to cast the expression immediately to the right of the cast to a DT_TEXT.
  • (DT_TEXT): Attempts to cast the expression immediately to the right of the cast to a DT_TEXT using the «format_string» to format the result.
  • (DT_UI1): Attempts to cast the expression immediately to the right of the cast to a DT_UI1.
  • (DT_UI2): Attempts to cast the expression immediately to the right of the cast to a DT_UI2.
  • (DT_UI4): Attempts to cast the expression immediately to the right of the cast to a DT_UI4.
  • (DT_UI8): Attempts to cast the expression immediately to the right of the cast to a DT_UI8.
  • (DT_WSTR): Attempts to cast the expression immediately to the right of the cast to a DT_WSTR.
  • (DT_WSTR): Attempts to cast the expression immediately to the right of the cast to a DT_WSTR using the «format_string» to format the result.

Value Lookup Functions (6)

  • GetValueFromJson: Returns the value in the source «json» at the location specified by «jpath».
  • GetValueFromNameValueCollection: Returns the first value with the name of «name» from the «name_value_collection» where the name-value pairs are delimited by the «outter_delimiter» and separated by the «inner_delimiter».
  • GetValueFromXml: Returns the value in the source «xml» at the location specified by «xpath».
  • NameExistsInNameValueCollection: Returns a boolean value if the name of «name» exists in the «name_value_collection» where the name value-pairs are delimited by the «outter_delimiter» and separated by the «inner_delimiter».
  • ValueExistsInJson: Returns a boolean specifying if there is a value in the «json» at the specified «jpath».
  • ValueExistsInXml: Returns a boolean specifying if there is a value in the «xml» at the specified «xpath».

XML Functions (3)

  • ReplaceInvalidXmlChars: Returns «character_expression» with all invalid XML characters replaced with «replace_string».
  • XslTransform: Returns the result of performing an XSL transformation on «source_xml» using the provided «xsl».
  • XslTransform: Returns the result of performing an XSL transformation on «source_xml» using the provided «xsl».

Italicized Text indicates the function is also included in the native Derived Column SSIS component.

Download the SIS Productivity Pack with the Premium Derived Column Now for Free

DownloadData Sheet