I have a DTS package that imports excel spreadsheets.
The excel spreadsheet cells contain numbers based on vlookups. Now negative values appear in these cells as -999 but when loaded into the table they appear as (999).
Is there anyway way to configure it to just load -999? The tricky part is I don't have control over the format of the spreadsheet cell and the destination table field is varchar. I am hoping there is some way that the DTS interprets and picks up the value as it is displayed in excel.
Or do I have to stick with converting it manually by replacing '(' and ')' with nothing and appending '-'?
you will have to alter the default transformation. edit the activex transformation as below
'---for other columns
DTSDestination("Col1") = DTSSource("Col1")
'----for the column Col2 with (999) etc value
DTSDestination("Col2") = "-" & DTSSource("Col2")
DTSDestination("Col2") = DTSSource("Col2")
Main = DTSTransformStat_OK