DTS import, Add 2 columns Date and Source

[Barneye]

I have a inventory I import daily from a Pick system via ODBC. I need to add 2 columns, Date and Source. Date being the date the import occured. Source will always be a fixed value. Can I do this with a DTS package?

I am running SQL 2000 SP4. I am new to SQL so the simpler the better!

Thanks!

 

[quatermass]

Hi,

There are a couple of ways to do this

a) assuming that your target table has a datetime field for the import date, set the default value of this field to

getdate()

and the default value of the source field to be 'PICK' for example

b) in the dts package on the source for the transform, make the source a sql statement rather than a direct table/view source. The sql statement will be along the lines of

select *, sysdate, 'PICK' from table

where sysdate is the pick sql command for the current system date

c) in the transformations for the job, highlight the 2 destination fields for import date and source, click new and select ActiveX script

DTSDestination("import date") = Now
DTSDestination("source") = "PICK"

This will give you a transform line with no source


It is possible to mix and match the above, eg a default date value on the table with an ActiveX script for source

Hope this helps

**************************************************************
Rock is Dead (Long Live Paper and Scissors)**************************************************************

 

[Barneye]

Sorry I took so long getting back!


I used the ActiveX script and it worked great!

Thanks!