THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Andy Leonard

Andy Leonard is a Data Philosopher at Enterprise Data & Analytics, an SSIS Trainer, Consultant, developer of the Data Integration Lifecycle Management (DILM) Suite, a Business Intelligence Markup Language (Biml) developer and BimlHero; SQL Server database and data warehouse developer, community mentor, engineer, and farmer. He is a co-author of SQL Server Integration Services Design Patterns, and the Stairway to Integration Services.

SSIS Expression Language and the Derived Column Transformation


This post is part of a series titled An Introduction to the SSIS Expression Language.

In this post, I demonstrate the SSIS Expression Language and the Derived Column Transformation.

To build the demo project described in this article you will need SSIS 2005 and the AdventureWorks sample database (which can be downloaded at

Add To The Demo Project

If you have not already done so, create an SSIS project named ExpressionLanguageDemo.

Once the project is created, open Solution Explorer and rename Package.dtsx - change the name to DerivedColumn.dtsx. If you've already created the project, right-click the SSIS Packages folder in Solution Explorer and click New Package - and rename that package to DerivedColumn.dtsx. When prompted, rename the package object as well. (I think you should always do this - I always answer this message box by clicking the Yes button.)

Drag a Data Flow Task onto the Control Flow. Click the Data Flow tab to begin editing, and drag an OLE DB Source onto the canvas. Double-click the OLE DB Source to open the editor. As in the post entitled SSIS Expression Language and Variables, click the New button to create a new Connection Manager to the AdventureWorks database.

Set the Data Access Mode to SQL Command and enter the following T-SQL statement into the SQL Command Text textbox:

From Person.Contact

Click OK to close the editor.

Learning To Derive

Drag a Derived Column Transformation onto the Data Flow canvas and connect the OLE DB Source Adapter to it using a Data Flow Task:


Double-click the Derived Column transformation to open the editor.

Let's start somewhat in the middle. The Derived Column column of the editor contains a list of all the columns flowing into the transformation, plus an entry for "<add as new column>": 


If you select one of the columns flowing into the transformation, you can use an Expression to modify the value in some way. Why would you do this? Let's look at an example.

The Replacement

From the Derived Column dropdown, select "Replace 'MiddleName'":

Note a few properties are automatically populated when you make the selection. The Derived Column Name is set to "MiddleName." This makes sense - your selection indicates you want to replace the current values in the MiddleName column. The Data Type column contains the equivalent SSIS data type for the MiddleName field in the Person.Contact table.

Data Type coercion in SSIS is interesting - there are quite a few moving parts. The MiddleName field is an NVARCHAR(50) data type in the Person.Contact table. The connection between SSIS and the AdventureWorks database is made using an OLE DB Connection Manager. There is some data type corecion in the database - OLE DB provider - SSIS connection layers. By the time the data type makes it into the SSIS data flow pipeline, it is represented as a DT_WSTR, or Unicode string. The Length column is also populated with 50.

The Expression column is a blank slate upon which you write the code that controls value. You can hard code a value, such as a space, by enclosing a space inside double-quotes:

" "

You can hard code a Null value by using a NULL function. Expand the NULL Functions folder in the upper right of the Derived Column transformation and drag the Null function for the DT_WSTR data type into the Expression textbox, replacing the <<length>> placeholder with the length of the column (50):


Or you can conditionally replace the MiddleName value. In this case, we'll replace Null middle names with a space. If not Null, we'll allow the existing value to continue in the data flow pipeline. To do this, use the ternary syntax:

[Test Condition] ? [True Operation] : [False Operation]

To replace Null MiddleName values with a space, use the following expression:

IsNull(MiddleName) ? " " : MiddleName

Add One

In the second row, let's add a new column. In the Derived Column dropdown, select "<add as new column>". Note the only column auto-populated is the Derived Column Name column, and it contains "Derived Column 1". Rename this column "FullName." Add the following expression:

FirstName + (IsNull(MiddleName) ? " " : MiddleName) + LastName

Note the Data Type DT_WSTR for both columns. Suppose we want to change the data type? We can cast the data type for added columns. Expand the Type Casts function folder and drag a DT_STR cast to the beginning of the Expressions textbox - replacing the <<length>> placeholder with 150 (the summed lengths of the columns) and the <<code_page>> placeholder with 1252 for en-us (for US English, feel free to substitute with your code page if different) - to convert the value from Unicode to an ASCII string. The FullName expression should now read:

(DT_STR, 150, 1252)(FirstName + (IsNull(MiddleName) ? " " : MiddleName) + LastName)

Note the Data Type column is now a String:

You can add a DT_STR Cast statement to the expression for the MiddleName, but it doesn't change the Data Type. Why can't we change the data type for existing columns in the Derived Column transformation? We're replacing the values, not changing the data type. Is it impossible to change the data type of an existing column in the Derived Column? Let's put it this way: It is not possible to convert the data type of a column when you are merely replacing the value. You can, however, accomplish the same goal by creating a new column in the Data Flow.

To demonstrate, configure the third row to add a new column. Name the new column StrMiddleName and set the expression to:

(DT_STR, 150, 1252)MiddleName

Other options: You can also use a Data Conversion Transformation to change the data type of a column in the data flow pipeline, but the Data Conversion also adds a column to the data flow pipeline. You can also cast the value in T-SQL from the source (in the OLE DB Source Adapter).


The Derived Column Transformation is very flexible and powerful. The mathematical and string parsing functions are more easily understood by folks with a developer background. The ETL-specific functions - such as those demonstrated in this post: NULL fields, type-casting, and ternary conditional operations - are not as commonly understood.

:{> Andy

Published Wednesday, February 04, 2009 12:03 AM by andyleonard

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



Jyothi said:

Great Example. Thanks

April 3, 2010 2:10 PM

Smith said:

thank's a lot

April 19, 2010 2:13 AM

Vasant Jagtap said:


I think for more readable output you can use expression for FullName like:-

(DT_STR,150,1252)(FirstName + (ISNULL(MiddleName) ? " " : " " + MiddleName) + " " + LastName)


Vasant G Jagtap

April 21, 2010 3:46 AM

minh said:

I have a comment to make to vent my frustration wit this product.  Why did the MS team not use the same syntax as say "TSQL"?  

April 26, 2010 12:50 PM

Bill K. said:

Great article, very good examples, clear step by step guidance.  One of the best written technical guide I have seen and I have seen alot, thanks...

June 24, 2010 1:00 PM

Nitin Joshi said:

Good One.

July 21, 2010 7:09 AM

yakub said:

its nice and easily understandable...


July 21, 2010 8:02 AM

Jes said:

hola y gracias por el aporte...Tengo una duda...cómo logro con una columna derivada usar el condicional:                             cd_gpo_sang == "GRUPOA" ? "GRUPO A" : "GRUPO B"

El asunto es que son 3 las condiciones: Si es GRUPOA, convertirlo a GRUPO A. Si es GRUPOB, convertirlo a GRUPO B. Finalmente, si es GRUPOAB, convertirlo a GRUPO AB. ¿Cómo podría hacerlo?

August 4, 2010 11:12 PM

HANU said:

it's ok. But have to explaim more i thnk....

August 5, 2010 5:17 AM

Carl said:

I tend to agree with minh above it is much easier to simply:

select *,

 FirstName + IsNull(MiddleName,'') + LastName as FullName

from table

Hopefully in 2008 and beyond the expression language will be at least as elegant as T-SQL - there is too much potential here to be wasted.

September 2, 2010 6:18 PM

KT said:

Yes, very glad I found this while starting out with some messy flat files.  Also found in another post how to convert an Empty String into a null to insert into a numeric destination field.  -[YourColumnA]=="" ? NULL(DT_I8) : (DT_I8)[YourColumnA]

It solves  -1071607689 ERROR CODE

-- Column type mis-match trying to insert an empty string into a numeric field.

Thanks so much for getting me going.  Was very stuck to begin with.

October 7, 2010 10:39 PM

Xain said:

This was may I say - AWESOME

October 19, 2010 9:49 AM

hassan said:


I have a table A with 4 columns. Now I need to have a 5th column added to this table, but the values in this 5th column should be coming from a different source table B..How to do that???

November 1, 2010 4:47 AM

andyleonard said:

Hi Hassan,

  You have a few options:

1. In the OLE DB (or other) Source, write a SQL statement that includes a JOIN to Table B;

2. Add another Source adapter aimed at Table B, then use a Merge Join transformation to add the desired column to your Data Flow;

3. Add a Lookup transformation aimed at Table B and select the desired Table B column on the Columns page.

Hope this helps,


November 1, 2010 8:29 AM

hassan said:

thank you very much sir,i`m glad to see your response.

i have some problems i would like to discuss,as i`m new to SSIS and have converted text files,acess,excel,oracle data to SQL.

Now before ETL i have to clean data and load it to a single database,on which i can perform dimensional modeling(cubes/facts)

I have four DBases with many attributes .  I'd like to use SSIS to read the source DBs, clean the data, and insert into the relational DB.  

first part of cleansing requires

Gender column has 0/1 in lahore DB

Gender column has 1/0 in karachi DB

Gender column has m/f in islamabad DB

Gender column has null  in peshawar DB.

I want SSIS to read all Dbases one by one then convert Gender column values to "Male And Female" and send erronous data to "error/exception table"

Second thing is i have Dtae Of Birth column in which erronous dates exists like 31-feb-90,22-apl-87 ,21/03/76 etc.Similarly I want SSIS to read Date of birth column from each database seperately , check erronous/dirty data if exists,send dirty data to a new "exception/error table". and send data after modification to original tables.

i even don`t know what control flow tasks i have to use to perform these functions,so plzz elaborate each step.

PLZ i need help ASAP plzzzzzzzzzzzz

November 2, 2010 6:46 AM

hassan said:

i`ve to convert gender colum values "0 or 1" to

0 to "Male" && 1 to "Female" .I used "Replace Gender" and I tried expressions

1)Gender== "0" ? "MALE" : Gender == "1" ? "FEMALE" : "NA"

2)(DT_I4)Gender==0 ? "MALE" : (DT_I4)Gender == 1 ? "FEMALE" : "NA"

3)(DT_STR,150,1252)Gender == "0" ? "MALE" : Gender

Data type=Unicode String[DT_WSTR] & Lenght= 1

but it gives error.plz help asap.Where i`m doing wrong???

November 10, 2010 2:57 PM

andyleonard said:

Hi Hassan,

  I think I'd write this expression as:

(Gender == 1) ? (DT_WSTR, 6)"FEMALE" : (DT_WSTR,6)"MALE"

  This presupposes gender is represented by two and only two states in your data, and I've seen more options in Gender columns.

Hope this helps,


November 10, 2010 3:58 PM

hassan said:

source data type was unicode[DT_WSTR ] length was 255.

so this query worked...

[M/F] == (DT_STR,50,1252)"M" ? "MALE" : [M/F] == (DT_STR,50,1252)"F" ? "FEMALE" : "NA"

but for

source data type is unicode[DT_WSTR ] length is 1.

i tried a new Column in Derived Column

Gender == "1" ? "MALE" : Gender == "0" ? "FEMALE" : "NA"

Gender == (DT_STR,6,1252)"1" ? "MALE" : Gender == (DT_STR,6,1252)"0" ? "FEMALE" : "NA"

but it gave NULL values

November 23, 2010 6:54 AM

andyleonard said:

Hi Hassan,

  Send me an email using the Email link in the upper right corner of this page. I will reply and you can send me the SSIS package. I will take a look.


November 23, 2010 7:57 AM

Dframe said:

I have an SSIS package that is querying Active Directory. Two of the fields come across fine. The rest show up as System.Object[] when writtem to a SQL Server table. If I put a Data Viewer in stream they show up as <Long Text>. The original data type is unicode text stream [DT_NTEXT]. I'm trying to run it through 2 Derived Column transformations. First to cast to [DT_WSTR] then to [DT_STR]. Nothing seems to work. Any help would be appreciated.

January 21, 2011 9:39 AM

Nigel Ainscoe said:

Nice one Andy. I was getting right in my face with trying to change the codepage on a column. Doh add a new column :-)

February 7, 2011 9:10 AM

wayne said:

ok so can you do an if statement that checks the value to start with a 00 and if it does not then add a 00 to the value?

February 9, 2011 6:55 PM

Irina said:


I would like to upload data from a couple Access databases into one SQL Server table. I found this article: Dynamically build connection objects for MS Access ( and use it to get location and unique Id for each Access db from SQL server table. Now I am trying to add column with unique id for each Access database

using derived column and got error "The data type of variable "User::project_code" is not supported in an expression. Variable is object type. Maybe you can direct me how to solve my problem.

Thank you

February 22, 2011 10:53 AM

andyleonard said:

Hi Irina,

  To be used in a data flow, an object variable needs to be "shredded" or at a minimum, cast. If the object contains a scalar, casting it to a discrete data type is the way to go. If the object contains a collection, array, or dataset, shredding the contents is your best option.

Hope this helps,


February 22, 2011 12:38 PM

Seshi said:


I have a date column coming in the dataflow and need to change as below

please suggest


2011-01-18 00:00:00.000



March 10, 2011 11:41 AM

Ed said:

You are the Man...

April 14, 2011 4:28 PM

Dean said:

I like the example - i echo some of the comments that I wish MS depts would talk to each other one day.  I think there are about 14 different syntax and/or like command phrases depending on which sub-module of sql you happen to fall into.  Good God is this frustrating.

April 18, 2011 12:47 PM

srini said:

We can write the name expression as FirstName +" "+ Middle Name +" " Last Name, instead off using NULL

May 3, 2011 3:37 PM

SRINI said:








May 26, 2011 6:04 PM

SRINI said:



May 26, 2011 7:21 PM

VV said:

Gett WeekDay name from SSIS

(DT_STR,50,1252)(DATEPART("dw",MyConvertedDate) == 2 ? "Monday" : DATEPART("dw",MyConvertedDate) == 3 ? "Tuesday" : DATEPART("dw",MyConvertedDate) == 4 ? "Wednesday" : DATEPART("dw",MyConvertedDate) == 5 ? "Thursday" : DATEPART("dw",MyConvertedDate) == 6 ? "Friday" : DATEPART("dw",MyConvertedDate) == 7 ? "Saturday" : DATEPART("dw",MyConvertedDate) == 1 ? "Sunday" : "InvalidDAY")

June 13, 2011 9:14 AM

citytower said:

can i use a variable as the expression.

such as:

var1 is a string and the value is "column1:" + [column1].

and i want use it as the expression on the derived column component.

July 4, 2011 12:40 PM

Dominique said:


I am trying to populate a lookup field in SharePoint from a nvarchar in SQL.

Any idea?



July 26, 2011 10:07 AM

Rob D said:

Hello all,

I hope some has an easy answer to this one.  I have a problem where I cannot get a derived column to write to my destination SQL Server DB.  I am able to do a simple mapping by defining a new column as a static integer value (say "42").  A data viewer shows me the transformation is happening correctly but the end result never writes to the db.  I just see nulls.   Anybody know what's going on?


Rob D.

July 27, 2011 9:32 AM

andyleonard said:

Hi Rob,

  Check the OLE DB Destination and make sure the derived column is mapped to a column on the Mappings page. That's easy to overlook.

Hope this helps,


July 27, 2011 9:39 AM

Rob D said:

Sorry, forgot to include that in my description, but yes, I have the mappings in my OLE DB Destination too.  Essentially the whole data flow is:

[Excel Source] --> [Data Conversion] --> [Derived Column] --> [OLE DB Destination]

The Data Conversion step is to take care of some unicode/non-unicode compatibility issues between the Excel Spreadsheet and the SQL Server 2008 R2 database.  As far as I can tell, this should be working.  The Data Viewer says it is...

July 27, 2011 9:52 AM

andyleonard said:

Hi Rob,

  My next troubleshooting step would be to create a new table and make sure the Data Flow can load it.


July 27, 2011 10:31 AM

RAMU said:

Is it if the source column is of nvarchar and if you do replace column in derived column with (DT_STR,10,1252), will it be of DT_STR/

because even after casting it is saying of DT_Wstr.

is it the only option is add new column and use the column to convert to DT_STR type

August 24, 2011 12:04 PM

andyleonard said:

Hi Ramu,

  You cannot change the data type of a column in a Derived Column Tranformation using the "Replace" functionality. If you want to change the data type, you have to select "<add as new column>" in the Derived Column dropdown.


August 24, 2011 12:15 PM

Gopi said:

I have to make an expression like

If isexited == true then

status == 'E'


same status

(Here we are setting value for one input column based on other input value)

is it possible thru derived column expression? if so could you plz let me know how to handle it?

August 29, 2011 8:37 PM

andyleonard said:

Hi Gopi,

  The expression for:

If isexited == true then

status == 'E'


same status


(isexited) ? "E" : <status>

  This assumes a column named status of a DT_STR or DT_WSTR SSIS data type, another column named isexited of the DT_BOOL SSIS data type, and that you are using the Derived Column Transformation to replace the value of the status column.

Hope this helps,


August 30, 2011 8:39 AM

vms said:

very nice post .......

thanks for the info ....

September 6, 2011 7:46 AM

G said:


Great article great illustration, i have been breaking my head with the trying to extract Date out of the datetime field. All the forums gave many options on how to type cast and I tried without any success. None gave the important info that derived column task doesn't have the ability to change the existing columns' datatype, but you did. I'm really impressed and appreciate your in depth explanation of how the derived column expression builder works.

Thanks a lot,


October 12, 2011 3:55 PM

Alpesh said:

Hi Andy,

I am not able to map derived column on OLEDB destination. On column mapping page, I can see derived column in Available input column list but there is no such column in Available destination column list. Do we have to add that manually? If yes, how can I add?


October 29, 2011 4:50 AM

Tasha said:

Great article, just what I needed.

November 20, 2011 12:38 PM

haryik said:

Thanks Andy... :-)

November 23, 2011 5:23 AM

neelima said:

thanks a lot .. this post along with snapshot helps a lot ...

January 15, 2012 12:02 PM

David Walker said:

Note that IsNull returns False when it is passed a string of blanks, or an empty string.  If you are parsing a text file with SSIS, and have a derived column transformation, you'll get False when testing things like MiddleName with IsNull (unless you have transformed the MiddleName with another transformation earlier in the process).

February 3, 2012 1:01 PM

Santy said:

I Have a float data type column in a text file source with some data values 192.3,200.08,191,300.6,82,etc, i want to import this data values in SQL Database with the column being of float data type in the database, as i would use this column as the measure in cube design!

I did do a import from text file to sql database with source and destination columns of float data type but i am getting a data conversion error because the data value 191 is of integer format.

can anyone tell me how to derive a column with data type as float with values intact in SQL Database.

February 7, 2012 10:00 AM

Yasemin said:


What if i have more than one condition like :


need advice,


March 15, 2012 5:31 AM

anil said:

am getting this error, source is Flat file and Datatype is string, Target is Sybase and Datatype is Numeric(20,3) using SSIS

am converting this formula [(DT_NUMERIC,20,3)SUBSTRING(FILE_STRING,44,11)) / 1000] then am getting this error

"Domain error during implicit conversion of NUMERIC value ''to a NUMERIC field".

May 3, 2012 2:04 PM

kushy said:


i have 4 sheets that i need to put in one table but some of them have names with spaces so i have to remove the spaces EG,mmmnu 264415 and neldf455156 oi am still an intern i am strugling with this

i have made the ff: excelsource      excelsource1

                            union all

                            derived column {i am struggling here}

i did create onerther one without the derived column i works pefectly but i must remove the spaces

please help!!

January 25, 2013 3:25 AM

Patrick said:

I'm new to SSIS and your example was straight forward and very helpful.

April 14, 2013 8:47 PM

Jane said:

How do you extract the domain name from an email address with SSIS expression?  I need to parse out domain.

April 18, 2013 6:11 PM

andyleonard said:

Hi Jane,

  I use an expression like:

LEFT(EmailAddress,FINDSTRING(EmailAddress,"@",1) - 1)

  to obtain the email recipient, and like:

RIGHT(EmailAddress,LEN(EmailAddress) - FINDSTRING(EmailAddress,"@",1))

  to obtain the domain information.

Hope this helps,


April 18, 2013 7:31 PM

Jane said:


Thanks so much for your quick response!  How do I also strip off the .com from the address so I just have the 'Name'.  Therefore, all I am left with is 'name'.  That is really the challenge.   I have been playing around with the expression you sent but I can't get it to just parse out name.


RIGHT(EmailAddress,LEN(EmailAddress) - FINDSTRING(EmailAddress,"@",1))

 to obtain the domain information.


Thanks again!


April 19, 2013 12:22 PM

andyleonard said:

Hi Jane,

  There are a couple ways to approach this. One example is:

LEFT(RIGHT(EmailAddress,LEN(EmailAddress) - FINDSTRING(EmailAddress,"@",1)),LEN(RIGHT(EmailAddress,LEN(EmailAddress) - FINDSTRING(EmailAddress,"@",1))) - 4)

Hope this helps,


April 19, 2013 3:48 PM

Jane said:

Hi again Andy,

I noticed my last ? didn't post.  I am really stumped on how to parse out only the domain email name... leaving out the .com part.  Your first solution is really helpful and I tried everything I can think of to also exclude the .com.   So, should parse out 'xyz'   I appreciate any help you can give me... I am not a programming just learning SSIS.

Thanks much!


April 20, 2013 5:19 PM

Jane said:

Hey Andy,

I see it did post!  so you can ignore my last question.  Going to try this now!

Thanks again!


April 20, 2013 5:20 PM

Jane said:

Wow! That is hard to wrap your head around.  It works though!

Is there a method with SUBSTRING? or something more intuitive?  If you are really busy I don't want to take up more of your time with this, this certainly helps a lot.  I think the problem is there are not a lot of expressions available in SSIS to make this simpler.  I am definitely bookmarking your site.

By the way if the other ways are just as complex, please don't spend time on this.  :)

I so appreciate this, you have been amazingly helpful!

April 20, 2013 6:26 PM

srinivas said:

I have country names in the state column Ex: Germany, Japan etc.. state names are Abbreviated i.e: 2 length I am trying to create a new column country in the derived column and trying to move the country names into country column using the expression (Len(STATE COLUMN NAME)>2)==TRUE? But I am getting "false" in the country column and I dont see country names in either STATE or COUNTRY columns??  Can you please help me.

May 6, 2013 11:49 AM

Sanju said:


I am in a situation where I want to replace NULL values of a field with some string value but this should be based on locale . For example, NULL values should be replaced with "Missing Value" if the locale is English and with "Mangler" if the locale is Danish.

Any help would be highly appreciated.


July 22, 2013 2:06 PM

John said:

Hi Andy,

How to store a derived column result to a variable. The derived column returns only one output not more than that.. I want to store the derived column output to the variable. Please advise. thanks ..



November 19, 2013 11:34 PM

ivan said:

great job

November 27, 2013 2:13 PM

Lan Nguyen said:



February 18, 2014 2:29 PM

Steve said:

How do I convert a DATE to a DATETIME using derived column ? I want to simply copy rows from one table to another. Target sql server does not recognize date, but source does. Hence column in target is datetime. That prevents my package from running. Thanks in advance.

March 8, 2014 6:00 PM

andyleonard said:

Hi Steve,

  You use a Type Cast like the DT_STR type cast in this post. To convert to a SQL Server datetime, use (DT_DBTimeStamp) as the cast operation.

Hope this helps,


March 9, 2014 4:21 PM

JaY said:

Hi Andy,

Very Informative article . I do have a concern How can I convert a DT_STR to DT_CY in Derived column transformation editor ??  

August 26, 2014 8:51 PM

andyleonard said:

Hi JaY,

  You can cast a string to currency using (DT_CY)<string_field_name>.

Hope this helps,


August 27, 2014 9:21 AM

JaY said:

Hi Andy,

Thanks for your response I tried using (DT_CY)<string_field_name> .

I am getting these errors now --

[Derived Column [2642]] Error: An error occurred while attempting to perform a type cast.

[Derived Column [2642]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR.  

The "component "Derived Column" (2642)" failed because error code 0xC0049064 occurred,

and the error row disposition on "input column "Child_Coverage_Amt" (2832)" specifies failure on error.

An error occurred on the specified object of the specified component.  

There may be error messages posted before this with more information about the failure.

Please help me .

Thanks a lot in advance

August 27, 2014 2:40 PM

andyleonard said:

Hi JaY,

  I suggest adding a Union All transformation near the Derived Column Transformation that is failing. Connect the Derived Column's Error Path (the red arrow) to the Union All transformation. When you do this, you will be prompted to configure the error disposition of the columns included in the Derived Column transformation. Select the Error field for the column in question and, in the dropdown, choose "Redirect row." Click OK to close the Error Configuration window. Right-click the error path connecting the Derived Column transformation to the Union All transformation and click "Data Viewer" or "Enable Data Viewer" (depending on which version of SSIS you are using). If you clicked "Data Viewer," click the Add button and add a Grid data viewer. Click OK and re-execute the package. The offending row(s) will be sent to the Derived Column's error path. Since this path has a grid data viewer configured, it will display the data in the rows in error. This should help you isolate the data raising the error.

Hope this helps,


August 27, 2014 9:03 PM

Badri said:

Thanks a lot.!

January 12, 2015 10:47 AM

Umadevi R said:

I have created an SSIS package with Derived column to convert the input to upper case. My input file has only 3 records. When I debug the package it is getting stuck at the Derived column execution and is never ending. What could be the cause of this? Could you please suggest?

January 15, 2015 8:25 AM

KentD said:

How do I can convert String(char19) with sample type: YYYY/MM/DD HH:MM:SS

to Datetime. Please help me. thanks you a lots.

January 19, 2015 5:51 AM

andyleonard said:

Hi KentD,

  For today's date at the local time of my typing, your char(19) would equal '2015/01/19 08:38:30'. To convert this to a SQL Server DateTime data type in SSIS I would using an SSIS Expression Language Casting function. It would look like this:

  (DT_DBTIMESTAMP)'2015/01/19 08:38:30'

Hope this helps,


January 19, 2015 8:40 AM

Dominic said:

Hi ... I am not very familiar with the transformations in SSIS and I am ingesting a Flat File into A relational DB ... I have 9881 <CRLF> delimited lines in the source with 0 making it to the destination.

I am receiving the following 5 errors:


[OLE DB Destination [356]] Error: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80040E21.

An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"  Hresult: 0x80040E21  Description: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".


[OLE DB Destination [356]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR.  The "input "OLE DB Destination Input" (369)" failed because error code 0xC020907B occurred, and the error row disposition on "input "OLE DB Destination Input" (369)" specifies failure on error. An error occurred on the specified object of the specified component.  There may be error messages posted before this with more information about the failure.


[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED.  The ProcessInput method on component "OLE DB Destination" (356) failed with error code 0xC0209029 while processing input "OLE DB Destination Input" (369). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.  There may be error messages posted before this with more information about the failure.


[Flat File Source [235]] Error: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020.


[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED.  The PrimeOutput method on component "Flat File Source" (235) returned error code 0xC02020C4.  The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.  There may be error messages posted before this with more information about the failure.

Do you have any idea where i'd start to solve this ?

April 14, 2015 9:21 PM

Bob said:

I'm so confused!  NONE of this works for me at all!!

I need to convert an integer field from a QDWA data warehouse to an NVARCHAR field (Unicode) with leading zeros.

I have tried all of these examples (revelent) but only this one works and it has no leading zeros.  (I need 3 characters).

May 14, 2015 6:32 PM

Reddy said:

I am so confused!!! how to load multiple excel files for dynamicaly load pls explain in step by steps.

July 9, 2015 3:35 AM

bob said:

how to convert o as 'scheduled' and 1 as 'random'.

can any one suggest me .

August 22, 2015 1:37 AM

junaib said:

Hi i want to check the below mentioned condition in Derived column in ssis.

SaleAmount > 0 ? "passed"  

  Act_amt > 0 ? "clear"  

   Clear_amt > 0 ? "ok"

how this is possible.

January 11, 2017 7:06 AM

andyleonard said:

Hi junaib,

  Assuming that's the order for which you want to test the conditions (check for "passed" first, then check for "clear", and then check for "ok"), this may work:

 ((SaleAmount > 0) ? "passed" : ((Act_amt > 0) ? "clear" : ((Clear_amt > 0) ? "ok")))

Hope this helps,


January 11, 2017 11:13 AM

junaib said:

Below Error is showing

TITLE: Microsoft Visual Studio


Error at Derived_Column_Examples [Derived Column [2]]: Attempt to parse the expression "((SaleAmount > 0 ) ? "passed" : ((Act_amt > 0) ? "clear" : ((Clear_amt > 0) ? "ok")))" failed. The expression might contain an invalid token, an incomplete token, or an invalid element. It might not be well-formed, or might be missing part of a required element such as a parenthesis.

Error at Derived_Column_Examples [Derived Column [2]]: Cannot parse the expression "((SaleAmount > 0 ) ? "passed" : ((Act_amt > 0) ? "clear" : ((Clear_amt > 0) ? "ok")))". The expression was not valid, or there is an out-of-memory error.

Error at Derived_Column_Examples [Derived Column [2]]: The expression "((SaleAmount > 0 ) ? "passed" : ((Act_amt > 0) ? "clear" : ((Clear_amt > 0) ? "ok")))" on "Derived Column.Outputs[Derived Column Output].Columns[Derived Column 1]" is not valid.

Error at Derived_Column_Examples [Derived Column [2]]: Failed to set property "Expression" on "Derived Column.Outputs[Derived Column Output].Columns[Derived Column 1]".



Exception from HRESULT: 0xC0204006 (Microsoft.SqlServer.DTSPipelineWrap)





January 11, 2017 11:26 PM

junaib said:

Hi, i solved this issue by converting those fields into float

January 12, 2017 10:23 PM

Leave a Comment


This Blog



My Companies

Community Awards

Friend of Red Gate

Contact Me


Privacy Statement