MSSQL - DTS Package - Find distinct rows - Output to TXT file - ActiveX?

MSSQL - DTS Package - Find distinct rows - Output to TXT file - ActiveX?

Post by jb1 » Thu, 14 Jun 2007 00:18:56


Hello All,

I am trying to create a DTS package.
I have two tables tbl_A and tbl_B with similar data/rows but no
primary keys.
tbl_A is master.

I would like this package to query tbl_A and tbl_B and find
1)all rows in tbl_A that are different in tbl_B, 2)all rows in tbl_A
that are not present in tbl_B and
3)all rows in tbl_B that are not present in tbl_A, and then just show
those rows.

Can this be done with a simple UNION?

Perhaps this could produce a temp Table that can be dropped once the
DTS package exists successfully.

The 2nd part after all the above rows are retrieved is that I would
like to add an addional Column to the retrieved data called STATUS
which has 3 possible values(letters) at the end of each row...

M (modified) means that row exists in tbl_B but has 1 or more
different columns
A (add) means this row exists in tbl_A but not in tbl_B
D (delete) means this row exists in tbl_B but not in tbl_A

I'm hopping this DTS package would output a nice comma seperated TXT
file with only...
1) rows from tbl_A that are different in tbl_B (STATUS M)
2) rows from tbl_A that are not present in tbl_B (STATUS A)
3) rows from tbl_B that are not present in tbl_A (STATUS D)


Can a DTS package in MS SQL be used to perfom all of the above tasks?
I would very much appreciate any help or any advise.

Thanks in advance :-)