Update data from excel using DTS 2

[sqlturbo]

I would like to update selected records in a Sql server table from an MS Excel spreadsheet using DTS. I tried to do this using Transfrom Data Task but it only lets me select data from excel. It does not let me write a update query. I could bring the data into a temp table in sql serer and then perform the update but I would prefer not to create the temp table.

Is there a way I could perform the update without the temp table?

Thanks!

 

[danceman]

Create a ODBC link to the excel file. If not already installed, you may need to install the driver excel driver.

Attitude is Everything

 

[sqlturbo]

danceman: Connectivity is not the issue here.
What I want to do is UPDATE data in a SQL Server table using data in the spreadsheet in one step using DTS.

 

[ks2000]

Why you want to UPDATE those records through DTS. Are you going to repeat this UPDATE process again.

You can UPDATE the recrods in DTS package With the Task "Execute SQL Script".

Use the following sub query to join with your sql server table and write update command.

select * FROM OPENROWSET ('Microsoft.Jet.OLEDB.4.0','Excel 8.0;DATABASE=<excel file path>', 'Select * from [<Sheet Name>]')

Good Luck
Gopala Krishna Kakani

 

[sqlturbo]

Thanks Gopala. That worked.

 

[donutman]

Gopala, that is awesome. I had written an entire VB app to upload data to a table. I could have done it with one Update query!
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[Modica82]

Hi all,

could someone give me an example of this update command?

Thanks,

Rob