May 19, 2010 at 7:43 am
Good Morning,
I am actually using SQL 2008, but I'm hoping this forum can still help..
I am trying to create a SSIS package that will export data returned from a query to an Excel document that will save to a folder. That sounds easy enough, and I know how to set up the Source and Excel Destination.
What I'm having trouble doing is I need this query to run hourly and save all of the versions of the Excel document separately with different names. For example, Document1_Hour1, Document1_Hour2, etc. I set up a variable using this link - http://searchsqlserver.techtarget.com/tutorial/Export-SQL-Server-data-to-an-Excel-file-using-SSIS-and-Visual-Studio.
So, essentially I want to create the package and then set up a job to run it hourly. I'm running it via a job because after the file saves to the folder the next step is to update the records. However, I'm having trouble saving every single version of the report to a folder.
My question is, how can I save every version of an Excel document (run from a SQL query) into a folder using SSIS run by a hourly job using an incremental naming scheme, preferrably by date (hour)? Whenever I try to work through it, the SSIS always asks for a file name in the folder. I want it to create the file, not add to an already existing file.
If this can't be done via SSIS, are there any other ways?
Thanks in advance,
Matt
May 19, 2010 at 9:15 am
The best way to do that is too create a batch file that will create a default file from a template file. Then after the file is loaded you can use a file system task to reanme the file and place in any folder you would like.
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
May 19, 2010 at 9:30 am
Thanks for the reply.
Any documentation (links) on how to create the batch file? I've never done that. I searched Google, but that wasn't much help. Also, the script (or report) I want to save is a CTE query that contains a temp table. Will the batch process work for that kind of script?
May 19, 2010 at 9:34 am
SSIS in my oppinoin has very poor to non existant options for formatting an excel document. So as such I always create a base file or template with the headers and formatting as I desire. Then at the first step I copy that template to a working file. you could also do this step with a system file task in SSIS. Just copy the file from the template name to a working file name. After you wrtie the output then you would do another copy and give it a name from a variable. I am sure there are links out there but I apologize I do not have them readily available.
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
May 19, 2010 at 2:40 pm
Thanks again for the replies....
Here's what I did (that worked) in case anyone searches on this and needs to find an answer:
First, the query I need to return as a report in a folder is a CTE query with temp tables. SSIS doesn't really like this syntax so I created a new table and a stored proc that will insert the data into the "fresh" (new) table a few times a day with a date/time stamp.
Second, I create a SSIS package using the Export Task in SSMS. Right click <Your database>, Tasks, Export Data. You have the option of creating a SSIS package in this wizard. I created it with the destination as an Excel sheet.
Third, I went to the SSIS which already has the data flow task set up. Make any adjustments necessary to make it work. Once it works, I then added a File System Task option and created two variables.
I followed the directions on these three links and combined the information on them:
http://www.sql-server-performance.com/articles/biz/file_system_task_ssis_p1.aspx
http://zulfiqar.typepad.com/zulfiqars_web/2006/11/ssis_dynamic_fi.html
When I run the SSIS package now, it saves a copy of the information in a distinct Excel file, which allows me to keep the history (which we will eventually share the folder with a customer).
Hope this helps any future issues anyone has....
-Matt
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply