SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


DTS - result to excel


DTS - result to excel

Author
Message
shani19831
shani19831
Old Hand
Old Hand (346 reputation)Old Hand (346 reputation)Old Hand (346 reputation)Old Hand (346 reputation)Old Hand (346 reputation)Old Hand (346 reputation)Old Hand (346 reputation)Old Hand (346 reputation)

Group: General Forum Members
Points: 346 Visits: 231
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
john.arnott
john.arnott
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3594 Visits: 3059
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
shani19831
shani19831
Old Hand
Old Hand (346 reputation)Old Hand (346 reputation)Old Hand (346 reputation)Old Hand (346 reputation)Old Hand (346 reputation)Old Hand (346 reputation)Old Hand (346 reputation)Old Hand (346 reputation)

Group: General Forum Members
Points: 346 Visits: 231
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.
john.arnott
john.arnott
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3594 Visits: 3059
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")?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search