mjs082969
asked on
Scheduled SSIS Package - Managing the Excel Output File
I have a simple SSIS Package (Sql Server 2005) that exports information from a view into an Excel spreadsheet. The package will be scheduled to execute daily.
The job currently runs fine. My challenge is that I am unsure as to build the functionality necessary to allow for subsequent runs. In particular:
1) How to clear the previous execution's information from the Excel spreadsheet.
2) How to rename the Excel file so as to include the current data.
The Excel file will be generated daily and transmitted to another organization.
Thanks In Advance,
- Michael
The job currently runs fine. My challenge is that I am unsure as to build the functionality necessary to allow for subsequent runs. In particular:
1) How to clear the previous execution's information from the Excel spreadsheet.
2) How to rename the Excel file so as to include the current data.
The Excel file will be generated daily and transmitted to another organization.
Thanks In Advance,
- Michael
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I followed the direction of the examples in vdri's examples.
I used a File System task to make a new copy of the Excel spreadsheet from a "template" file. And one of his examples provided a decent example of how to use variables and expressions in order to have dynamic file names.
I have learned some things through this process. If someone is reading this because they are going down the same path, I will recommend that you also familiarize yourself with the DelayValidation properties... until I learned about this I was receiving many errors, as my package was attempting to resolve connections to files that did not exist yet.
Thank you both for your help.
I used a File System task to make a new copy of the Excel spreadsheet from a "template" file. And one of his examples provided a decent example of how to use variables and expressions in order to have dynamic file names.
I have learned some things through this process. If someone is reading this because they are going down the same path, I will recommend that you also familiarize yourself with the DelayValidation properties... until I learned about this I was receiving many errors, as my package was attempting to resolve connections to files that did not exist yet.
Thank you both for your help.
Yes, Delay Validation is an important property to consider especially when you are dealing with Staging Tables or File connections in the SSIS package
I suggest you delete the EXCEL document by using xp_cmdshell and OS del command
"2) How to rename the Excel file so as to include the current data."
Same as above you coudl use OS ren command via xp_cmdshell from SQL code inside SSIS.
http://msdn.microsoft.com/en-us/library/ms175046.aspx