DTS SQL to Excel Replacing Old Data -- How?
-
OK, so this is probably discussed ad nauseum somewhere in a bunch of old threads, but the Forum search page isn't working for me. When I click "Next" I just get an error so I can't get to the older threads...
I have a fairly complex DTS job that I now need to have create an Excel spreadsheet from a SQL view. I have the connections set up and a Data Transform task and it worked fine the first time, but subsequent runs with different data append to the previous data on the spreadsheet.
I found an entry on MSDN that describes how to export to Excel using the Wizard and specifying "Drop and Recreate". This entry said that the "table" that was create was a named range in the sheet and that when the data was added to the spreadsheet the range was extended to include the data. So I used the wizard to create a new job that has the "Drop" and "Create" tasks, replicated that code in my job to drop and re-create the table, and it still does the same thing. When I open the XLS in Excel and select the named range it only highlights the column headings, not the exported data.
Is there a simple way to implement this?
TIA
-
I've done a DROP TABLE as the first task, then a CREATE table, then the transform. 3 tasks and that's worked.
-
That's what I'm doing and it isn't working...
-
Hmm...
I dropped the Transform and added it back in and now the job is running. Maybe there was a disconnect between the "Drop and Create" table name and the named range being used in the Transform task...
Thanks for verifying that I was on the right track.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply