DTS package to update a table

[Guest_imported]

I have table with fields like "ID" ,"name" ,"Amount" etc.
I need to UPDATE the Amount field for matching "Id" from an excel file which has the "id" and "amount" fields.
How can i write a DTS pcackage for the same ??

 

[aalmeida]

You will need to Connection one to Excell and one To SQL Server, then you can use Data pump to impot Excel to a temp table then update SQL from the Temp table. AL Almeida
NT/DB Admin
"May all those that come behind us, find us faithfull"

 

[Guest_imported]

Thanks for ur help
Here is what i have done .
Imported data from xcel into a temp table and then used a stored procedure to update the main table from the temp table.
But the problem is that sometimes the the xcel data might be errorneous eg one of the columns is a date field ,if date is not in proper format then the DTS inserts a null into the temporary table.IS there any way that i can trap this error when the process is running ??

 

[aalmeida]

you could use the isdate() function to determine if it is a valid date or not if not you can either try to fix it or get the system date with getdate() AL Almeida
NT/DB Admin
"May all those that come behind us, find us faithfull"

 

[Guest_imported]

Thanks for ur help
If the date is invalid i just need to skip the record and write it to and error log file or ELSE abort the whole process with an email being sent to concerned person to fix the bad date.Inserting system date in its place will not help.
Also i have a unique id field and in case this field contains a null or alphanumeric what can be done in that case??

 

[aalmeida]

Id fiels will drop your statment so you have to validate them prior to your insert and update, you could use isnull() on a where clause to make sure it is not null in case of an alphanumeric you could use isnumeric()as the example below:

where isnumeric(isnull(IdentCol, A))!= 0 AL Almeida
NT/DB Admin
"May all those that come behind us, find us faithfull"