May 26, 2009 at 3:43 pm
This seems like it should be quite simple, but I cannot seem to find a way to create an Excel file dynamically. All I'm trying to do is execute a SPROC and dump the results to an Excel file with the date in the filename (EX. DataDump_20090526.xls). How do you create an Excel Destination to a file that doesn't exist yet?
TIA
May 26, 2009 at 4:20 pm
have you tried using SSIS or the export data wizard. These will craete the neccessary tabs in Excel for you
Gethyn Elliswww.gethynellis.com
May 26, 2009 at 5:31 pm
If this is something that you are running regularly, the export wizard doesn't give you what you want. I suspect that the package validation is giving you problems.
One possible way of achieving what you want without falling foul of the advance validation is to use an existing Excel file name for the export and then to have a dynamic rename or copy task afterwards that names the file as you want it.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
May 27, 2009 at 7:35 am
Hi,
First create excel file and make connection and then change property of "Excel Connection Manager" DelayValidation to True.
May 27, 2009 at 9:31 pm
zbleslav (5/27/2009)
Hi,First create excel file and make connection and then change property of "Excel Connection Manager" DelayValidation to True.
How does this accommodate the dynamic file-name requirement?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
May 29, 2009 at 5:37 am
Phil Parkin (5/27/2009)
zbleslav (5/27/2009)
Hi,First create excel file and make connection and then change property of "Excel Connection Manager" DelayValidation to True.
How does this accommodate the dynamic file-name requirement?
Question was:
"How do you create an Excel Destination to a file that doesn't exist yet?"
- create "dummy" excel file
- make connection
- change DelayValidation to True
- delete "dummy" file
"DelayValidation to True" will skip pre-execution validation and problems with connection to the file that does not yet exist.
May 29, 2009 at 6:09 am
zbleslav (5/29/2009)
Phil Parkin (5/27/2009)
zbleslav (5/27/2009)
Hi,First create excel file and make connection and then change property of "Excel Connection Manager" DelayValidation to True.
How does this accommodate the dynamic file-name requirement?
Question was:
"How do you create an Excel Destination to a file that doesn't exist yet?"
- create "dummy" excel file
- make connection
- change DelayValidation to True
- delete "dummy" file
DelayValidation to True" will skip pre-execution validation and problems with connection to the file that does not yet exist.
Now I see what you were getting at. You answered the actual question, whereas I guessed at what was behind it and answered some other stupid question :crazy:
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply