sql server - How do I send the result set from a query as an attachment in an email using SSIS? -


i have result set execute sql task query saved on system.object variable , send results using send mail task using following expression on messagesource.

"please find attached data summary\n\n" + substring( @[user::myvariable] ,1,3990)
+ "\n\n"

probably, have found answer question now. answer others might stumble upon question. don't think can use object variable in expression. need loop through query result object , format string can send query output in e-mail message. can export data file , send file attachment. possible option. example shows how loop through query result set form message body emailed using send email task.

step-by-step process:

  1. create table named dbo.emaildata using script provided under sql scripts section.

  2. screenshot #1 shows sample data execute sql task query , send in e-mail in example.

  3. on ssis package, create 5 variables shown in screenshot #2.

  4. on ssis package, place following tasks: execute sql task, foreach loop container, script task within foreach loop container , send email task.

  5. configure execute sql task shown in screenshots #3 , #4.

  6. configure foreach loop container shown in screenshots #5 , #6. variable mappings section shows order in query result columns appear , how assigned ssis variables. these variables used form email message inside script task.

  7. in script task, replace code 1 shown under script task code section. script task has simple plain text email message formatting.

  8. configure send email task shown in screenshot #7. need configure valid email address in from , to fields.

  9. after configuring control flow tasks, package should shown in screenshot #8.

  10. sample package execution shown in screenshot #9.

  11. e-mail sent package shown in screenshot #10. information have been removed screenshot. can compare table data shown in screenshot #1 email output , should same.

hope helps.

sql scripts: .

create table [dbo].[emaildata](     [id] [int] identity(1,1) not null,     [itemid] [varchar](255) not null,     [itemname] [varchar](255) not null,     [itemtype] [varchar](255) not null,     [isprocessed] [bit] null,  constraint [pk_emaildata] primary key clustered ([id] asc)) on [primary] go

script task code:

c# code can used in ssis 2008 , above. .

/*microsoft sql server integration services script task    write scripts using microsoft visual c# 2008.    scriptmain entry point class of script. */  using system; using system.data; using microsoft.sqlserver.dts.runtime; using system.windows.forms;  namespace st_7f59d09774914001b60a99a90809d5c5.csproj {     [system.addin.addin("scriptmain", version = "1.0", publisher = "", description = "")]     public partial class scriptmain : microsoft.sqlserver.dts.tasks.scripttask.vstartscriptobjectmodelbase     {          #region vsta generated code         enum scriptresults         {             success = microsoft.sqlserver.dts.runtime.dtsexecresult.success,             failure = microsoft.sqlserver.dts.runtime.dtsexecresult.failure         };         #endregion          public void main()         {             variables varcollection = null;             string header = string.empty;             string message = string.empty;              dts.variabledispenser.lockforwrite("user::emailmessage");             dts.variabledispenser.lockforwrite("user::itemid");             dts.variabledispenser.lockforwrite("user::itemname");             dts.variabledispenser.lockforwrite("user::itemtype");             dts.variabledispenser.getvariables(ref varcollection);              //set header message query result             if (varcollection["user::emailmessage"].value == string.empty)             {                 header = "execute sql task output sent using send email task in ssis:\n\n";                 header += string.format("{0}\t{1}\t\t\t{2}\n", "item number", "item name", "item type");                 varcollection["user::emailmessage"].value = header;             }              //format query result tab delimiters             message = string.format("{0}\t{1}\t{2}",                                         varcollection["user::itemid"].value,                                         varcollection["user::itemname"].value,                                         varcollection["user::itemtype"].value);              varcollection["user::emailmessage"].value = varcollection["user::emailmessage"].value + message;              dts.taskresult = (int)scriptresults.success;         }     } }

screenshot #1:

1

screenshot #2:

2

screenshot #3:

3

screenshot #4:

4

screenshot #5:

5

screenshot #6:

6

screenshot #7:

7

screenshot #8:

8

screenshot #9:

9

screenshot #10:

10


Comments

Post a Comment

Popular posts from this blog

asp.net - repeatedly call AddImageUrl(url) to assemble pdf document -

i'm using abcpdf , i'm curious if can recursively call addimageurl() function assemble pdf document compile multiple urls? something like: int pagecount = 0; int theid = thedoc.addimageurl("http://stackoverflow.com/search?q=abcpdf+footer+page+x+out+of+", true, 0, true); //assemble document while (thedoc.chainable(theid)) { thedoc.page = thedoc.addpage(); theid = thedoc.addimagetochain(theid); } pagecount = thedoc.pagecount; console.writeline("1 document page count:" + pagecount); //flatten document (int = 1; <= pagecount; i++) { thedoc.pagenumber = i; thedoc.flatten(); } //now try again theid = thedoc.addimageurl("http://stackoverflow.com/questions/1980890/pdf-report-generation", true, 0, true); //assemble document while (thedoc.chainable(theid)) { thedoc.page = thedoc.addpage(); theid = thedoc.addimagetoc...
Read more

java - Android recognize cell phone with keyboard or not? -

in android app, want detect user's device if device has keyboard(like motorola milestone) , show corresponding user interface. guys know how that? ! regards justicepenny http://developer.android.com/reference/android/content/res/configuration.html public int keyboard the kind of keyboard attached device. 1 of: keyboard_nokeys, keyboard_qwerty, keyboard_12key. that's it!
Read more

iphone - How would you achieve a LED Scrolling effect? -

how achieve led scrolling effect example below? led sign - led ticker emulator iphone , ipad http://img.skitch.com/20101201-rsfh4p1bajb1wp94k466pdpiuj.preview.jpg sometimes remunerates comment in other posts :) have put code you, should implement font '8pin matrix' realistic feeling. uilabel *label; - (void)viewdidload { [super viewdidload]; label = [[uilabel alloc]initwithframe:cgrectmake(10, 50, 300, 20)]; label.font = [uifont fontwithname:@"courier-bold" size:16.0]; label.backgroundcolor = [uicolor blackcolor]; label.textcolor = [uicolor greencolor]; label.linebreakmode = uilinebreakmodecharacterwrap; // otherwise "…" label.text = @"this led text scrolled "; // blanks space between [self.view addsubview:label]; [nstimer scheduledtimerwithtimeinterval:0.12f target:self selector:@selector(scrolllabel) use...
Read more