THE SQL Server Blog Spot on the Web

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

Andy Leonard

Andy Leonard is CSO of Linchpin People and SQLPeople, an SSIS Trainer, Consultant, and developer; a Business Intelligence Markup Language (Biml) developer; SQL Server database and data warehouse developer, community mentor, engineer, and farmer. He is a co-author of SQL Server 2012 Integration Services Design Patterns. His background includes web application architecture and development, VB, and ASP. Andy loves the SQL Server Community!
Note: Comments are moderated. Spam shall not pass! </GandalfVoice>

SSIS Expression Language and the Derived Column Transformation

Introduction

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 http://www.codeplex.com/MSFTDBProdSamples/Release/ProjectReleases.aspx?ReleaseId=4004).

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:

Select
  ContactID
 ,Title
 ,FirstName
 ,MiddleName
 ,LastName
 ,EmailAddress
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):

NULL(DT_WSTR, 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).

Conclusion

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

Comments

 

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:

Hi,

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

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

Thanks

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...

thanks...

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:

hi!

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,

Andy

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,

Andy

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:

Hello,

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 (mssqltips.com/tip.asp?tip=1437) 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,

Andy

February 22, 2011 12:38 PM
 

Seshi said:

Hi,

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

please suggest

Source

2011-01-18 00:00:00.000

Target

18012011

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:

I HAVE FLAT FILE SOURCE LIKE

1"ram"100

2"raj"400

3"reddy"890

4"rao"900

5"manju"1000

I NEED REPLACE " WITH ,(COMA) AND LOAD INTO TABLE . WHAT TYPE OF EXPRESSION CAN USE?

May 26, 2011 6:04 PM
 

SRINI said:

HI SESHI YOU CAN USE BELLOW QUERY TO CONVERT

"SELECT CONVERT(VARCHAR(10),GETDATE(),104)"

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:

Hello,

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

Any idea?

Thanks,

Dom

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?

Thanks,

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,

Andy

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.

Andy

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.

Andy

August 24, 2011 12:15 PM
 

Gopi said:

I have to make an expression like

If isexited == true then

status == 'E'

else

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'

else

same status

  reads:

(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,

Andy

August 30, 2011 8:39 AM
 

vms said:

very nice post .......

thanks for the info ....

September 6, 2011 7:46 AM
 

G said:

Andy,

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,

G

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?

Thanks

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:

Hi,

What if i have more than one condition like :

CASE WHEN EROED = '00000000' OR EROED = '' OR EROED IS NULL OR LENGTH(EROED) <>8 THEN  NULL  WHEN EROED = '20020631' THEN '20020630' ELSE  TO_DATE(DT,'YYYYMMDD')  END

need advice,

thanks

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:

hi

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?  xyz@domain.com  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,

Andy

April 18, 2013 7:31 PM
 

Jane said:

Andy,

Thanks so much for your quick response!  How do I also strip off the .com from the address so I just have the 'Name'.  xyz@Name.com.  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!

Jane

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,

Andy

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, jane@xyz.com should parse out 'xyz'   I appreciate any help you can give me... I am not a programming just learning SSIS.

Thanks much!

Jane

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!

Jane

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:

Hello,

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.

Thanks

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 ..

Reg

John

November 19, 2013 11:34 PM
 

ivan said:

great job

November 27, 2013 2:13 PM
 

Lan Nguyen said:

Helpful!!

Thanks.

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,

Andy

March 9, 2014 4:21 PM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

My Company


Other Blog

Check out my personal blog...
http://andyleonard.me

Contact Me

Twitter: @AndyLeonard
Email: andy.leonard@gmail.com

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement