Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
theboyholty
Posting Yak Master
226 Posts |
 Posted - 2011-09-28 : 10:34:53
|
| Using SSIS, I want to be able to run a SQL query then export the resulting data to excel and save it to a specific folder with the date in the filename, i.e Selection_20110928.xlsI have set up a variable which will give me the filename including the date but if I use an Excel Destination in the data flow, it won't allow me to use the variable as the destination path.If I use a File System Task, I can use the variable as a destination but can't get the sql query to populate the excel file with the data.There must be a way to do it, can anyone help?---------------------------------------------------------------------------------http://www.mannyroadend.co.uk A Bury FC supporters website and forum |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-09-28 : 11:22:47
|
| you can use excel destination itself. You can use expression builder to map the connection string to variable so that it takes filename from variable------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
theboyholty
Posting Yak Master
226 Posts |
Posted - 2011-09-28 : 12:00:29
|
| When I try to do that it fails because the file doesn't exist. So it seems the file needs to be created before it will work, but that's no good. Can I not get it to build a brand new excel file as part of the process?---------------------------------------------------------------------------------http://www.mannyroadend.co.uk A Bury FC supporters website and forum |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-09-28 : 12:03:54
|
| you just need to map to a local xls file at first. then at runtime it will take care of itself using value from the variable------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
theboyholty
Posting Yak Master
226 Posts |
Posted - 2011-09-28 : 12:15:53
|
| Thanks. I really apreciate your help here.I've mapped it to a local file using the Excel File Path dialog box, and in the properties > expressions on the Excel Connections manager I've used my variable, but as soon as I save it, the local file path resets itself to the name of the new file and the package fails to validate because the file doesn't exist.---------------------------------------------------------------------------------http://www.mannyroadend.co.uk A Bury FC supporters website and forum |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-09-28 : 12:35:00
|
| nope. put default value of variable as path of local file. While running prior to excel population step put a task to populate the variable with correct value------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
Run SQL Query, export to Excel with yyyymmdd