I have two SQL Servers in the dts package. SQL_Source and SQL_Dest.

I am doing a very basic transformation that copies columns from a
SQL_Source.table1 to SQL_Dest.dest. I have a data transformation task
that does it no problem.

I am also doing a transformation from another table in SQL_Source to
the same destination (SQL_Source.table2 to SQL_dest.dest).

Here is the part I want to do nicely. There is a bit column in
Sql_dest.dest that specifies which table that row came from (table1 or
table2).

I can't just do an sql update on the table after because there is no
way to tell which records came from table1 and which came from table2.

What is the simplest way to do this? The order of the workflows
doesn't matter to me at this level as long as all the data gets there
properly.

I was looking at a data-driven transformation but I'm not sure that is
what I need. What I want is for the transformation to happen, then
update the records that were just transformed. Then do the same for
the next transformation.

Thanks,
Dave.

In article <4d053ad3.0407130823.7024b7db (AT) posting (DOT) google.com>, Dave
wrote:
Why not add Something like

SELECT ..........................., 'TABLE1' as TableName FROM TABLE1
UNOIN ALL
SELECT ..........................., 'TABLE2' as TableName FROM TABLE2







Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
www.konesans.com - for all your consultancy needs

Well, I found a solution. Instead of using a "Copy Column" type of
transformation I changed it to an "ActiveX Script" type.

This allows me to add an extra destination column for Sql_dest.dest in
the list of destinations, and edit the vbscript so that
Destination("dest") = '1' for all the rows that are executed using
that script.

Just incase anyone was wondering. Pretty simple.

google (AT) xenogear (DOT) net (Dave) wrote in message news:<4d053ad3.0407130823.7024b7db (AT) posting (DOT) google.com>...