asked
Creating and attaching Excel File as Attachment In SSIS
Hi All, I have a requirement in SSIS where I have to generate a excel file based on a query output and send this excel file as attachment to a particular person. After the package is over. I have to schedule this package so that it runs UN-attended. means I want everything to be dynamic. Kindly suggest me a workaround to this problem. Thanks
You can do all of this within SSIS without too much hassle assuming that the Excel file will always be the same name, meaning you'll truncate it and reload it every run of the package. If the Excel file name is going to be different, you can probably utilize a variable to dynamically provide the name within the package. Within the data flow task, you can specify an OLE DB source where you would specify the query and an Excel Destination thus putting the output of your query to your Excel file. You can then utilize the 'Send Mail' task at the control flow level which you can specify to add an attachment, which you would select the above Excel file. If the recipient of the email is different every time, you can use a variable to dynamically change the recipient. You can schedule a SQL Server agent job to execute this package on any schedule that fits your business needs. Hope this helps!
I was able to find this with a google search:
http://www.pertell.com/sqlservings/archive/2011/03/error-using-variable-for-worksheet-name-in-ssis-excel-destination/ It explains how to utilize a variable in conjunction with a dynamic naming convention for the file name. I'm not sure that it's exactly what you are looking for your situation, but it should point you in the right direction. Hope this help!
@CoolDbGuy As long as you are using expression to have a dynamic file name, it does not matter. You need to let SSIS know the structure of the Excel file and what exactly the information to be extracted. This is where you had to point a starting excel file.
4 People are following this question.
