March 4, 2013 at 7:15 am
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
March 5, 2013 at 7:19 pm
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
March 6, 2013 at 2:22 am
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.
March 6, 2013 at 6:15 pm
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")?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply