Great Article Joseph!
This is even more interesting when you consider the new features in the "soon to be revamped" DTS in Yukon. I read that you will be able to use looping structures so using this types of packages you will be able to put all you data into a spreadsheet in one fell swoop.
I'm wondering whether Microsoft will release a separated module with the new DTS for download until we can actually start using it when the product is released together SQL Server 2005 like they did with other features (enhanced XML, Reporting Services). But if the change is "that big" maybe they wont be able to release separately.
Keep writing this great DTS articles... I'm looking forward to see more of them
Enjoyed your article Joseph.
FYI, the page title that appears in the window title bar is:
"Capturing The Error Description In A Stored Procedure"
"There is a limitation in the Excel driver that effects the 'DELETE' keyword. If you attempt to use it, you will receive the error message, "Deleting data in a linked table is not supported by this ISAM". Instead, we remove the Excel table entirely with the DROP TABLE 'authors' command."
That is so clever! For years, I have been using an Excel template and the FSO to overcome the delete failure. Your solution is so simple it's elegant! Bravo!
I had som similar problems with Excel-reports a while back, but my solution was to create a template of the excelfile and in the beginning of the DTS, I deleted the previous report and copied my fresh template.
exec master.dbo.xp_cmdshell 'del excelfile.xls'
exec master.dbo.xp_cmdshell 'copy excelfile_template.xls excelfile.xls'
The great thing with this option is that you can create some basic formatting on the template if your report require that.
Very nice Article!
I got an error on implementation. The first task of dropping the table, did not work for me. It says error occurred in drop table or drop index. I removed the first task and tried with the remaining tasks. the transformation task works fine but the execute ActiveX control task gives me an error saying that the 'SendUsing' has a configuration value that is invalid. I dont konw what it means. Can anybody help me???
thanks
vakula
Two thinsg to note:
1) I too had trouble with the DROP Table statement until I used the slanted quotes. I accidentally discovered that I could also use Brackets to get it to work like this:
DROP TABLE [Authors]
2) I can;t get the ActiveX code to work with the objMail.AddAttachment line. If I remove that line then the code works but when that line is there I get the error that the object doesn't support this property or method. ANy ideas?
Thanks
