SSIS正在成功执行，但它似乎没有运行脚本任务部分 [英] SSIS is executing successfully, but it doesn't seem to be running the script task portion
问题描述
我有一个 SSIS 包，它运行 SQL 查询并通过数据流任务将其导出到 csv 文件.创建 csv 后，我将脚本任务"设置为连接到 SMTP 服务器并将 csv 文件作为附件发送.
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.
在我的本地机器上，包运行良好，但是当我将它加载到服务器上的 SQL Server Management Studio 时，它没有按预期工作.SQL Server MS 表示包执行成功，并且在预期位置生成了 csv 文件.但是，脚本任务"似乎根本没有执行.我在 C# 脚本中包含了一些语句，用于写入文件以进行调试 - 一个用于 try/catch 异常块，另外两个用于正常执行.
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".
我确实注意到 SQL Server Integration Services 11.0 服务在我的本地机器上运行，但不在服务器上运行.但是，如果我在本地机器上禁用此服务，任务仍会执行.
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.
我还缺什么吗?我是 SQL Server 的新手，我已经研究这个问题好几天了.
Am I missing something else? I'm pretty new to SQL Server and I've been working on this problem for days.
我正在运行 SQL Server 2012
I'm running SQL Server 2012
我还应该提到，我已经尝试在 64 位运行时设置为 false 的情况下保存包，并通过 SQL Server 代理在 32 位模式下运行它.
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.
推荐答案
我之前遇到过类似的情况，在 SSDT(SQL Server Data Tools - Visual Studio 界面)中一切正常，当我的包部署在 SSMS 中时，只是脚本任务失败了.
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.
使用 SSIS 2012，我加载了一个 Excel 工作表，然后在脚本任务中，我调用了一个 Excel 宏来对工作表中的差异进行排序和突出显示.在 SSDT 环境中一切正常，但是当我在 SSMS(作为计划作业 - 32 位模式)中运行时，脚本任务没有执行.但是，我可以看到加载了原始数据的 Excel 工作表 - 没有对差异进行排序和突出显示.
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.
在 SSMS 包执行日志中未捕获到任何错误.正如@Tab Alleman 在评论部分强调的那样，一些错误记录在事件查看器中.就我而言，错误记录在 WindowsLogs > System
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
在访问 Microsoft SQL Server Integration Services 11.0 时显示 SQLSERVERAGENT 的权限错误.
Which showed permission errors for SQLSERVERAGENT while accessing Microsoft SQL Server Integration Services 11.0.
应用程序特定的权限设置不授予本地具有 CLSID 的 COM 服务器应用程序的激活权限{FDC3723D-1588-4BA3-92D4-42C430735D7D}和APPID{83B33982-693D-4824-B42E-7196AE61BB05}给用户NT服务SQLSERVERAGENT
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 SERVICESQLSERVERAGENT
然后在授予适当的访问权限(授予访问权限的步骤在 此处 中描述)后，我又收到了一个权限错误当帐户尝试访问 Excel 时
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
机器默认权限设置不授予本地激活具有 CLSID 的 COM 服务器应用程序的权限{00024500-0000-0000-C000-000000000046}和APPID{00020812-0000-0000-C000-000000000046}给用户NT服务SQLSERVERAGENT
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 SERVICESQLSERVERAGENT
即使解决了所有权限错误，我仍然看不到预期的输出.因此，我增加了脚本任务的自定义日志记录(请参阅 此处 了解步骤).然后我能够看到脚本任务中引发的异常.我的 catch 块如下所示:
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);
}
这将在 sysssislog 表中记录错误(您必须在配置自定义日志记录时配置)
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.
这篇关于SSIS正在成功执行，但它似乎没有运行脚本任务部分的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
