sql server dts: DTS for Import Export TO And From EXCEL -- sql server dts

DTS for Import Export TO And From EXCEL

Prabhat
5/19/2005 6:42:28 PM

sql server dts:

Hi All,

I want to design a DTS Package that will read an EXCEL Document (One Data
Source) and ONE SQL Server (2nd Data Source) and Execute one Query which
will have a JOIN from Both the source and Export the result to another Excel
Document.

How Can I perform that using DTS?

I have took 3 Connections 1) SQL Server 2) Excel -> These tow for Source
And 3) Excel Connection for Export the Result.

My Requirement is to get the value from One of the column from one of the
Sheet and use that values to get a Joined Record from TWO tables of SQL
Server.

Ex: -

Sheet2$ : Having Column "EmployeeID" with 100 rows.

IN SQL Server I have 2 Tables. 1) Employee 2) Dept.

I want to export the LIST of the Departments for the Employee that are in
the Excel Sheet2.

Please Suggest how can I do that or any Better solution using DTS.

Thanks
Prabhat

Re: DTS for Import Export TO And From EXCEL

Allan Mitchell
5/19/2005 6:58:30 PM

You could use OPENDATASOURCE

SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\Finance\account.xls";User ID=Admin;Password=;Extended
properties=Excel 5.0')...xactions

Or you can create a linked server of the source XL spreadsheet from the
SQL Server. You then query that and export to XL destination.

You cannot use the Excel connections to do this ..........Yet.

Allan

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server dts group.