Using SQL Server 2008 Express.
In the past, I've made DTS packages for MSDE (SQL2000). Looking for some guidance on the following project:
Need to develop a weekly process to update a specific column in a specific table in a SQL 2008 Express database. The column contains full Employee Names. Destination field is called PL5 and is PrimaryKey and it does not accept nulls or duplicates.
Source: XLS or MDB. Columname is "Fullname"
Destination Database Name: Personnel
Table Name: PL5
Column Name: Item (PK, varchar, no nulls)
Using DTS:
1. Despite finding out that you cannot save DTS packages in SQL 2008 express, I got it to work with the DTS install that was made for SQL2000. In SQL Server Mgt Studio under Legacy DTS, I can see and run the saved packages.
2. Can get FullName field from XLS spreasheet.
3. Excluded nulls by using "Fullname<>null"
3. Tried to insert that data to a column in a specific table. However, I get errors that indicate no duplicates.
Question: Is there a statement that can be used in DTS to compare the XLS to the target column so that only NEW records are transferred?
Maybe I should look at Stored Procedures?
Helpful comments appreciated. Thanks.