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

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

"Prabhat" <not_a_mail (AT) hotmail (DOT) com> wrote

306397 How To Use Excel with SQL Server Linked Servers and Distributed
Queries
http://support.microsoft.com/?id=306397

-Doug
--
Douglas Laudenschlager
Microsoft SQL Server documentation team
Redmond, Washington, USA

This posting is provided "AS IS" with no warranties, and confers no rights.

"Prabhat" <not_a_mail (AT) hotmail (DOT) com> wrote

"Douglas Laudenschlager [MS]" <douglasl (AT) online (DOT) microsoft.com> wrote in
message news:OOnZmB$XFHA.2884 (AT) tk2msftngp13 (DOT) phx.gbl...