[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SSIS - derived column transformation

Posted on 2009-04-28
10
Medium Priority
?
1,909 Views
Last Modified: 2013-11-10
I'm bringing data in from v2000 into v2008;  the two columns in question are DATETIME in the source, yet they're going into DATETIME2.

simple data flow, oledb source, sql server destination, using sql command.  but,
the conversion between types DT_DBTIMESTAMP and DT_DBTIMESTAMP2 is not supported...

Now I am trying to do a derived column transformation, such that i could map the derived column to the destination, not the original

i've added the derived column transform in between the source and destination in the data flow tab.  I am doing this with the understanding that i need only address the two columns in the derived transform editor, and everything else will map just fine.

derived column transformation editor
derived column name / derived column /  expression  /  datatype
columnA / Replace 'ColumnA'  / (DT_DBTIMESTAMP2,27)ColumnA / database timestamp [DT_DBTIMESTAMP]

that expression fails to parse

i've tried this, too:    (DT_WSTR,27)columnA
That one parses, but it gets me back to the conversion error between types DT_DBTIMESTAMP and DT_DBTIMESTAMP2

I am surely just handling the expression in error.

Is anyone able to advise?



0
Comment
Question by:dbaSQL
  • 6
  • 2
  • 2
10 Comments
 
LVL 17

Author Comment

by:dbaSQL
ID: 24252262
any ideas?
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 24252881
i've changed my expression to this:

(DT_DBTIMESTAMP2,7)ColumnA

still no go.  only this time it just seems as thought i'm mapping incorrectly.   initially i was doing simple sql command from v2000 database. now i'm trying the same with a flat file source.  my derived column transformation actually appears correct.  but now it seems i am failing to map the other columns.
my error log is attached.

is anybody able to suggest anything?
Import.txt
0
 
LVL 8

Expert Comment

by:k_rasuri
ID: 24253586
your error message is not related to datatype
" "Cannot insert the value NULL into column 'OrderTime'".....this is what i see in the file attached. If the OrderTime column is NOT NULL, change it to NULL
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 17

Author Comment

by:dbaSQL
ID: 24253869
As I said, I think my transform is correct now, but my mapping must be off.
OrderTime is NOT NULL, by design.  There are no NULL OrderTimes in source, and there won't be in destination.  
0
 
LVL 8

Assisted Solution

by:k_rasuri
k_rasuri earned 800 total points
ID: 24253909
why dont you load the results into some temp table and see what values you are getting for OrderTimes from the source. If you see NULL values then there should be some problem with the mapping
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 24253980
I appreciate that.  But I can assure you, there are no NULLs in the data source.  That attribute is part of the PK and not NULL by design.  This dataset is upwards of 3TB.  I would rather not load into staging or temp, unless I really need to.  

I will keep digging.
0
 
LVL 17

Accepted Solution

by:
HoggZilla earned 1200 total points
ID: 24259153
The only way you should be getting an error converting DT_DBTIMESTAMP to DT_DBTIMESTAMP2 is if the fractional value in the source is greater than allowed (7). So, because you are getting an error converting, it must be something else.
Are you going to import from the db or a flat file?
0
 
LVL 17

Expert Comment

by:HoggZilla
ID: 24259250
By the way, the error you got from the Flat File Data Flow looks like a buffer error. In the properties for your Data Flow task, put a path for the BufferTempStorage property. I have had this error before.
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 24259375
Last night I tried it yet again... OLEDB Source, Data Conversion, OLEDB Destination

It worked.

I honestly am extremely disappointed that I couldn't get the derived column transformation working... everything I've read suggests this is the way to go.  And I would like to be able to implement the derived transformations in other projects going forward.    But...  I used a data conversion --- my two attributes that are DATETIME in source and DATETIME2 in destination, are in the transformation with a unicode datatype (DT_WSTR), length of 27.  I am not sure if this is ideal, but it is the ONLY way I could get the damned thing to work.  i found many references on line to bugs with the two different datatypes (DT_DBTIMESTAMP, DT_DBTIMESTAMP2).... https://connect.microsoft.com/SQLServer/feedback/Workaround.aspx?FeedbackID=320897&wa=wsignin1.0

maybe that was a part of it... who knows.    

I wrote 3969952717 records from source to destination, without error.

In parallel to all of that, I created another package, too.  Only this one is the flat file to sql destination.  I've changed the properties on the two input columns to DT_DBTIMESTAMP2.    dunno.... but I'm going to give it a go.  

0
 
LVL 17

Author Comment

by:dbaSQL
ID: 24263932
I'm going to split amongst you both and close this out.  I would still truly like to figure out the derived transformation... but, the current issue is resolved.  (using a conversion)
thank you both for trying to help out
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question