Somewhat noobish here with SQL. Have done some packages with DTS and
MSDE. 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 EmpNamePL and is
PrimaryKey and it does not accept nulls or duplicates.

Source: XLS or MDB

Destination Database Name: Personnel
Table Name: PL5
Column Name: Item (PK, varchar, no nulls)

I'm thinking DTS or Stored Procedure:

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 SQL
2000 by using Legacy DTS.
2. Can get FullName field from XLS spreasheet.
3. Tried to insert that data to a column in a specific table. However,
I get errors that indicate no duplicates or nulls.

Question: Is there a statement that can be used in DTS to transform
the XLS to ensure there are no nulls or duplicates?

STORED PROCEDURE:
1. Not sure how to start here - Copy Fullname field from XLS or MDB
into the existing EmpNamePL?
2. Schedule a weekly execution of the Stored Procedure?

Thanks for any constructive comments.

dorkygrin (dorkygrin (AT) gmail (DOT) com) writes:
This sounds somewhat venturous to me. To be able to update a row,
you would need to correlate with the source. That is usually what
you use the primary key for. In this case you update the primary
key, so how do you now know what rows to update and with what?

DTS has been replaced by SQL Server Integration Services but SSIS does
not come with SQL Express. Since DTS is an old technology that probably
will go away with some later release, it seems better to built a solution
without DTS. One alternative is to use OPENROWSET and the Jet provide
to read the Excel files. I have never done this myself, so I cannot
give any examples, but Google should be able help you.

What is more problematic is if the data in the Excel file does not
conform with the business rules. You could read the data into a temp
table and perform some cleansing on. But given that it includes PK
data, I would be hesitent to accept errors at all...

SQL Express does not come with Agent, but you can schedule job with
Windows Task Scheduler. Use SQLCMD to execute your stored procedure.


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx