November 22, 2010 at 2:01 pm
Hello,
I need to export weekly sales data into excel sheet every week in a dynamic way and I am using variable and expression. The file name should also be dynamic like Sales1115_to_1121.xls, Sales1122_to_1128.xls. However, it gives me a fatal error, but works fine while exporting to flat file destination(csv and text file). Am I missing something or it is a bug?
Please see the attachment and suggest me.
Thank you in advance.
Ganesh
December 3, 2010 at 9:51 am
Hi Ganesh,
Are you giving path of files kept on Network location? If so, try getting those file on local machine using FTP task/any other way based on your server location and evaluate path of local machine in connection string and it should work...!
:rolleyes:
December 3, 2010 at 10:05 am
Hi,
I have been trying since last week but no success.
Simply, I wanted to create a dynamic excel file name for weekly sales data,
For example: SalesData1101_to_1107.xls, SalesData1108_to_1114.xls etc. Everything works fine if I use .txt or .csv format but does not work for .xls format. The user wants weekly sales sheet(excel) in his secured ftp server. If you know any example or link, that would be great.
Thanks
December 3, 2010 at 3:24 pm
Hi Ganesh,
I had to do something similar a few months ago (i.e. writing to MS Excel files that I needed to copy to our network with dated file names). From memory, this is what worked for me:
1) Set the destination in your data flow task to a local copy of the Excel workbook.
2) Create a Script Task in the control flow to build the path and dynamic file name and store it in an SSIS variable.
3) If you're writing to your network, use this variable in a control flow File System Task to copy your local file.
or...
3) If you need to FTP the file, then use this variable in a control flow FTP Task to transfer your local file.
It may not be best-practice (or the most elegant way to do it) but it'll break your problem into manageable components where you can begin to isolate the cause of your troubles. If you hit problems in step 3) then try to write the file with your dynamic name locally to rule out the problem being caused by steps 1) and 2).
Good luck!
RF
_____________________________________________________________
MAXIM 106:
"To know things well, we must know the details; and as they are almost infinite, our knowledge is always superficial and imperfect."
Francois De La Rochefoucauld (1613-1680)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply