DTS (Data Transformation Services)

Given:
I'm using a DTS Package to import data into a table. Each row from a text
file gets placed into a corresponding table.
What I need to do:
I'd like to modify the DTS Package to change the data saved to a table based
on values in the records from the text file. In otherwords,
if a value from the text file > 150 then set a value in the table to 1...
if a value from the text file < 150 then set a value in the table to 2.
Thank You
Hi Steve
You might be able to make this column a calculated one! e.g.
CREATE TABLE MyTest ( id int not null identity(1,1),
textval text,
size as DATALENGTH(textval) )
INSERT INTO MyTest ( Textval )
SELECT 'ABC'
UNION ALL SELECT 'BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB BBBBBBBBBB'
UNION ALL SELECT
'CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC'
SELECT * FROM mytest
UPDATE MyTest SET Textval = 'A' WHERE id = 3
SELECT * FROM mytest
CREATE TABLE MyTest2 ( id int not null identity(1,1),
textval text,
size as CASE WHEN DATALENGTH(textval) > 10 THEN 1 ELSE 0 END )
INSERT INTO MyTest2 ( Textval )
SELECT 'ABC'
UNION ALL SELECT 'BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB BBBBBBBBBB'
UNION ALL SELECT
'CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC'
SELECT * FROM mytest2
UPDATE MyTest2 SET Textval = 'A' WHERE id = 3
SELECT * FROM mytest2
Otherwise you can do this is an ActiveX transformation
http://www.sqldts.com/default.aspx?279,5
John
"SteveS" wrote:

> Given:
> I'm using a DTS Package to import data into a table. Each row from a text
> file gets placed into a corresponding table.
> What I need to do:
> I'd like to modify the DTS Package to change the data saved to a table based
> on values in the records from the text file. In otherwords,
> if a value from the text file > 150 then set a value in the table to 1...
> if a value from the text file < 150 then set a value in the table to 2.
> --
> Thank You
|||ActiveX transformation is the simple option for this one!!!
Thanks,
Sree
"SteveS" wrote:

> Given:
> I'm using a DTS Package to import data into a table. Each row from a text
> file gets placed into a corresponding table.
> What I need to do:
> I'd like to modify the DTS Package to change the data saved to a table based
> on values in the records from the text file. In otherwords,
> if a value from the text file > 150 then set a value in the table to 1...
> if a value from the text file < 150 then set a value in the table to 2.
> --
> Thank You