SSIS is executing successfully, but it doesn't seem to be running the script task portion

Question

I have an SSIS package that runs an SQL query and exports it to a csv file via a Data Flow Task. After the csv is created, I have a "Script Task" set to connect to an SMTP server and send the csv file as an attachment.

On my local machine the package runs fine, but when I load it into SQL Server Management Studio on the server it doesn't work as expected. SQL Server MS says that the package executed successfully, and the csv file is generated in the location expected. However, the "Script Task" doesn't appear to be executing at all. I've included some statements in the C# script to write to a file for debugging purposes - one for the try/catch exception block and a couple of others for normal execution.

    public void Main()
    {
        string sSubject = "Weekly PAX Test";
        string sBody = "Test Message";
        int iPriority = 2;

        if (SendMail(sSubject, sBody, iPriority))
        {
            Dts.TaskResult = (int)ScriptResults.Success;
        }
        else
        {
            //Fails the Task
            Dts.TaskResult = (int)ScriptResults.Failure;
        }
    }

    public bool SendMail(string sSubject, string sMessage, int iPriority)
    {
        try
        {
            string sEmailServer = Dts.Variables["User::sEmailServer"].Value.ToString();
            string sEmailPort = Dts.Variables["User::sEmailPort"].Value.ToString();
            string sEmailUser = Dts.Variables["User::sEmailUser"].Value.ToString();
            string sEmailPassword = Dts.Variables["User::sEmailPassword"].Value.ToString();
            string sEmailSendTo = Dts.Variables["User::sEmailSendTo"].Value.ToString();
            string sEmailSendFrom = Dts.Variables["User::sEmailSendFrom"].Value.ToString();
            string sEmailSendFromName = Dts.Variables["User::sEmailSendFromName"].Value.ToString();
            string sAttachmentPath = Dts.Variables["User::sAttachmentPath"].Value.ToString();

            SmtpClient smtpClient = new SmtpClient();
            MailMessage message = new MailMessage();

            MailAddress fromAddress = new MailAddress(sEmailSendFrom, sEmailSendFromName);

            //You can have multiple emails separated by ;
            string[] sEmailTo = Regex.Split(sEmailSendTo, ";");
            int sEmailServerSMTP = int.Parse(sEmailPort);

            smtpClient.Host = sEmailServer;
            smtpClient.Port = sEmailServerSMTP;

            System.Net.NetworkCredential myCredentials =
               new System.Net.NetworkCredential(sEmailUser, sEmailPassword);
            smtpClient.Credentials = myCredentials;

            message.From = fromAddress;

            if (sEmailTo != null)
            {
                for (int i = 0; i < sEmailTo.Length; ++i)
                {
                    if (sEmailTo[i] != null && sEmailTo[i] != "")
                    {
                        message.To.Add(sEmailTo[i]);
                    }
                }
            }

            switch (iPriority)
            {
                case 1:
                    message.Priority = MailPriority.High;
                    break;
                case 3:
                    message.Priority = MailPriority.Low;
                    break;
                default:
                    message.Priority = MailPriority.Normal;
                    break;
            }

            //You can enable this for Attachments.  
            //sAttachmentPath is a string variable for the file path.

            Attachment myAttachment = new Attachment(sAttachmentPath);
            message.Attachments.Add(myAttachment);


            message.Subject = sSubject;
            message.IsBodyHtml = true;
            message.Body = sMessage;

            smtpClient.Send(message);
            System.IO.File.WriteAllText("C:\\Users\\SQLCLservice\\SQLServerAgent\\file.txt", "Test");
            return true;
        }
        catch (Exception ex)
        {
            System.IO.File.WriteAllText("C:\\Users\\SQLCLservice\\SQLServerAgent\\ex.txt", ex.ToString());
            return false;

        }
    }

No email is being sent - no files are being written. It's as if the task is not running at all despite the "Successful Execution".

I did notice that the SQL Server Integration Services 11.0 service is running on my local machine but not on the server. However, if I disable this service on my local machine the task still executes.

Am I missing something else? I'm pretty new to SQL Server and I've been working on this problem for days.

EDIT: I'm running SQL Server 2012

EDIT2: I should also mention that I've tried both saving the package with 64-bit runtime set to false and running it in 32-bit mode through the SQL Server Agent.

Solution 1

I faced a similar situation earlier, where everything works fine in SSDT(SQL Server Data Tools - which is Visual studio interface) and when my package is deployed in SSMS, just the script task was failing.

Using SSIS 2012, I was loading an excel sheet and then in the script task, I was calling an Excel macro to sort and highlight the differences in the sheet. Everything was working fine in the SSDT environment but when I ran in SSMS (as a Scheduled Job - 32 bit mode), The script task was not executing. However, I could see the Excel sheet loaded with raw data - without sorting and highlighting the differences.

No errors were captured in SSMS package execution logs. As highlighted by @Tab Alleman in the comments section, some of the errors were logged in the Event viewer. In my case, the error was logged under WindowsLogs > System

Which showed permission errors for SQLSERVERAGENT while accessing Microsoft SQL Server Integration Services 11.0.

The application-specific permission settings do not grant Local Activation permission for the COM Server application with CLSID {FDC3723D-1588-4BA3-92D4-42C430735D7D} and APPID {83B33982-693D-4824-B42E-7196AE61BB05} to the user NT SERVICE\SQLSERVERAGENT

And then after granting appropriate access (Steps to grant access is described here), I received one more permission error while the account tried to access Excel

The machine-default permission settings do not grant Local Activation permission for the COM Server application with CLSID {00024500-0000-0000-C000-000000000046} and APPID {00020812-0000-0000-C000-000000000046} to the user NT SERVICE\SQLSERVERAGENT

Even after resolving all the permission errors, I still couldn't see the expected output. So I increased the custom logging (refer here for steps) for the script task. Then I was able to see the exceptions raised in the script task. My catch block looks something like the one below:

  catch (Exception ex)
            {
               Dts.Log(ex.ToString(), 0, emptyBytes);
            }

Which will log the errors in the sysssislog table (which you must have configured while configuring custom logging)

select * from [*YourDatabaseName*].[dbo].[sysssislog]

Should list down your custom logs.

Solution 2

I too faced the similar situation, but resolved it differently. I had two key tasks in my SSIS package. A Script Task running C# and a Data Flow Task reading an Excel file. I first received this error:

OLE DB provider Microsoft.Jet.OLEDB.4.0 is not registered.  If the 64-bit drive is not installed, run the package in 32-bit mode.

After running in 32 bit mode, I noticed that my Script Task didn't run. SSIS just ignored it as if it was disabled. I did find some Audit Failures in my Event Viewer.

The root cause of my Script Task not running was because it was being compiled for x64 (the default setting). Here are my steps that I used to fix this:

1. Edit Script
2. Right click the c# project name that looks like a GUID and choose properties
3. Select the Build menu on the left side of the properties page
4. Set the Platform target = x86

Now your Script Task will now run in 32 bit mode along with your 32 bit OLEDB Jet driver.