1

I have an ssis package with an existing vb script task to send emails.

I am rewriting it in c#.

The vb version works, the c# version doesn't but it doesn't fail or error either, that I can see.

If I put a breakpoint in both, when I run the package to debug, it breaks in the vb script but not the c# script.

I have included the code for each below.

They are very similar so I am wondering if there is a task setting that controls this that I am overlooking.

Thanks in advance

The vb script is as follows.

...

Public Sub Main()

    Dim htmlMessageFrom As String = Dts.Variables("SSISErrorEmailFrom").Value.ToString
    Dim htmlMessageTo As String = Dts.Variables("SSISErrorEmailTo").Value.ToString
    Dim htmlMessageSubject As String = Dts.Variables("SSISErrorEmailSubject").Value.ToString
    Dim htmlMessageBody As String = Dts.Variables("SSISErrorEmailBody").Value.ToString
    Dim SSISErrorTable As String = Dts.Variables("SSISErrorTable").Value.ToString
    Dim smtpConnectionString As String = DirectCast(Dts.Connections("SMTP Connection").AcquireConnection(Dts.Transaction), String)
    Dim smtpServer As String = "smtprelay.white01.babcockgroup.co.uk"

    htmlMessageBody = htmlMessageBody.Replace("###subject###", htmlMessageSubject)
    htmlMessageBody = htmlMessageBody.Replace("###SSISErrorTable###", SSISErrorTable)

    SendMailMessage(
  htmlMessageFrom, htmlMessageTo,
  htmlMessageSubject, htmlMessageBody,
  True, smtpServer)

    Dts.TaskResult = ScriptResults.Success

End Sub

Private Sub SendMailMessage(
ByVal From As String, ByVal SendTo As String,
ByVal Subject As String, ByVal Body As String,
ByVal IsBodyHtml As Boolean, ByVal Server As String)

    Dim htmlMessage As MailMessage
    Dim mySmtpClient As SmtpClient

    htmlMessage = New MailMessage(
From, SendTo, Subject, Body)
    htmlMessage.IsBodyHtml = IsBodyHtml

    mySmtpClient = New SmtpClient(Server)
    mySmtpClient.Credentials = CredentialCache.DefaultNetworkCredentials
    mySmtpClient.Send(htmlMessage)

End Sub

...

The c# scipt is as follows

...

    public void Main()
    {
        sendEmail();
    }

    private void sendEmail()
    {

        var noBytes = new byte[0];
        // TODO: Add your code here
        try
        {
            string SSISErrorEmailTo = Dts.Variables["SSISErrorEmailTo"].Value.ToString();
            string SSISErrorEmailFrom = Dts.Variables["SSISErrorEmailFrom"].Value.ToString();
            string SSISErrorEmailSubject = Dts.Variables["SSISErrorSubject"].Value.ToString();
            string SSISErrorEmailBody = Dts.Variables["SSISErrorEmailBody"].Value.ToString();
            string SSISErrorTable = Dts.Variables["SSISErrorTable"].Value.ToString();

            SSISErrorEmailBody.Replace("###subject###", SSISErrorEmailSubject);
            SSISErrorEmailBody.Replace("###SSISErrorTable###", SSISErrorTable);

            string SmtpServer = "smtprelay.white01.babcockgroup.co.uk"; 

            MailMessage msg = new MailMessage(SSISErrorEmailFrom, SSISErrorEmailTo, SSISErrorEmailSubject, SSISErrorEmailBody);
            msg.IsBodyHtml = true;

            SmtpClient smtpClient = new SmtpClient(SmtpServer);
            smtpClient.Credentials = CredentialCache.DefaultNetworkCredentials;
            smtpClient.Send(msg);

            Dts.TaskResult = (int)ScriptResults.Success;
            Dts.Log("OnError script completed", -1, noBytes);
        }
        catch (Exception e)
        {
            Dts.TaskResult = (int)ScriptResults.Failure;
            Dts.Log(e.InnerException.Message, -1, noBytes);
        }
    }

...

kevins1966
  • 366
  • 2
  • 4
  • 19
  • If the task doesn't compile due to syntax issues, you might be exiting the script editor without having a valid "thing". You're not writing equivalent code by the way. The VB is parameterized and your C# assumes you can access the Dts.Variables collection in the method. Try making parameters for `sendEmail` and populating them from the Main() method. Also, move the TaskResult logic there as well which might entail reworking the sendEmail as a non-void return type. – billinkc May 13 '20 at 15:32
  • I have added another c# script task and not touched the code except to add a break point in the Main method. The package still does not stop on the break point. That makes me think it's a setting. – kevins1966 May 13 '20 at 15:51
  • As a further experiment I have created a new package in the same solution containing just a vanilla c# script task with a break point. That does break when run. Any thoughts? – kevins1966 May 13 '20 at 15:56
  • Created the hello world equivalent of an ssis package with a script task; breakpoint set on a trivial assignment (SQL Server 2017 target) and it totally skipped it. Same behaviour in C# or VB.NET. This _should_ work but clearly isn't. Feels like it's getting optimized out but I have verified I'm starting in debug mode and the default project settings allow for debugging. -> https://i.stack.imgur.com/9qMex.gif Animated gif showing breakpoints set as one would expect but not working as expected – billinkc May 13 '20 at 16:15
  • 1
    OK, so the vanilla c# script task which, as I mentioned above, I added to separate package which I could break in, I copied to the package I am working on where the original non-breaking c# script is. It still managed to stop on the breakpoint while the original still didn't. I have copied all the code from the troublesome script to the new one and the new one still breaks as required and the original doesn't. If anyone can explain how you can have two c# scripts containing identical code but where the break point in one is detected but the other isn'tt, I'd been interested. – kevins1966 May 13 '20 at 16:48
  • Have you tried these: https://stackoverflow.com/questions/41470415/debugging-ssis-script-task-breakpoint-is-enabled-but-it-does-not-hit ? Something that I mess up a lot, how do you run your package when you want to debug it? You should execute the package, not just run the script task. Also, more info here: https://learn.microsoft.com/en-us/sql/integration-services/extending-packages-scripting/debug-a-script-by-setting-breakpoints-in-a-script-task-and-script-component?view=sql-server-ver15 – Gigga May 14 '20 at 17:11

0 Answers0