活动任务SSIS中的自定义日志记录 [英] Custom logging in Active task SSIS

问题描述

我在SSIS软件包中面临两个问题.我有一个SSIS包，它有一个活动任务，可以按照我的要求格式化Excel工作表并将其保存为其他文件Modify.xlsx.然后，在我的数据流任务中使用此文件来处理数据并将其上传到数据库表.该程序包在我的本地系统中运行完美，但是当我在SQL Server上创建计划作业以运行该程序包时，它失败，并显示通用错误消息"Microsoft(R)SQL Server执行程序包实用程序版本11.0.5058.0(用于64位)"版权所有(C)Microsoft Corporation.版权所有.开始时间:12:06:55 PM错误:2016-04-01 12:06:57.06代码:0x00000001源:脚本任务描述:调用目标已引发异常.结束错误DTExec:程序包执行返回DTSER_FAILURE(1).开始:12:06:55 PM完成:12:06:57 PM经过:1.563秒.程序包执行失败.该步骤失败."

要获取更详细的错误消息，我尝试为活动任务设置日志记录.我将日志记录配置为将事件的日志条目写入CSV文件，如下面的屏幕截图所示.

我启用了程序包的日志记录功能，并且还检查了个人任务.在活动任务中，我添加了Dts.Log(，"，0，bytes);.跟踪任何异常(如果有的话)也记录每个步骤.

 公共局部类ScriptMain:Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase{byte []个字节=新的byte [0]；公共无效Main(){LogMessages(")；LogMessages(更新Bug程序包的执行从::: + + DateTime.Now.ToLongTimeString())开始；LogMessages(将包配置值加载到局部变量.")；FileName = Convert.ToString(Dts.Variables ["User :: ExcelFileName"].Value)；SourceFileLocation = Convert.ToString(Dts.Variables ["User :: SourceFileLoc"].Value)；SourceFileName = Path.Combine(SourceFileLocation，FileName);saveLoc = Path.Combine(SourceFileLocation，"ModifiedExcel.xlsx");var excel = new Excel.Application();var workbook = excel.Workbooks.Open(SourceFileName);尝试{foreach(工作簿中的Excel.Worksheet tempSheet.Worksheets){LogMessages("For循环以检查工作表名称")；if((((Excel.Worksheet)(tempSheet)).Name.Contains("Test"))){如果(File.Exists(saveLoc)){File.Delete(saveLoc);}//File.Create(saveLoc);tempSheet.Select();workbook.SaveAs(saveLoc);}System.Runtime.InteropServices.Marshal.ReleaseComObject(tempSheet);}workbook.Save();workbook.Close();excel.Quit();LogMessages(退出Excel工作表")；System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);LogMessages(发布excel对象")；}抓住(例外){LogMessages(" Exception:" + ex.InnerException);System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);}Dts.TaskResult =(int)ScriptResults.Success;}#region ScriptResults声明///< summary>///此枚举为此类的设置提供了一个方便的快捷方式，用于设置///脚本的结果.//////此代码是自动生成的.///</summary>枚举ScriptResults{成功= Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success，失败= Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure};#endregion#region将消息记录到包日志文件/表中.公共无效LogMessages(字符串strLogMsg){Dts.Log(strLogMsg，0，字节);}#endregion} 

但是当我运行软件包时，日志文件未更新.日志文件仅包含以下内容:

字段:事件，计算机，操作员，源，sourceid，执行ID，开始时间，结束时间，数据代码，数据字节，消息

有人可以帮助我了解我在此处缺少的日志记录吗?另外，作业在SQL Server中失败可能是什么问题?

解决方案

为什么不记录日志?

这是最有趣的部分，这是我多年来与SSIS的往来所能做到的. Dts.Log 几乎没有用，至少如果您希望将其显示在SSIS内置的日志记录工具中.

相反，将您的Dts.Log调用更改为Dts.Events. Fire ，例如

  bool fireAgain = false;Dts.Events.FireInformation(0，此gest已记录"，我的详细描述在这里"，string.Empty，0，ref fireAgain); 

然后，在上方的 Details 标签中，确保您已选中 OnInformation 事件(这也假定您已将软件包配置为跟踪所有事件)

最后，如果您实际上未单击提供者和日志"选项卡中的按钮，它将不会登录到表中

为什么不起作用?

该软件包无法正常工作，因为您正在使用Excel，并且错误消息表明您正在以64位模式运行.

用于64位的Microsoft(R)SQL Server执行软件包实用程序版本11.0.5058.0

除非您已完成一些操作以在此服务器上显式使64位Excel正常工作，否则它将无法正常工作.相反，在SQL Agent作业中，您需要指定此任务以32位模式运行.

另请参见

I am facing two issues in my SSIS package. The SSIS package I have has an active task that formats an excel sheet as per my requirement and saves it as a different file, modified.xlsx. This file is then used in my data flow task to process and upload data to database tables. This package works perfectly in my local system, but when I created a scheduled job on my SQL server to run this package it fails with the generic error message "Microsoft (R) SQL Server Execute Package Utility Version 11.0.5058.0 for 64-bit Copyright (C) Microsoft Corporation. All rights reserved. Started: 12:06:55 PM Error: 2016-04-01 12:06:57.06 Code: 0x00000001 Source: Script Task Description: Exception has been thrown by the target of an invocation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 12:06:55 PM Finished: 12:06:57 PM Elapsed: 1.563 seconds. The package execution failed. The step failed."

To get a more detailed error message i tried to set up logging for the active task. I configured logging to write log entries for events to a CSV file as in the below screen shot.

I enabled logging for package and checked individuals tasks as well. In active tasks, I added Dts.Log("",0,bytes); to keep track of any exception if any also to log each steps.

public partial class ScriptMain:Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{
 byte[] bytes = new byte[0];
    public void Main()
            {
                LogMessages("");
    
                LogMessages("Update Bug package execution started at :: " + DateTime.Now.ToLongTimeString());
                LogMessages("Loading package configuration values to local variables.");
    
                FileName = Convert.ToString(Dts.Variables["User::ExcelFileName"].Value);
                SourceFileLocation = Convert.ToString(Dts.Variables["User::SourceFileLoc"].Value);
    
                SourceFileName = Path.Combine(SourceFileLocation, FileName);
                saveLoc = Path.Combine(SourceFileLocation, "ModifiedExcel.xlsx");
               
    
                var excel = new Excel.Application();
                var workbook = excel.Workbooks.Open(SourceFileName);
                try
                {
    
                    foreach (Excel.Worksheet tempSheet in workbook.Worksheets)
                    {
                        LogMessages("For loop to check sheet names");
                        if (((Excel.Worksheet)(tempSheet)).Name.Contains("Test"))
                        {
                            if (File.Exists(saveLoc))
                            {
                                File.Delete(saveLoc);
                            }
    
                            //File.Create(saveLoc);
                            tempSheet.Select();
                            workbook.SaveAs(saveLoc);
                        }
    
                        System.Runtime.InteropServices.Marshal.ReleaseComObject(tempSheet);
                    }
    
                    workbook.Save();
                    workbook.Close();
                    excel.Quit();
                    LogMessages("Quit Excel sheet");
    
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
                    LogMessages("Release excel objects");
                }
                catch(Exception ex)
                {
                    LogMessages("Exception: " + ex.InnerException);
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
                }
    
    
                Dts.TaskResult = (int)ScriptResults.Success;
            }
    
            #region ScriptResults declaration
            /// <summary>
            /// This enum provides a convenient shorthand within the scope of this class for setting the
            /// result of the script.
            /// 
            /// This code was generated automatically.
            /// </summary>
            enum ScriptResults
            {
                Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
                Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
            };
            #endregion
    
            #region Log messages to package log files/table.
            public void LogMessages(string strLogMsg)
            {
                Dts.Log(strLogMsg, 0, bytes);
            }
            #endregion
}

But when i run the package the log file is not updated. The log file just contains the following :

Fields: event,computer,operator,source,sourceid,executionid,starttime,endtime,datacode,databytes,message

Can someone please help me understand what I am missing here for logging? Also, what could be the issue for the job to fail in SQL server?

解决方案

Why is it not logging?

Here's the fun part, as best as I've been able to divine over the years of dealing with SSIS. Dts.Log is pretty useless, at least if you want it to show up in the Logging facility built into SSIS.

Instead, change out your Dts.Log calls to Dts.Events.Fire e.g.

bool fireAgain = false;
Dts.Events.FireInformation(0, "This gest logged", "My long description here", string.Empty, 0, ref fireAgain);

Then, in your Details tab above, ensure that you have checked the OnInformation event (this also presumes you've configured the package to track it all)

Finally, if you don't actually click the button in Providers and Logs tab, it won't log to the table

Why is it not working?

The package isn't working because you're dealing with Excel and the error message specifies you are running in 64 bit mode.

Microsoft (R) SQL Server Execute Package Utility Version 11.0.5058.0 for 64-bit

Unless you've done something to explicitly make 64 bit Excel work on this server, it's not going to work. Instead, in the SQL Agent job, you need to specify that this task runs in 32 bit mode.

See also

Why does my ODBC connection fail when running an SSIS load in Visual Studio but not when running the same package using Execute Package Utility

这篇关于活动任务SSIS中的自定义日志记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！