We help IT Professionals succeed at work.

Scheduled SSIS Package - Managing the Excel Output File

255 Views
Last Modified: 2012-02-14
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
Comment
Watch Question

Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
lcohanDatabase Analyst
CERTIFIED EXPERT

Commented:
"1) How to clear the previous execution's information from the Excel spreadsheet."

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

Author

Commented:
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.

Commented:
Yes, Delay Validation is an important property to consider especially when you are dealing with Staging Tables or File connections in the SSIS package
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.