scheduled SSIS package creating excel file with date as filename

Question

Hi,

I need some feedback on what is the best way to do what I want to do, allow me to situate.

I have a huge database which receives new inputs constantly. Now I need to have an excel file which shows all the columns of inputs of the past day. So I thought the best way to do this is to use SSIS packages. I never really used SSIS that much so I'm not very sure how wide the possibilities are. So at the moment I'm only capable of creating an SSIS package of ALL my inputs. But I need a new file every day, but also where the filename has the data in it, because the old files will not be deleted etc. So I need to be able to create an .xls file with the date in the filename. Is there an easy way to do this?

I was also wondering if it's possible to automaticly modify the layout for this excel file, especially the column widths, since they are all the default width, which is not very usefull for clients.

I heard it's also possible to create SSIS packages with visual studio, would this make a difference or are the possibilities the same (Keep in mind that I'm a c# programmer)?

Thanks a lot

Answer 1

It is possible to create SSIS packages through Visual Studio. When click on new project in Visual Studio, select Business Intelligence Projects->Integration Services Project.

Though different from your situation (i.e., Excel File output), in my situation, for a flat .TXT file output, I had the date as part of flat file name. For this, in my Flat File Connection Manager, in Properties-Expressions->Connection String (Property), I put in the following expression. Similar approach could work in your case as well, specially if you go for a .csv file output.

"[Folder Destination]_" + 
(DT_WSTR,4)YEAR(GETDATE()) + 
RIGHT("0" + DT_WSTR,2)MONTH(GETDATE()),2) +
RIGHT("0" + (DT_WSTR,2)DAY(GETDATE()),2) + 
RIGHT("0" + (DT_WSTR,2)DATEPART("hh", GETDATE()),2) + ".TXT"