Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
josefismael
Starting Member
2 Posts |
 Posted - 2007-09-10 : 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! "Lord, beer me strength." |
|
|
mwjdavidson
Aged Yak Warrior
735 Posts |
Posted - 2007-09-11 : 07:06:57
|
| A derived column task will do the trick. What problems have you had with this? |
 |
|
|
josefismael
Starting Member
2 Posts |
Posted - 2007-09-11 : 17:25:41
|
| *answered my own question*Apparently you have to add your derived column component BEFORE you add the destination component. Then, select <add as new column> in the derived column editor. ONLY THEN, when you add your destination component, you will be able to select these "new columns" and map them to their corresponding columns in your destination table.The thing is, it kind of makes sense. Sort of.jm"Lord, beer me strength." |
|
|
|
|
|
|
transformation script in SSIS