sql server dts: Regarding Step 2, you could use the following example:


INSERT INTO NewTable (col1, col2, Marketing)
SELECT col1, col2, CASE WHEN Marketing = 'NoMarketing' THEN 1 ELSE 0 END
FROM OldTable

The above example will copy all the rows to the new table, but when it
encounters a value of 'NoMarketing' it will change it to 1, otherwise 0.

See CASE in SQL Server Books Online.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @ http://vyaskn.tripod.com/


[quoted text, click to view]
Hello,

I've just discovered DTS and I'm very glad I did because I need to do
some serious data transformation tricks! I realise that I will need to
write some ActiveX scripts to convert my data, however, I am completely
new to DTS and my VBScripting skills are limited. I've used PHP for
years so it's really only the VBScript syntax (and how to leverage the
DTS objects) that is causing me difficulty. I need a nudge in the right
direction...

I have been charged with building a new database based on the data from
an old database. Basically the new one has a better, more flexible
design to it. I need to transfer the data from the old database to the
new one but it's not as easy as simply copying it all over. Both
databases are SQL Server 2000.

An illustrative example:
The old database has a table for contact data called tblContacts (id,
name, job, category,etc...). One of the problems was that the
"category" field was being used incorrectly and contacts were being
categorized as "No Marketing" so we knew not to email them. The problem
with that is their original category (i.e. their type of industry) was
lost the moment someone made them "No Marketing".

The new database's tblContacts has some boolean fields such as
"NoMarketing" so that the category field can serve it's intended purpose
of storing the contact's type.

So here's my problem. How do I:
1) Extract all the tblContact data from the old database
2) For each row, if the category is "No Marketing" then make the new
field NoMarketing = 1.
3) Insert each new row into the new tblContacts.

I think I know how to do step 1: I use an "Execute SQL Task" to extract
the data from the old tblContacts into a "rowset output parameter".

Any help for step 2 would be great. It seems like a simple if/else
statement to me but how do I build up the SQL insert statement (if
indeed that is what I should be doing)?

Step 3 really stumps me - how do I insert the data? Do I do it row by
row with SQL insert statements in VBScript? Can I build up the entire
new table and then insert it all in one go? How do you approach this?

Thank you for any help you can offer,

Alex

Hello,

I've just discovered DTS and I'm very glad I did because I need to do
some serious data transformation tricks! I realise that I will need to
write some ActiveX scripts to convert my data, however, I am completely
new to DTS and my VBScripting skills are limited. I've used PHP for
years so it's really only the VBScript syntax (and how to leverage the
DTS objects) that is causing me difficulty. I need a nudge in the right
direction...

I have been charged with building a new database based on the data from
an old database. Basically the new one has a better, more flexible
design to it. I need to transfer the data from the old database to the
new one but it's not as easy as simply copying it all over. Both
databases are SQL Server 2000.

An illustrative example:
The old database has a table for contact data called tblContacts (id,
name, job, category,etc...). One of the problems was that the
"category" field was being used incorrectly and contacts were being
categorized as "No Marketing" so we knew not to email them. The problem
with that is their original category (i.e. their type of industry) was
lost the moment someone made them "No Marketing".

The new database's tblContacts has some boolean fields such as
"NoMarketing" so that the category field can serve it's intended purpose
of storing the contact's type.

So here's my problem. How do I:
1) Extract all the tblContact data from the old database
2) For each row, if the category is "No Marketing" then make the new
field NoMarketing = 1.
3) Insert each new row into the new tblContacts.

I think I know how to do step 1: I use an "Execute SQL Task" to extract
the data from the old tblContacts into a "rowset output parameter".

Any help for step 2 would be great. It seems like a simple if/else
statement to me but how do I build up the SQL insert statement (if
indeed that is what I should be doing)?

Step 3 really stumps me - how do I insert the data? Do I do it row by
row with SQL insert statements in VBScript? Can I build up the entire
new table and then insert it all in one go? How do you approach this?

Thank you for any help you can offer,