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数据工具-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>系统
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 Server应用程序的激活权限 {FDC3723D-1588-4BA3-92D4-42C430735D7D}和APPID 向用户NT {83B33982-693D-4824-B42E-7196AE61BB05} SERVICE \ 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 SERVICE\SQLSERVERAGENT
然后授予适当的访问权限(此处中描述了授予访问权限的步骤)，我收到了另一个权限错误而该帐户尝试访问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 Server应用程序的权限 {00024500-0000-0000-C000-000000000046}和APPID 向用户NT {00020812-0000-0000-C000-000000000046} SERVICE \ 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 SERVICE\SQLSERVERAGENT
即使解决了所有权限错误，我仍然看不到预期的输出.因此，我增加了脚本任务的自定义日志记录(有关步骤，请参见此处). 然后，我可以看到脚本任务中引发的异常. 我的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屋！
