October 17, 2017 at 4:35 am
Hi everyone
I have two tables in SQL: one is called dbo.sales and the other is called dbo.Sales_YTD. Both tables contain sales information for each of our customers and has one row of information for each customer. There is a field in each of the tables called Cust_ID which is unique for that particular customer. I have another table called dbo.customers which along with the Cust_ID field contains all other information about our customers. I have also now made a new table dbo.NewCustomers which shows our new customers for this year.
What I need to do is create a SSIS package that does the following:
1. Creates an Excel file to save in the file directory. The file will contain 2 tabs – one containing data for the given customer for sales and the other for sales ytd (ie the two sales tables I indicated above).
2. Loops through to do the same for all customers that are contained in the dbo.newcustomers table.
3. Sends the files to a number of email addresses – customer emails saved in another table called dbo.customercontact
I’m sure all of the above is possible but I need a good starting point to get me on my way – any good articles etc…
Any help would be much appreciated .
Thanks
BO
October 17, 2017 at 7:57 am
This should get you started.
https://www.red-gate.com/simple-talk/sql/ssis/implementing-foreach-looping-logic-in-ssis/#comments
The article doesn't show it but you can return multiple columns and store them in variables as part of the For Each Loop Container so you could get the Customer ID and email in one step.
For something like this I usually create an Excel file I use as template. I then use a file task to copy the template to my destination directory. Then load the data to that copy (which is now my customer specific file).
Not a huge fan of sending emails with attachments from SSIS (or in general) as there are all sorts of gotchas, but it can be done..
October 17, 2017 at 9:13 am
Tom_Hogan - Tuesday, October 17, 2017 7:57 AMNot a huge fan of sending emails with attachments from SSIS (or in general) as there are all sorts of gotchas, but it can be done..
Me neither. The way I do this is via an Exec SQL task which calls a stored proc.
The stored proc includes all of the logic to call msdb.dbo.sp_send_dbmail with the required parameters.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
October 17, 2017 at 1:13 pm
Thanks guys.
I’ll give the article a read and see where I get.
I’m reluctant to send as attachments myself but the boss likes the idea of the click of a button solution - don’t they always! 😉
BO
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply