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

Derived column in SQL Server 2005 SSIS

Hi,

I am trying to upload a csv file into a destination SQL Server table using Data FLow objects in MS SQL Server 2005 SSIS. My destination table X has a date column while my source data file (for which i have a flat file connection) does not have a date column. I created a Derived column for date using the system date function in my Source data object and ran the package but it returned errors. I changed the data type of the derived column but I still get errors.

is there any other way i can get the date on the fly i.e generate a date for the incoming source file and map that (insert into) to the destination table ?

Thanks.

Post your errors here and we can then better help you. Also ensure that you have the correct date types flowing into the given date column.|||

Devived column name - Name for your date field

Devived column - should be <add as new column>

The expression columns should be GETDATE()

On the advanced Editor - Make sure to select the date name to be used in the output columns.

this should work for you.

Hammer.

Deployment options help!

Sorry I am a newbie in SSIS.

I have got a small project that requires to feed in a .CSV flat file and load the data into SQL server 2000.

I developed a SSIS package for this and get it working in my computer, but I need to deploy it to customers that they don't have VS 2005 or SSIS installed. May any one of you give me some clues on that?

I played around with the flat file deployment and again it seems only working on my computer as I have everything installed.

Did you read BOL?

http://msdn2.microsoft.com/en-us/library/ms180167.aspx

http://msdn2.microsoft.com/en-us/library/ms137900.aspx

or the SSIS management whitepaper?

http://www.microsoft.com/technet/prodtechnol/sql/2005/mgngssis.mspx

or other sources?

http://www.databasejournal.com/features/mssql/article.php/3600201

http://blogs.conchango.com/jamiethomson/archive/2006/01/23/2702.aspx

http://blogs.conchango.com/jamiethomson/archive/2006/02/20/2902.aspx

I found these by typing "SSIS deployment" into Google. The help is there if you go and look for it.

-Jamie