export data to dynamically created excel 2007 file

Question

export data to dynamically created excel 2007 file

boaz.goldstein

SSC Enthusiast

Points: 103
More actions
February 17, 2009 at 11:00 am

#129126

Hi,
method for this task to accomplished is known with excel 2003 files, but is there anybody who managed to get by such a task with excel 2007 files using OLE DB connection?
Thanks.
boaz

Viewing 4 posts - 1 through 4 (of 4 total)

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

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

You must use a new ODBC driver for either import or export

SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=d:\dasdgigs.xls', 'SELECT * FROM [dasdgigs$]')

Download site for the new driver is:

http://www.microsoft.com/downloads/details.aspx?FamilyID=7554F536-8C28-4598-9B72-EF94E038C891&displaylang=en

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

boaz.goldstein SSC Enthusiast Points: 103 More actions · Answer 2

Hi Bitbucket,

Thanks for the help,

as i am using SSIS i am not specifically writing the OpenRowset -do you have any example for creating the dynamic connection to excel 2007 file through script or through expression?

thanks,

boaz

Ron McCullough SSC Guru Points: 63877 More actions · Answer 3

From this SSC article:

http://www.sqlservercentral.com/articles/SSIS/64585/

1. Drag and drop OLE DB Source data flow source to the data flow task.
2. Double click the OLE DB Source and click New button for OLE DB Connection Manager.
3. Click New button in the Configure OLE DB Connection Manager screen.
4. Select Native OLE DB\Microsoft Office 12.0 Access Database Engine OLE DB Provider from the OLE DB Provider list.
5. Select All option and at the Extended Properties enter Excel 12.0.

Although this article is for importing Excel data I believe if you follow it, it you will generate the proper connection string.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]