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!