The Premium Derived Column component offers a familiar UI to that of the built-in Derived Column component. Like the built-in component, the user will add derived columns and utilize expressions to transform data.

With Premium Derived Column, developers have all the functionality of the out-of-box component plus over 225 additional functions, an advanced expression editor, advanced data type detection and the ability to validate and test expressions with test values.

Below is a list of function categories included within the Premium Derived Column, part of the SSIS Productivity Pack. Click on each category to see the list of available functions with a brief description of what it achieves.

SSIS Premium Derived Column

  • 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».
  • 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».
  • 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».
  • ComputeMD5Hash: Returns the MD5 hash of «bytes».
  • ComputeMD5HMCA: Returns the MD5 Hash-based message authentication code (HMAC) of «bytes» using «key».
  • ComputeRIPEMD160Hash: Returns the RIPEMD160 hash of «bytes».
  • ComputeRIPEMD160HMCA: Returns the RIPEMD160 Hash-based message authentication code (HMAC) of «bytes» using «key».
  • ComputeSHA1Hash: Returns the SHA1 hash of «bytes».
  • ComputeSHA1HMCA: Returns the SHA1 Hash-based message authentication code (HMAC) of «bytes» using «key».
  • ComputeSHA256Hash: Returns the SHA256 hash of «bytes».
  • ComputeSHA256HMCA: Returns the SHA256 Hash-based message authentication code (HMAC) of «bytes» using «key».
  • ComputeSHA384Hash: Returns the SHA384 hash of «bytes».
  • ComputeSHA384HMCA: Returns the SHA384 Hash-based message authentication code (HMAC) of «bytes» using «key».
  • ComputeSHA512Hash: Returns the SHA512 hash of «bytes».
  • ComputeSHA512HMCA: Returns the SHA512 Hash-based message authentication code (HMAC) of «bytes» using «key».
  • DecryptUsingAesCbc: Returns «bytes» decrypted using the AES-CBC algorithm and the provided «password».
  • DecryptUsingAesXts: Returns «bytes» decrypted using the AES-XTS algorithm and the provided «password».
  • DecryptUsingTripleDesCbc: Returns «bytes» decrypted using the 3DES-CBC algorithm and the provided «password».
  • DecryptUsingTwofishCbc: Returns «bytes» decrypted using the Twofish-CBC algorithm and the provided «password».
  • EncryptUsingAesCbc: Returns «bytes» encrypted using the AES-CBC algorithm and password set to «password».
  • EncryptUsingAesXts: Returns «bytes» encrypted using the AES-XTS algorithm and password set to «password».
  • EncryptUsingTripleDesCbc: Returns «bytes» encrypted using the 3DES-CBC algorithm and password set to «password».
  • EncryptUsingTwofishCbc: Returns «bytes» encrypted using the Twofish-CBC algorithm and password set to «password».
  • 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».
  • 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».
  • HtmlDecode: Returns the HTML encoded «character_expression» as a decoded string.
  • HtmlEncode: Returns «character_expression» as a HTML encoded string.
  • UrlDecode: Returns the URL encoded «character_expression» as a decoded string.
  • UrlEncode: Returns «character_expression» as a URL encoded string.
  • 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.
  • 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.
  • 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.
  • - (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)
  • IfElse: Returns the «result» whose «condition» evaluates to true. If none of them evaluate to true then «default_result» is returned.
  • RowIndex: Returns the current row index.
  • RowIndex(): Returns the current row index.
  • 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».
  • 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».
  • 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».
  • Capitalize: Returns «character_expression» with the first character in every word converted to upper case and all other characters converted to lower case.
  • 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.
  • 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.
  • 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.
  • 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».
  • 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.
  • UnescapeXml: Returns the «string_expression» with XML characters escaped.
  • Upper: Returns «character_expression» as all upper case characters.
  • (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_NUMERIC): 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_TEXT): Attempts to cast the expression immediately to the right of the cast to a DT_TEXT.
  • (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.
  • 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 seperated 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 seperated 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».
  • ReplaceInvalidXmlChars: Returns «character_expression» with all invalid XML characters replaced with «replace_string».
  • XslTransform: Returns the result of performing an XSL tranformation on «source_xml» using the provided «xsl».
  • XslTransform: Returns the result of performing an XSL tranformation on «source_xml» using the provided «xsl».

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

Premium Derived Column component is available in SSIS Productivity Pack

Premium Derived Column: data integration made easy

Our solution is easy to use, offering code-less integration between the Premium Derived Column component and virtually any other application or database system. Our solution is built utilizing Microsoft SQL Server Integration Services (SSIS), a performant and flexible ETL platform, allowing your team to take advantage of the technologies and skills they already have. The drag-and-drop user interface makes it easy to set up powerful integration within a matter of minutes.

Our Premium Derived Column component is available within the Premium Data Flow components of our SSIS Productivity Pack, a large collection of premium and unique ETL tools to enable greater development productivity. We also offer several additional connectivity solutions to easily integrate Premium Derived Column with applications such as Microsoft Dynamics 365, SharePoint, Salesforce, and many more.

See our full product offerings for more information.


Other Premium Data Flow Components

Our Premium Data Flow components make it easy to integrate with different services within the ETL process. Each component available has been specifically developed to expand and enhance data migration and integration processes. In addition to Premium Derived Column, the Premium Data Flow components include: