April 19, 2003 at 10:19 am
I have created a Package in SQL server 2000 that exports a table to Excel. I need to delete the rows in the spreadsheet prior to each export. I have seen hints that this is possible, but haven't found a detailed description on any methodology.
April 21, 2003 at 4:08 am
I have not seen a way to DELETE the data. However you can DROP TABLE sheetname as a SQL task then do create as another then run the push this will clear just fine.
If you need the create statment just open the transformation to the Excel file in question and on the Destination tab press the create button to get.
April 21, 2003 at 1:17 pm
I generally attach my spreadsheet as a linked server. Then you can truncate, insert, select, etc...
I have a proc that I pass the unc filename to, the alias to call the linked server, and away it goes......the main consideration is that the login used by the sql server have access to wherever the file is located, and it's really little more than the two system procs sp_addlinkedserver and sp_addlinkedsrvlogin.
April 21, 2003 at 10:15 pm
The post from Antares686 did the trick. I'm sure I tried this last night and couldn't get it to work.
Thanks.
April 26, 2003 at 9:25 am
I have built a package that creates the Excel spreadsheet from the Customers table in Northwinds. I would like to then attach this spreadsheet to an email notification.
The Package is very simple, it Drops the spreadsheet (so that the data will be refreshed with each load), it then Builds the spreadsheet, connects to SQL Server, selects * from Customers and transforms the result to the Excel connection task. The last task is the Send Mail Task which includes the spreadsheet as an attachment.
The Send Mail Task executes successfully when I execute it directly, but when I run the entire Package the Send Mail task fails with an error message - 'Error sending mail: MAPI error: Could not open attached file'.
Once I select Done after the Package execution and Send Mail failure, I can successfully execute the Send Mail task individually. I am suspecting that the issue is around the release of the spreadsheet by the Excel connection after the spreadsheet is loaded because I get a very similiar error if I execute the Send Mail task while I have the spreadsheet open.
Any suggestions?
May 6, 2003 at 1:38 am
I have a procedure that runs correctly at my system,
I have done this:
1. Command task: delete the old Excel File
2. SQL Execute task: CREATE TAB on a Excel Connection
CREATE TABLE `tbl_LFCM_Report` (
`Type` Long ,
`Id` Long ,
`DEV_Name` VarChar (30) ,
`DEV_Version` VarChar (80) )
When file does not exist, it creates an Excel file with the name and path specified in the Excel Connection.
3. Data Transformation task: copy columns from SQL table to new Excel file.
4. Send mail task
This works fine, on SQL Server 2000 SP3
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply