June 25, 2009 at 8:08 am
Hello,
I hope I am in the right forum. I have Sql Server 2000 and I have been exporting to Excel 2003 for a long time using DTS. I installed Office 2007 on this server in the hopes it would simply add the appropriate drivers to export to Excel 2007 in the Destination portion of the export wizard. However it did not appear to do so, or I am doing something wrong.
In the Destination drop down, I clearly see Excel 97-2000, which works well for exporting to Excel 2003. But there is no entry for the new Excel 2007. After selecting Excel 97-2000, all I have to do is create a path to save the file and give it a name, then finish up with the wizard. I want to do the same thing, but using Excel 2007.
Can anyone help me with this?
Thanks
June 25, 2009 at 9:49 am
Hope this might help.... Excel 2007 uses a different dll. I have used it in OPENROWSET to export data.. Can you search your export wizard for:
Microsoft.ACE.OLEDB.12.0
Also you may find this reference useful
June 25, 2009 at 10:51 am
Thank you. I looked and did not find "Microsoft.ACE.OLEDB.12.0". I do see "Microsoft Office 12.0 Access Database Engine OLE DB Provider". And I did read the article that said to use this one. It says to go into the properties of it and enter Excel 12.0 XML to the Extended Properties. I tried that but I got an error that said "Persist Security Info (Not Supported)".
June 25, 2009 at 1:58 pm
If you have not yet discovered the reason for the reported error,
can you paste your connection string, and the exact error message and error number and I will attempt to assist you further.
June 25, 2009 at 3:39 pm
I appreciate that, but I don't have a connection string to give you. I am never able to complete the configuration to begin with. As for the error, it's simply "Persist Security Info (Not Supported)". I get this when trying to create the connection using "Microsoft Office 12.0 Access Database Engine OLE DB Provider" and setting the Extended Properties to Excel 12.0 XML, like the article suggested.
Another thing that puzzles me is, the article you mentioned, and others I read, say to create the connection using the "Microsoft Office 12.0 Access Database Engine OLE DB Provider" in the DATA SOURCE field on the DATA SOURCE screen.
I don't understand that! SQL Server is the data source. I am attempting to export data out of an SQL Server database and to an Excel sheet. So the connection settings should take place in the Destination screen, not the Data Source screen. Right? The Data Source field should be OLE DB for Microsoft SQL Server..........
However, when I attempt to use the "Microsoft Office 12.0 Access Database Engine OLE DB Provider" in the Destination screen (instead of the typical Excel 95-2000 I always use) I get the same error as I get when doing this in the Data Source screen.
June 26, 2009 at 8:54 pm
defiantclass (6/25/2009)
I appreciate that, but I don't have a connection string to give you. I am never able to complete the configuration to begin with. As for the error, it's simply "Persist Security Info (Not Supported)". I get this when trying to create the connection using "Microsoft Office 12.0 Access Database Engine OLE DB Provider" and setting the Extended Properties to Excel 12.0 XML, like the article suggested.Another thing that puzzles me is, the article you mentioned, and others I read, say to create the connection using the "Microsoft Office 12.0 Access Database Engine OLE DB Provider" in the DATA SOURCE field on the DATA SOURCE screen.
I don't understand that! SQL Server is the data source. I am attempting to export data out of an SQL Server database and to an Excel sheet. So the connection settings should take place in the Destination screen, not the Data Source screen. Right? The Data Source field should be OLE DB for Microsoft SQL Server..........
However, when I attempt to use the "Microsoft Office 12.0 Access Database Engine OLE DB Provider" in the Destination screen (instead of the typical Excel 95-2000 I always use) I get the same error as I get when doing this in the Data Source screen.
In the old ODBC-speak (which MS still uses for all data connections) all connections to data, whether sources or targets are "DATA SOURCES". In short, there are no "DATA TARGETS".
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 26, 2009 at 9:15 pm
OK, two things:
1) on my list of DTS Data Sources I do have an *.XLSX driver. On you connections manager list, click "Other Connections" and then in the DATA SOURCE drop down scroll up to about the mid-point. Just below the "Microsoft Excel 97-2000" is an entry (without the XL icon) called "Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *xlsb)" on my system.
If for some reason you do not have this entry, then I assume the difference is because I have multiple versions of SQL Server installed and the later versions of SQL (or Windows) also have later versions of ODBC/MDAC/JET installed too. Ususally these are free to download and upgrade from MS. Possibly there is a specific ODBC driver for the XML-excel files that you may have to download.
2) Alternately, you can go back to trying the "Office 12.0 Access" route. This thread at another site gives a more detailed description, including using just "Excel 12.0" without the "XML".
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 29, 2009 at 9:19 am
Thank you.
I'd like to investigate the last option you suggested. I do have the .xlsx driver from the first option you mentioned, but that just isn't working for me. When I select that option, it wants me to select a preconfigured data source. The data source is what I am trying to create in the first place, so that escapes me.
In the thread you provided, he said:
We have to select OLE DB source. In the connection managers area, right click and select New OLE DB connection. Then click on New and in the Provider, select Native OLE DB\Microsoft Office 12.0 Access Database Engine OLE DB Provider. Then on the left, click on All and in the Advanced column in the Extended Properties field, type Excel 12.0. Then in the Named ConnectionString column, where there is File Name field, locate the Excel File that we wish to use. On the right most side, there is a small icon, that will allow us to select the file. Then simply right click and cut that file path and paste it under the Source column, in the Data Source field. Then on the left most bottom corner, click on Test Connection. If everything is good, then the Test Connection succeeded message will prompt. Now, go back to the OLE DB source and select the connection that you wish to use. Then you can go ahead and use Excel 2007 sheet for building the package.
"In the connection managers area": What connection manager area is he talking about? In the DTS Export Wizard, the first thing I have to do is select a data source.
I select: Microsoft Office 12.0 Access Database Engine OLE DB Provider
I am given a Properties button
I click the Properties button
I have Provider, Connection, Advanced, All tabs
The Provider tab: already is selected with Microsoft Office 12.0 Access Database Engine OLE DB Provider
The Connection tab: I have fields for Data Source: I don't understand this. The source of the data is a database in SQL Server. I'm trying to get it out of SQL Server and in to Excel 2007.
The Advanced tab: Nothing important there
The All tab: I click Extended Properties and type Excel 12.0 in the Value field
June 29, 2009 at 9:19 am
Thank you.
I'd like to investigate the last option you suggested. I do have the .xlsx driver from the first option you mentioned, but that just isn't working for me. When I select that option, it wants me to select a preconfigured data source. The data source is what I am trying to create in the first place, so that escapes me.
In the thread you provided, he said:
We have to select OLE DB source. In the connection managers area, right click and select New OLE DB connection. Then click on New and in the Provider, select Native OLE DB\Microsoft Office 12.0 Access Database Engine OLE DB Provider. Then on the left, click on All and in the Advanced column in the Extended Properties field, type Excel 12.0. Then in the Named ConnectionString column, where there is File Name field, locate the Excel File that we wish to use. On the right most side, there is a small icon, that will allow us to select the file. Then simply right click and cut that file path and paste it under the Source column, in the Data Source field. Then on the left most bottom corner, click on Test Connection. If everything is good, then the Test Connection succeeded message will prompt. Now, go back to the OLE DB source and select the connection that you wish to use. Then you can go ahead and use Excel 2007 sheet for building the package.
"In the connection managers area": What connection manager area is he talking about? In the DTS Export Wizard, the first thing I have to do is select a data source.
I select: Microsoft Office 12.0 Access Database Engine OLE DB Provider
I am given a Properties button
I click the Properties button
I have Provider, Connection, Advanced, All tabs
The Provider tab: already is selected with Microsoft Office 12.0 Access Database Engine OLE DB Provider
The Connection tab: I have fields for Data Source: I don't understand this. The source of the data is a database in SQL Server. I'm trying to get it out of SQL Server and in to Excel 2007.
The Advanced tab: Nothing important there
The All tab: I click Extended Properties and type Excel 12.0 in the Value field
Am I beginning this process in the wrong area?? I do not see a Connection Manager in SQL Server 2000. Is he talking about the Data Source (ODBC) tool in Administrative Tools? I guess I need to know that before I can move forward, as these instructions are not matching up with what I am seeing in SQL Server.
Thanks
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply