Alternate to DTS

Question

Post reply

Alternate to DTS

NewBeeSQL

SSCrazy

Points: 2170
More actions
March 25, 2008 at 2:29 am

#122897

Hi,
I have 2 excel files which has to be dumped in to sql server 2000 after performing some business logic. This is already implemented using DTS.
Excel file has :
1) more than 75 columns
2) Row length for fewer columns is not predetermined.
3) Values in the columns can be of any data type.
4) Special characters
Now, customer wants to know the alternatives for dumping data for the above said excel files without using DTS.
Please suggest me the alternate approaches to accomplish the above task, advantages, disadvantages..
Thanks
Raj
-----------------------------------------------------------------------
For better assistance in answering your questions[/url]
Perforamance Issues[/url]
Cross Tabs and Pivots[/url]
Cross Apply[/url]
The Numbers or Tally Table- Jeff Moden[/url]

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Girish Bhat Ten Centuries Points: 1323 More actions · Answer 1

Within SQL Server 2000, you have two alternatives:

1. Create Linked Servers to the Excel sheets and then access them like tables.

2. Query the Excel sheets using OPENROWSET method.

With the above two methods, you can use the Excel sheets from within stored procedures and T-SQL batches. Take a look at the links:

http://support.microsoft.com/kb/306397

http://support.microsoft.com/kb/321686

NewBeeSQL SSCrazy Points: 2170 More actions · Answer 2

Thanks for the reply Girish. These options looks fine. but linked servers are not permitted to us. do you have any idea on making the excel file as XML string and passing this string to Sproc to dump data. we are using .net application.

Thanks

Raj

-----------------------------------------------------------------------
For better assistance in answering your questions[/url]
Perforamance Issues[/url]
Cross Tabs and Pivots[/url]
Cross Apply[/url]
The Numbers or Tally Table- Jeff Moden[/url]