DTS - result to excel

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » T-SQL » DTS - result to excel

DTS - result to excel

Rate Topic

Display Mode

Topic Options

Author

Message

shani19831

Posted Monday, March 04, 2013 7:15 AM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, May 23, 2013 2:26 AM
Points: 37, Visits: 126

Hi Guys

I have created several DTS packages, and the data gets stored in csv flat file and get mailed automatically.

Now i wanted to created the same but this time save the data in excel file but this is the error i receive when try to choose destination file:

Microsoft database engine external table is not in the expected format
I have searched the web but couldn't find a satisfactory answer.

Do i need to have office installed in my sql server box?

Thank you

Post #1426233

john.arnott

Posted Tuesday, March 05, 2013 7:19 PM

UDP Broadcaster

Group: General Forum Members
Last Login: Wednesday, April 17, 2013 10:57 PM
Points: 1,491, Visits: 3,008

Without all the details, it's hard to say anything definitively, but you're on the right track asking if Office has to be on the server. The server needs the proper provider software and if you had Office on the server, it would be there, but that's not necessary. You can instead install the Office 12.0 engine or (for older Excel) the "Jet" provider (see link below).

If it is an xlsx (Excel 2007) file, you're NOT connecting to the type of Excel that SQL 2000 knows about, and you need to establish an OLEDB connection. The "Server" connection has an optional drop-down from which you can select a new connect of "Microsoft Office 12.0 Access Database Engine OLE DB Provider". If you have Office on your machine, you'll see this, but you also have to make sure the provider is on the server where the package will run. Configure it under the "Properties" button with the file name in the Connection tab and
Extended Properties on the "All" tab as "Excel 12.0".

Take a look at this MSDN article. Even thought its domain is SQL 2008, the concepts still hold -- you need the proper provider. http://msdn.microsoft.com/en-us/library/cc280527(v=sql.105).aspx

Post #1427122

shani19831

Posted Wednesday, March 06, 2013 2:22 AM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, May 23, 2013 2:26 AM
Points: 37, Visits: 126

Hi

this is what im trying to do;

I have a sql server as follow
Windows server 2003
SQL server instance 2000
32 bit i presume

i open import and export data --> on destination i choose excel 97-2000 and i locate the excel file, just when i hit next this error comes up

Error Source: Microsoft JET Database engine
Error Description: External table is not in the expected format.

I'm knocked out.

Post #1427248

john.arnott

Posted Wednesday, March 06, 2013 6:15 PM

UDP Broadcaster

Group: General Forum Members
Last Login: Wednesday, April 17, 2013 10:57 PM
Points: 1,491, Visits: 3,008

You say you choose Excel 97-2000 and locate the file. Does that mean the file already exists? If so, is it in the 97-2000 format (I mean, is its extension "xls" and not "xlsx")?

Post #1427715

« Prev Topic | Next Topic »

Permissions