April 22, 2010 at 12:37 pm
I have a package that pulls the results from a query and puts them into an Excel file. I would like to set the file name to the current date and time. I have been able to set the path and create the file name (I think) but I get an error. It looks to me that it doesn't have a sheet to add it to. I have looked at several articles but can't seem to find what I need.
How do I send the results to Excel and set the name of the file dynamically?
Error: 0xC02020E8 at Data Flow Task, Excel Destination [151]: Opening a rowset for "sheet" failed. Check that the object exists in the database.
April 22, 2010 at 12:55 pm
Assuming you are creating a new sheet from scrath then the default worksheet would be sheet1
the sheet name provided in the excel destination must match one that is in the file.
when I had to do this I tend to create a master file and then you a file system task to copy the file to the new name. this not only allows me to avoid problem with metadata but also allows me to name the sheet whatever I want and use that as part of the process.
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
April 22, 2010 at 1:34 pm
Let me see if I am following you:
Create an empty excel file (Template)
Job
Copy empty template to folder using the name that is built dynamically
Insert data using the name that was built dynamically into the copied template
The name that is built dynamically has minute in it. Do I stand a chance of it being different from the copied template?
April 22, 2010 at 1:53 pm
Yes that is roughly the idea. I define a blank spreadsheet give it pretty headers call the worksheet something useful. remove the extra worksheets that I don't need. Change cell formating. For example if I am going to export a dollar amount I define the cell as currency.
One of the first steps of the SSIS is to copy the excel master to the work excel file.
I usually give it a name from a variable that is populated with date time stamp whatever is needed.
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
April 22, 2010 at 2:41 pm
This is working great thank you very much for your help.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply