SQL Server Performance

How to extract updated data and email to muliple users

Discussion in 'SQL Server 2005 Integration Services' started by ahn0212, Feb 18, 2009.

  1. ahn0212 New Member

    I'm new on SSIS, so I'm struggling with it now.
    So, the whole picture I've been thinking is that
    1. extracting data which have null value from tables
    2. Retrieve the email accouts from the extracted data
    3. sending email to their accounts (so, it will be multiple users)
    I want to
    1. extract null data daily basis from my database/cube
    • first, I tried to set variable for defining current date so that the system reads data only for the current date,
    I set date variable as package, string type "Select getDate()" but it does not work.
    Is there any other way to get current date?
    • 2. email warning to multiple users who have null data
    Is it possible to send email to multiple users? If anyone knows, please answer my questions.
    I will really appreciate it.
  2. AJITH123 Member

    You can do it by using a script component
    (1) Put a execute task and find out the null values in the table by a select statement.
    (2) Put a foreack component to loop throug the records
    (3) Put a script component and on the script page, put the below mentioned script. This will send mail to the receipient
    (4) Define 2 variable for catching the ToAddress and Mailserver [IP] address.
    --------------------------------------Imports
    SystemImports
    System.DataImports
    System.MathImports
    Microsoft.SqlServer.Dts.RuntimeImports
    System.Net.MailImports
    System.Net
    --------------------------------------

    Dim HtmlMessage As StringDim myHtmlMessage As MailMessage Dim mySmtpClient As SmtpClient
    Dim toEmailAddress As StringDim FromEmailAddress As String Dim sSMTPServer As String = Dts.Variables("ServerId").Value.ToString()
    'MsgBox("After " & Dts.Variables("ApplicantId").Value.ToString())toEmailAddress = Dts.Variables("EmailId").Value.ToString()
    FromEmailAddress = Dts.Variables(
    "EmailId").Value.ToString()mySmtpClient = New SmtpClient(sSMTPServer) mySmtpClient.Credentials = New NetworkCredential("UserId", "Password", "domain")
    '------------------------------------------------------------------------------------
    'Notify applicantHtmlMessage =
    "Test Message "myHtmlMessage = New MailMessage(FromEmailAddress, toEmailAddress _, "Test Subject ", HtmlMessage)
    mySmtpClient.Send(myHtmlMessage)
    '------------------------------------------------------------------------------------Dts.TaskResult = Dts.Results.Success
    Thanks
    Ajith
  3. ahn0212 New Member

    Thanks alot, Ajith!!!!
    Another question.
    Is there anyway to import calender to SSIS?
    I found some articles about CalendarTransform sample provided by microsoft.
    I'm trying to download it but, microsoft does not provide it anymore..*dissapointed*
    If you know, could you tell me how to get the calendar?
  4. preethi Member

    Calendar Transform is a nice control which gives a whole lot of information on a datetime column. It does not gives you a calendar (like holidays etc.), but transforms a date time column into calendar format. ( by using most of the date related functions)
    It is no more supported. However, you can use derived columns to implement most of the features given by calendar transform. You have to write the code, but it is the way available right now.
    Check this site, This may be helpful to you: http://blogs.conchango.com/jamiethomson/archive/2006/02/14/SSIS_3A00_-Calendar-Transform-Sample.aspx
  5. ahn0212 New Member

    I am trying to compile script component. But I got Errors.
    It says Name "Dts" is not declared.
    As far as I know, in this syntax, "Dim sSMTPServer As String = Dts.Variables("ServerId").Value.ToString() "Dts means my variable's namespace, right? so, I put (NameSpace name.Variables("VariableName").Value.ToString())
    I don't know why it doesnt recognize the my namespace "Dts"
    I am using SQL server 2005 and the variable is user-defined, and package scope.Have any idea?
    Please help!!! lol
  6. AJITH123 Member

    Sorry ahn, You are correct, however I do not have idea why it is failing :( , i just tried the code which i sent you is working fime withe the environment which you mentioned.
    The other variable also have the same issue ?

Share This Page