December 17, 2007 at 4:14 am
Hi.
I'm having a problem creating a SSIS packages that has to copy data from a SQL source to an Excel destination and create new Excel file for each time the package run.
I've tried to use the file system task and the execute process task (here I've called a .bat file I've created, that copies the Excel file to its destination).
The problem is that for each time the task is executed the data's just added to the Excel sheet instead of creating a new Excel file with the new data.
Are you having any idea what I'm doing wrong, or what could be a solutio?
\Jan Andersen
December 17, 2007 at 4:44 am
Hi Jan,
If I I understand you correctly, you have an (empty) excel-template somewhere that you copy to the destination, and then fill with data from a SQL source.
You should be able to do this by using a FileSystemTask, and then a DataFlowTask in a SSIS package.
The FST should copy the template to the destination (watch the Overwrite destination property), the DFT should fill the Excel-file.
If you can not complete this scenario, please give some more detail on the errors you get when trying this.
Peter Rijs
BI Consultant, The Netherlands
December 17, 2007 at 5:47 am
HI Peter.
Thanks for you're fast repley.
My setup is:
1: I've copy an empy Excel file to a destination on a network drive (I've tried with both FST and DFT).
2: Then I fills it with data from a SQL server.
3: Exit.
It seems that the file is filled with data before it is being copied, and I want it to be filled with data when it has been copied.
After that and the task is set to run again a new file (I belive it is a data dump task in the DTS days) a new file has to be created for each day - not overwrite the existing one.
In the control flow I have a filesystem task that is linked to a dataflow task.
In the dataflow task I first have an OLE DB datasource, second, a Excel distination.
Am I completly woring is this setup?
Again, thanks for your fast repley - its a great help.
December 17, 2007 at 6:50 am
It might be that you use the wrong file as the excel destination or something else, I still can't figure it out with your information.
Please post me a message with you email address by clicking my profile, then I will send you an example package that you can study (as it is working in my setup :)).
Peter Rijs
BI Consultant, The Netherlands
December 27, 2007 at 1:11 pm
Do you need to setup a blank XLS to accept data as an Excel destination? I can't get it to work without doing that. If I do, I get a 'check that object exists in database' error. I would have thought that the xls would be created for you.
December 30, 2007 at 1:08 pm
if your excel file has always the same name and the same path you should check overwrite destination of the file system task
else you can use variable to change the name of excel file
i am not undertand why you use the bat file
you can use data flow task with ole db source and excel destination
if you give me more detail i can help you
thanks
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply