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 |
|
-Dman100-
Posting Yak Master
210 Posts |
 Posted - 2006-06-04 : 20:25:01
|
| I've created a dts package that exports data to an excel. The data exports okay, but I'd like to make a few changes on how the data is exported. First, when the data exports to the excel file, it always creates a new excel sheet. How can I map the data to the existing sheet in the Excel file? I have an existing sheet named 'Sheet1'. I've deleted the other two sheets in the excel file, so there is only one sheet. When I create the destination table in the dts package for the data transformation task, I specified the name as 'Sheet1', but when it exports, it creates a new sheet named 'Sheet11'. So, there ends up being two sheets in the excel file with the original one empty and the second sheet which is created and populated with the data. I haven't been able to correct this.Second, I'd like to either ammend data to the last row in the excel file, which I'd have to know the last record that was inserted from the SQL table. Or probably much easier, simply drop the existing sheet in the excel file and simply re-create and insert.How can I do this?Thank you. |
|
|
-Dman100-
Posting Yak Master
210 Posts |
Posted - 2006-06-05 : 00:23:55
|
| I figured it out...please disregard.Thank you. |
 |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
-Dman100-
Posting Yak Master
210 Posts |
Posted - 2006-06-05 : 12:59:30
|
| Thanks for the additional information.I just discovered a strange behavior everytime I run my dts package or this might be a normal behavior that I'm not aware of. Either way, the drop/create new table causes the previous rows that were inserted in the excel file to be left blank.For example, if 10 rows are inserted, then next time the package runs, the first 10 rows in the excel file are blank and the insert begins at the 11th row. The column headings are on the very first row. Then there are a blank rows and the the insert will start at the row where the last record left off. So, the number of blank rows keeps increasing each time the package runs.It's like it doesn't re-seed and start at the top of the spreadsheet when the package is run again?The steps in my dts package are as follows:drop/create new table ('Execute SQL Task on my excel connection) export data from SQL to excel file.I can't figure out what is causing the blank rows. |
|
|
|
|
|
|
|
|
export data to excel file