SSIS Send email to different recipients with different data from SQL Table
up vote
0
down vote
favorite
I am trying to create an SSIS Package that sends out emails to different recipients and includes some of the data as a table that is either included in the body of the email or attached as an Excel file.
I have three tables, one that includes the email addresses, one that includes the error type and the main table that contains customer information like name, old address info, and new address info all broken out into about 16 different columns, of which are codes to pull from the two other tables to determine which person to email to update the address and a code description on why they need to update it.
I have created a script task to combine the data and pass it to a foreach loop container.
I'm not sure how to take the customer info, old address and new address columns of data from what I've pulled together in the sql task (CIF_Nbr to Current_XIPplus4) and create a table from it that can be inserted into the body or added as an attachment to the send mail task.
A branch could have multiple lines of data so I would prefer one attachment for all same branches sent only once to that branch.
My three tables have the following columns:
I'm using Microsoft Visual Studio 2008 and SQL server 2008 if that helps. I'm pretty much stuck with the default toolbox.
Hopefully this all makes sense. I'm fairly new to visual studio so the more detail the better. Thanks.
sql-server ssis edited Nov 13 at 5:18
kenlukas
1,2221217
asked Nov 12 at 22:09
Shaun Rezac
1
add a comment |
up vote
0
down vote
favorite
I am trying to create an SSIS Package that sends out emails to different recipients and includes some of the data as a table that is either included in the body of the email or attached as an Excel file.
I have three tables, one that includes the email addresses, one that includes the error type and the main table that contains customer information like name, old address info, and new address info all broken out into about 16 different columns, of which are codes to pull from the two other tables to determine which person to email to update the address and a code description on why they need to update it.
I have created a script task to combine the data and pass it to a foreach loop container.
I'm not sure how to take the customer info, old address and new address columns of data from what I've pulled together in the sql task (CIF_Nbr to Current_XIPplus4) and create a table from it that can be inserted into the body or added as an attachment to the send mail task.
A branch could have multiple lines of data so I would prefer one attachment for all same branches sent only once to that branch.
My three tables have the following columns:
I'm using Microsoft Visual Studio 2008 and SQL server 2008 if that helps. I'm pretty much stuck with the default toolbox.
Hopefully this all makes sense. I'm fairly new to visual studio so the more detail the better. Thanks.
sql-server ssis edited Nov 13 at 5:18
kenlukas
1,2221217
asked Nov 12 at 22:09
Shaun Rezac
1
Please show your table schema as a code snippet and not as screenshots.
– jtate
Nov 12 at 22:14
Here's one example of sending data in an email dba.stackexchange.com/questions/35263/… You can do what you want by repeatedly calling msdb.dbo.sp_send_dbmail. You don't even need SSIS for this.
– Nick.McDermaid
Nov 13 at 11:12
add a comment |
up vote
0
down vote
favorite
up vote
0
down vote
favorite
I am trying to create an SSIS Package that sends out emails to different recipients and includes some of the data as a table that is either included in the body of the email or attached as an Excel file.
I have three tables, one that includes the email addresses, one that includes the error type and the main table that contains customer information like name, old address info, and new address info all broken out into about 16 different columns, of which are codes to pull from the two other tables to determine which person to email to update the address and a code description on why they need to update it.
I have created a script task to combine the data and pass it to a foreach loop container.
I'm not sure how to take the customer info, old address and new address columns of data from what I've pulled together in the sql task (CIF_Nbr to Current_XIPplus4) and create a table from it that can be inserted into the body or added as an attachment to the send mail task.
A branch could have multiple lines of data so I would prefer one attachment for all same branches sent only once to that branch.
My three tables have the following columns:
I'm using Microsoft Visual Studio 2008 and SQL server 2008 if that helps. I'm pretty much stuck with the default toolbox.
Hopefully this all makes sense. I'm fairly new to visual studio so the more detail the better. Thanks.
sql-server ssis edited Nov 13 at 5:18
kenlukas
1,2221217
asked Nov 12 at 22:09
Shaun Rezac
1
I am trying to create an SSIS Package that sends out emails to different recipients and includes some of the data as a table that is either included in the body of the email or attached as an Excel file.
I have three tables, one that includes the email addresses, one that includes the error type and the main table that contains customer information like name, old address info, and new address info all broken out into about 16 different columns, of which are codes to pull from the two other tables to determine which person to email to update the address and a code description on why they need to update it.
I have created a script task to combine the data and pass it to a foreach loop container.
I'm not sure how to take the customer info, old address and new address columns of data from what I've pulled together in the sql task (CIF_Nbr to Current_XIPplus4) and create a table from it that can be inserted into the body or added as an attachment to the send mail task.
A branch could have multiple lines of data so I would prefer one attachment for all same branches sent only once to that branch.
My three tables have the following columns:
I'm using Microsoft Visual Studio 2008 and SQL server 2008 if that helps. I'm pretty much stuck with the default toolbox.
Hopefully this all makes sense. I'm fairly new to visual studio so the more detail the better. Thanks.
sql-server ssis sql-server ssis edited Nov 13 at 5:18
kenlukas
1,2221217
asked Nov 12 at 22:09
Shaun Rezac
1
edited Nov 13 at 5:18
kenlukas
1,2221217
asked Nov 12 at 22:09
Shaun Rezac
1
edited Nov 13 at 5:18
kenlukas
1,2221217
edited Nov 13 at 5:18
kenlukas
1,2221217
edited Nov 13 at 5:18
kenlukas
1,2221217
1,2221217
asked Nov 12 at 22:09
Shaun Rezac
1
asked Nov 12 at 22:09
Shaun Rezac
1
asked Nov 12 at 22:09
Shaun Rezac
1
1
Please show your table schema as a code snippet and not as screenshots.
– jtate
Nov 12 at 22:14
Here's one example of sending data in an email dba.stackexchange.com/questions/35263/… You can do what you want by repeatedly calling msdb.dbo.sp_send_dbmail. You don't even need SSIS for this.
– Nick.McDermaid
Nov 13 at 11:12
add a comment |
Please show your table schema as a code snippet and not as screenshots.
– jtate
Nov 12 at 22:14
Here's one example of sending data in an email dba.stackexchange.com/questions/35263/… You can do what you want by repeatedly calling msdb.dbo.sp_send_dbmail. You don't even need SSIS for this.
– Nick.McDermaid
Nov 13 at 11:12
Please show your table schema as a code snippet and not as screenshots.
– jtate
Nov 12 at 22:14
Please show your table schema as a code snippet and not as screenshots.
– jtate
Nov 12 at 22:14
Here's one example of sending data in an email dba.stackexchange.com/questions/35263/… You can do what you want by repeatedly calling msdb.dbo.sp_send_dbmail. You don't even need SSIS for this.
– Nick.McDermaid
Nov 13 at 11:12
Here's one example of sending data in an email dba.stackexchange.com/questions/35263/… You can do what you want by repeatedly calling msdb.dbo.sp_send_dbmail. You don't even need SSIS for this.
– Nick.McDermaid
Nov 13 at 11:12
add a comment |
active
oldest
votes
active
oldest
votes
active
oldest
votes
active
oldest
votes
active
oldest
votes
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53270820%2fssis-send-email-to-different-recipients-with-different-data-from-sql-table%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Please show your table schema as a code snippet and not as screenshots.
– jtate
Nov 12 at 22:14
Here's one example of sending data in an email dba.stackexchange.com/questions/35263/… You can do what you want by repeatedly calling msdb.dbo.sp_send_dbmail. You don't even need SSIS for this.
– Nick.McDermaid
Nov 13 at 11:12