Using DTS to update SQL PK Column

[dorkygrin]

I'm trying to make a DTS package to update a field in SQL. This
package would run once per week or when a user forces it to run using
a batch file that contains DTSRUN.exe

The DTS package is should do this:

1. Get data from XLS spreasheet. Two Fields. EmpID and Fullname
2. Insert that data to a column in a specific table. The column is a
PK and does not allow duplicates or nulls.

Is there some statement I can use to transform the XLS to ensure there
are no nulls or duplicates?

I could move the XLS to a Access MDB if that makes it easier.

[Todd C]

If it simply MUST be DTS, then I suggest you load the entire XLS data into a
staging table. Then run a singe T-SQL statement that will insert the missing
rows into your table with the PK:

INSERT INTO dbo.<My Table With Primary Key>
( [EmpID], [FullName] )
SELECT stage.[EmpID], stage.[FullName]
FROM dbo.<my staging table> AS stage
LEFT OUTER JOIN dbo.<My Table With Primary Key> AS existing
ON stage.[EmpID] = existing.[EmpID]
WHERE existing.[EmpID] IS NULL
AND stage.[EmpID] IS NOT NULL

If, however, you can move to SSIS, then you can set the Destination Adapter
to reject any rows that violate the primary key and you can also send said
rows to another data pipeline and perhaps its own text file destination. That
way you could SEE what rows were rejected.

HTH
--
Todd C
MCTS SQL Server 2005

Post by dorkygrin
I'm trying to make a DTS package to update a field in SQL. This
package would run once per week or when a user forces it to run using
a batch file that contains DTSRUN.exe
1. Get data from XLS spreasheet. Two Fields. EmpID and Fullname
2. Insert that data to a column in a specific table. The column is a
PK and does not allow duplicates or nulls.
Is there some statement I can use to transform the XLS to ensure there
are no nulls or duplicates?
I could move the XLS to a Access MDB if that makes it easier.
.