Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

DTS - result to excel Expand / Collapse
Author
Message
Posted Monday, March 4, 2013 7:15 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, November 26, 2013 12:26 AM
Points: 47, Visits: 180
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
Posted Tuesday, March 5, 2013 7:19 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, April 27, 2014 6:26 AM
Points: 1,521, Visits: 3,036
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
Posted Wednesday, March 6, 2013 2:22 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, November 26, 2013 12:26 AM
Points: 47, Visits: 180
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
Posted Wednesday, March 6, 2013 6:15 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, April 27, 2014 6:26 AM
Points: 1,521, Visits: 3,036
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 »

Add to briefcase

Permissions Expand / Collapse