{"id":891,"date":"2015-06-09T10:19:42","date_gmt":"2015-06-09T14:19:42","guid":{"rendered":"http:\/\/binaryworld.net\/blogs\/?p=891"},"modified":"2015-12-01T16:37:34","modified_gmt":"2015-12-01T21:37:34","slug":"use-ssis-derived-column-transform","status":"publish","type":"post","link":"https:\/\/binaryworld.net\/blogs\/use-ssis-derived-column-transform\/","title":{"rendered":"How to use SSIS Derived Column Transform"},"content":{"rendered":"<div id=\"toc_container\" class=\"no_bullets\"><p class=\"toc_title\">Contents<\/p><ul class=\"toc_list\"><li><a href=\"#What_is_SSIS_Derived_Column_Transform\"><span class=\"toc_number toc_depth_1\">1<\/span> What is SSIS Derived Column Transform<\/a><\/li><li><a href=\"#Use_case_of_SSIS_Derived_Column_Transform\"><span class=\"toc_number toc_depth_1\">2<\/span> Use case of SSIS Derived Column Transform<\/a><\/li><li><a href=\"#Changing_Adding_column_using_SSIS_Derived_Column_Transform\"><span class=\"toc_number toc_depth_1\">3<\/span> Changing \/ Adding column using SSIS Derived Column Transform<\/a><\/li><li><a href=\"#Caution_about_using_SSIS_variable_in_Derived_Column_Transform\"><span class=\"toc_number toc_depth_1\">4<\/span> Caution about using SSIS variable in Derived Column Transform<\/a><\/li><li><a href=\"#Troubleshoot_SSIS_Derived_Column_Transform_errors\"><span class=\"toc_number toc_depth_1\">5<\/span> Troubleshoot SSIS Derived Column Transform errors<\/a><\/li><li><a href=\"#Example_Expressions_for_Derived_Column_Transform\"><span class=\"toc_number toc_depth_1\">6<\/span> Example Expressions for Derived Column Transform<\/a><ul><li><a href=\"#ssis_derived_column_transformation_if_then\"><span class=\"toc_number toc_depth_2\">6.1<\/span> ssis derived column transformation if then<\/a><\/li><li><a href=\"#ssis_derived_column_transformation_switch\"><span class=\"toc_number toc_depth_2\">6.2<\/span> ssis derived column transformation switch<\/a><\/li><li><a href=\"#ssis_derived_column_transformation_concatenate_string_see_how_to_type_cast_int_to_string\"><span class=\"toc_number toc_depth_2\">6.3<\/span> ssis derived column transformation concatenate string (see how to type cast int to string)<\/a><\/li><\/ul><\/li><\/ul><\/div>\n<h2><span id=\"What_is_SSIS_Derived_Column_Transform\">What is SSIS Derived Column Transform<\/span><\/h2>\n<p><strong>SSIS Derived column transform<\/strong> is used to change existing columns coming from Source or add New Columns in the Pipeline Buffer.<br \/>\n<strong>Note:<\/strong> For simplicity we have used <a href=\"http:\/\/zappysys.com\/onlinehelp\/ssis-powerpack\/scr\/dummydata-source.htm\" target=\"_blank\">Free DummyData Generator Source<\/a> in this post rather than real Source.. Please download it from <a href=\"http:\/\/zappysys.com\/ssis-powerpack\/download\/\" target=\"_blank\">this link<\/a>\n<\/p>\n<h2><span id=\"Use_case_of_SSIS_Derived_Column_Transform\">Use case of SSIS Derived Column Transform<\/span><\/h2>\n<ol>\n<li>Changing data to Upper case or Trimming white spaces from values coming from Flat File<\/li>\n<li>Combining First name and Last name to create New Display name column<\/li>\n<li>Detecting NULL and Replace with BLANK<\/li>\n<li>Format Date Time  <\/li>\n<\/ol>\n<h2><span id=\"Changing_Adding_column_using_SSIS_Derived_Column_Transform\">Changing \/ Adding column using SSIS Derived Column Transform<\/span><\/h2>\n<p> Many time you need to change upstream column value inside pipeline somewhere in between source and destination. In that case you will most likely use either <a title=\"SSIS Derived Column Transform\" href=\"https:\/\/technet.microsoft.com\/en-us\/library\/ms141069(v=sql.105).aspx\" target=\"_blank\">SSIS Derived Column Transform<\/a> or SSIS Script Transform. For simple expressions use SSIS Derived Column Transform because its very fast compared to Script Transform.<\/p>\n<p>Remember few things while using <strong>SSIS Derived Column Transform<\/strong><\/p>\n<ol>\n<li>Column Names are Case sensitive<\/li>\n<li>You can only have single line Expressions<\/li>\n<li>Expressions are hard to debug so avoid very complex expressions (use script transform in that case)<\/li>\n<\/ol>\n<p>Here is sample how to TRIM and do UPPER case of existing Source column<\/p>\n<figure style=\"width: 590px\" class=\"wp-caption alignnone\"><img loading=\"lazy\" decoding=\"async\" title=\"SSIS Derived Column Transform Expression\" alt=\"SSIS Derived Column Transform Expression\" src=\"http:\/\/binaryworld.net\/blogs\/images\/ssis\/ssis-derived-column-transform-expression.png\" width=\"590\" height=\"434\" \/><figcaption class=\"wp-caption-text\">SSIS Derived Column Transform Expression<\/figcaption><\/figure>\n<h2><span id=\"Caution_about_using_SSIS_variable_in_Derived_Column_Transform\">Caution about using SSIS variable in Derived Column Transform<\/span><\/h2>\n<p>Some time you may have to use SSIS Variables in Derived Column Transform. Values of SSIS Variables are cached in PreExecute phase of Transform so its read only once when transform receive first row and then cached.<\/p>\n<h2><span id=\"Troubleshoot_SSIS_Derived_Column_Transform_errors\">Troubleshoot SSIS Derived Column Transform errors<\/span><\/h2>\n<p>The most annoying part in <strong>SSIS Derived Column Transform<\/strong> is troubleshooting. Here are some tips so you don&#8217;t pull your hair.<\/p>\n<ol>\n<li>If error is at design time then most likely you will get validation error. You can read it by doing mouse over expressions flagged as red color.<\/li>\n<li>If error is at runtime then you have to find out which raw causing issue.\n<ol>\n<li>Drag error output (red arrow coming from transform) of SSIS derived Column Transform and attach to some Flat File destination to know which rows are failing<\/li>\n<li>Use data viewer  <\/li>\n<\/ol>\n<\/li>\n<li>Reduce rows coming from source and narrow down troubleshooting<\/li>\n<\/ol>\n<h2><span id=\"Example_Expressions_for_Derived_Column_Transform\">Example Expressions for Derived Column Transform<\/span><\/h2>\n<h3><span id=\"ssis_derived_column_transformation_if_then\">ssis derived column transformation if then<\/span><\/h3>\n<pre >[CustomerFlag]==1 ? &quot;ACTIVE&quot; : &quot;IN ACTIVE&quot;<\/pre>\n<h3><span id=\"ssis_derived_column_transformation_switch\">ssis derived column transformation switch<\/span><\/h3>\n<pre>[State]==&quot;GA&quot; ? &quot;GEORGIA&quot; : &quot; [State]==&quot;NY&quot; ? &quot;NEWYORK&quot; : [State]==&quot;AL&quot; ? &quot;ALABAMA&quot; : &quot;Other&quot;<\/pre>\n<h3><span id=\"ssis_derived_column_transformation_concatenate_string_see_how_to_type_cast_int_to_string\">ssis derived column transformation concatenate string (see how to type cast int to string)<\/span><\/h3>\n<pre>[Col1] + [Col2] + &quot;some static value&quot;  + (DT_WSTR)55<\/pre>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Contents1 What is SSIS Derived Column Transform2 Use case of SSIS Derived Column Transform3 Changing \/ Adding column using SSIS Derived Column Transform4 Caution about using SSIS variable in Derived Column Transform5 Troubleshoot SSIS Derived Column Transform errors6 Example Expressions &hellip;<\/p>\n<p class=\"read-more\"> <a class=\"more-link\" href=\"https:\/\/binaryworld.net\/blogs\/use-ssis-derived-column-transform\/\"> <span class=\"screen-reader-text\">How to use SSIS Derived Column Transform<\/span> Read More &raquo;<\/a><\/p>\n","protected":false},"author":1,"featured_media":895,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[29],"tags":[140,139,32],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v22.3 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\r\n<title>How to use SSIS Derived Column Transform - BinaryWorld Blog<\/title>\r\n<meta name=\"description\" content=\"Learn how to use SSIS Derived Column Transform. How to use Expressions in SSIS, Use of Replace Column option, Add New Column, and Use of SSIS variable.\" \/>\r\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\r\n<link rel=\"canonical\" href=\"https:\/\/binaryworld.net\/blogs\/use-ssis-derived-column-transform\/\" \/>\r\n<meta property=\"og:locale\" content=\"en_US\" \/>\r\n<meta property=\"og:type\" content=\"article\" \/>\r\n<meta property=\"og:title\" content=\"How to use SSIS Derived Column Transform - BinaryWorld Blog\" \/>\r\n<meta property=\"og:description\" content=\"Learn how to use SSIS Derived Column Transform. How to use Expressions in SSIS, Use of Replace Column option, Add New Column, and Use of SSIS variable.\" \/>\r\n<meta property=\"og:url\" content=\"https:\/\/binaryworld.net\/blogs\/use-ssis-derived-column-transform\/\" \/>\r\n<meta property=\"og:site_name\" content=\"BinaryWorld Blog\" \/>\r\n<meta property=\"article:published_time\" content=\"2015-06-09T14:19:42+00:00\" \/>\r\n<meta property=\"article:modified_time\" content=\"2015-12-01T21:37:34+00:00\" \/>\r\n<meta property=\"og:image\" content=\"https:\/\/binaryworld.net\/blogs\/wp-content\/uploads\/2015\/06\/ssis-derived-column-transform-expression.png\" \/>\r\n\t<meta property=\"og:image:width\" content=\"590\" \/>\r\n\t<meta property=\"og:image:height\" content=\"434\" \/>\r\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\r\n<meta name=\"author\" content=\"Binary World\" \/>\r\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\r\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Binary World\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\r\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/binaryworld.net\/blogs\/use-ssis-derived-column-transform\/\",\"url\":\"https:\/\/binaryworld.net\/blogs\/use-ssis-derived-column-transform\/\",\"name\":\"How to use SSIS Derived Column Transform - BinaryWorld Blog\",\"isPartOf\":{\"@id\":\"https:\/\/binaryworld.net\/blogs\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/binaryworld.net\/blogs\/use-ssis-derived-column-transform\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/binaryworld.net\/blogs\/use-ssis-derived-column-transform\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/binaryworld.net\/blogs\/wp-content\/uploads\/2015\/06\/ssis-derived-column-transform-expression.png\",\"datePublished\":\"2015-06-09T14:19:42+00:00\",\"dateModified\":\"2015-12-01T21:37:34+00:00\",\"author\":{\"@id\":\"https:\/\/binaryworld.net\/blogs\/#\/schema\/person\/77cf0a9a512dd22bff93c6a1b6374fe0\"},\"description\":\"Learn how to use SSIS Derived Column Transform. How to use Expressions in SSIS, Use of Replace Column option, Add New Column, and Use of SSIS variable.\",\"breadcrumb\":{\"@id\":\"https:\/\/binaryworld.net\/blogs\/use-ssis-derived-column-transform\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/binaryworld.net\/blogs\/use-ssis-derived-column-transform\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/binaryworld.net\/blogs\/use-ssis-derived-column-transform\/#primaryimage\",\"url\":\"https:\/\/binaryworld.net\/blogs\/wp-content\/uploads\/2015\/06\/ssis-derived-column-transform-expression.png\",\"contentUrl\":\"https:\/\/binaryworld.net\/blogs\/wp-content\/uploads\/2015\/06\/ssis-derived-column-transform-expression.png\",\"width\":590,\"height\":434},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/binaryworld.net\/blogs\/use-ssis-derived-column-transform\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/binaryworld.net\/blogs\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"How to use SSIS Derived Column Transform\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/binaryworld.net\/blogs\/#website\",\"url\":\"https:\/\/binaryworld.net\/blogs\/\",\"name\":\"BinaryWorld Blog\",\"description\":\"Tips and Tutorials for Microsoft SQL Server, SSIS, SSAS, Business Intelligence, C#, .net\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/binaryworld.net\/blogs\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/binaryworld.net\/blogs\/#\/schema\/person\/77cf0a9a512dd22bff93c6a1b6374fe0\",\"name\":\"Binary World\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/binaryworld.net\/blogs\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/eaea47799daa577835eb53e64dfd3e13?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/eaea47799daa577835eb53e64dfd3e13?s=96&d=mm&r=g\",\"caption\":\"Binary World\"},\"description\":\"Binary World is a Software Development company located in Atlanta, USA (since 2007). Binary World specialized in Business Intelligence, mobile, cloud computing and .Net Application Development.\",\"url\":\"https:\/\/binaryworld.net\/blogs\/author\/admin\/\"}]}<\/script>\r\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"How to use SSIS Derived Column Transform - BinaryWorld Blog","description":"Learn how to use SSIS Derived Column Transform. How to use Expressions in SSIS, Use of Replace Column option, Add New Column, and Use of SSIS variable.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/binaryworld.net\/blogs\/use-ssis-derived-column-transform\/","og_locale":"en_US","og_type":"article","og_title":"How to use SSIS Derived Column Transform - BinaryWorld Blog","og_description":"Learn how to use SSIS Derived Column Transform. How to use Expressions in SSIS, Use of Replace Column option, Add New Column, and Use of SSIS variable.","og_url":"https:\/\/binaryworld.net\/blogs\/use-ssis-derived-column-transform\/","og_site_name":"BinaryWorld Blog","article_published_time":"2015-06-09T14:19:42+00:00","article_modified_time":"2015-12-01T21:37:34+00:00","og_image":[{"width":590,"height":434,"url":"https:\/\/binaryworld.net\/blogs\/wp-content\/uploads\/2015\/06\/ssis-derived-column-transform-expression.png","type":"image\/png"}],"author":"Binary World","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Binary World","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/binaryworld.net\/blogs\/use-ssis-derived-column-transform\/","url":"https:\/\/binaryworld.net\/blogs\/use-ssis-derived-column-transform\/","name":"How to use SSIS Derived Column Transform - BinaryWorld Blog","isPartOf":{"@id":"https:\/\/binaryworld.net\/blogs\/#website"},"primaryImageOfPage":{"@id":"https:\/\/binaryworld.net\/blogs\/use-ssis-derived-column-transform\/#primaryimage"},"image":{"@id":"https:\/\/binaryworld.net\/blogs\/use-ssis-derived-column-transform\/#primaryimage"},"thumbnailUrl":"https:\/\/binaryworld.net\/blogs\/wp-content\/uploads\/2015\/06\/ssis-derived-column-transform-expression.png","datePublished":"2015-06-09T14:19:42+00:00","dateModified":"2015-12-01T21:37:34+00:00","author":{"@id":"https:\/\/binaryworld.net\/blogs\/#\/schema\/person\/77cf0a9a512dd22bff93c6a1b6374fe0"},"description":"Learn how to use SSIS Derived Column Transform. How to use Expressions in SSIS, Use of Replace Column option, Add New Column, and Use of SSIS variable.","breadcrumb":{"@id":"https:\/\/binaryworld.net\/blogs\/use-ssis-derived-column-transform\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/binaryworld.net\/blogs\/use-ssis-derived-column-transform\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/binaryworld.net\/blogs\/use-ssis-derived-column-transform\/#primaryimage","url":"https:\/\/binaryworld.net\/blogs\/wp-content\/uploads\/2015\/06\/ssis-derived-column-transform-expression.png","contentUrl":"https:\/\/binaryworld.net\/blogs\/wp-content\/uploads\/2015\/06\/ssis-derived-column-transform-expression.png","width":590,"height":434},{"@type":"BreadcrumbList","@id":"https:\/\/binaryworld.net\/blogs\/use-ssis-derived-column-transform\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/binaryworld.net\/blogs\/"},{"@type":"ListItem","position":2,"name":"How to use SSIS Derived Column Transform"}]},{"@type":"WebSite","@id":"https:\/\/binaryworld.net\/blogs\/#website","url":"https:\/\/binaryworld.net\/blogs\/","name":"BinaryWorld Blog","description":"Tips and Tutorials for Microsoft SQL Server, SSIS, SSAS, Business Intelligence, C#, .net","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/binaryworld.net\/blogs\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/binaryworld.net\/blogs\/#\/schema\/person\/77cf0a9a512dd22bff93c6a1b6374fe0","name":"Binary World","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/binaryworld.net\/blogs\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/eaea47799daa577835eb53e64dfd3e13?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/eaea47799daa577835eb53e64dfd3e13?s=96&d=mm&r=g","caption":"Binary World"},"description":"Binary World is a Software Development company located in Atlanta, USA (since 2007). Binary World specialized in Business Intelligence, mobile, cloud computing and .Net Application Development.","url":"https:\/\/binaryworld.net\/blogs\/author\/admin\/"}]}},"_links":{"self":[{"href":"https:\/\/binaryworld.net\/blogs\/wp-json\/wp\/v2\/posts\/891"}],"collection":[{"href":"https:\/\/binaryworld.net\/blogs\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/binaryworld.net\/blogs\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/binaryworld.net\/blogs\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/binaryworld.net\/blogs\/wp-json\/wp\/v2\/comments?post=891"}],"version-history":[{"count":0,"href":"https:\/\/binaryworld.net\/blogs\/wp-json\/wp\/v2\/posts\/891\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/binaryworld.net\/blogs\/wp-json\/wp\/v2\/media\/895"}],"wp:attachment":[{"href":"https:\/\/binaryworld.net\/blogs\/wp-json\/wp\/v2\/media?parent=891"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/binaryworld.net\/blogs\/wp-json\/wp\/v2\/categories?post=891"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/binaryworld.net\/blogs\/wp-json\/wp\/v2\/tags?post=891"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}