Derived column usage when column does not exist in source (but exists in destination)

Posted - 09/10/2007 : 15:53:26

---

Hey all - got a problem that seems like it would be simple (and probably is : )

I'm importing a csv file into a SQL 2005 table and would like to add 2 columns that exist in the table but not in the csv file. I need these 2 columns to contain the current month and year (columns are named CM and CY respectively). How do I go about adding this data to each row during the transformation? A derived column task? Script task? None of these seem to be able to do this for me.

Here's a portion of the transformation script I was using to accomplish this when we were using SQL 2000 DTS jobs:

'**********************************************************************
' Visual Basic Transformation Script
'************************************************************************

' Copy each source column to the destination column
Function Main()
DTSDestination("CM") = Month(Now)
DTSDestination("CY") = Year(Now)
DTSDestination("Comments") = DTSSource("Col031")
DTSDestination("Manufacturer") = DTSSource("Col030")
DTSDestination("Model") = DTSSource("Col029")
DTSDestination("Last Check-in Date") = DTSSource("Col028")
Main = DTSTransformStat_OK
End Function
***********************************************************
Hopefully this question isnt answered somewhere else, but I did a quick search and came up with nothing. I've actually tried to utilize the script component and the "Row" object, but the only properties I'm given with that are the ones from the source data.

thanks in advance!

jm

It could be easily done by using the Derived Column task.

Just specify 2 new derived column names:

Code Snippet

Derived Column Derived Column Expression

============================================================

Month <add as a new column> MONTH(@.[System::StartTime])

Year <add as a new column> YEAR(@.[System::StartTime])

Regards,

Yitzhak