Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
thanksfor help
Posting Yak Master
106 Posts |
 Posted - 2008-09-16 : 11:50:27
|
| Hi,I have upload excel 2007 file to tables in DB. Earlier I was using excel 2003 and it worked fine. I don't know which option to select for uploading excel 2007 file through DTS package.Thanks for any suggestions. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-09-16 : 11:58:50
|
| Did you try using excel connection manager available in DTS? |
 |
|
|
thanksfor help
Posting Yak Master
106 Posts |
Posted - 2008-09-16 : 12:05:58
|
| Yes, it worked for Microsoft excel 97-2000, but for excel 2007 do I need to use ODBC driver, if so what is correct procedure for that.Thanks for your quick reply. |
|
|
|
notes4we
Yak Posting Veteran
90 Posts |
Posted - 2008-09-18 : 08:45:47
|
| Hello,I know how it works with SSIS 2005. You can check if this same method works with DTS 2000. I am not sure about it.I have been working on Excel Workbook (latest excel version) since quite some time. I am giving you a detailed explanation. Trust me. This will work for sure. And if you have any questions, feel free to post it here. I will keep on checking this forum and help you as much as I can.Excel 97-2003 files have .xls extension. Excel 2007 files have .xlsx extension. In SSIS 2005, if we wish to use Excel 2007, then we cannot select Excel as our source. 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.Hope this helps. Good Luck.- notes4we |
|
|
|
thenappann
Starting Member
4 Posts |
Posted - 2010-05-03 : 19:40:41
|
| Hi notes4we, I have tried your method and i am getting an error at the 65000 row.My scenario : I am trying to copy a table from SQL Server 2005 to Excel 2007 using DTS. For smaller files i have no problems but when i am trying to drop a table in excel and create the heading rows and then push values from SQL Server to excel 2007 it fails at 65000 mark, pls let me know if i am missing some settingsPls Note : If i try to manually delete the existing rows in the excel and then run the DTS it works perfectly but my scenario is to automate the drop and recreate of the excel 2007 xlsx file so that i do not have to do it manually every time.Thanks in AdvanceThanks,TN |
|
|
|
|
|
|
|
|
excel 2007 upload by DTS