SQL Server Integration Services（SSIS）中的脚本任务调试
脚本任务 ( Script Task )
In SQL Server Integration services (SSIS), it may not be possible to meet all the business requirements using built-in tasks. In these cases, we can achieve the functionality using C# or VB.net code in script task. The script task is an ideal alternative solution to built-in tasks.
在SQL Server集成服务（SSIS）中，使用内置任务可能无法满足所有业务要求。 在这些情况下，我们可以在脚本任务中使用C＃或VB.net代码来实现该功能。 脚本任务是内置任务的理想替代解决方案。
脚本任务与脚本组件 ( Script Task Vs Script Component )
Both the Script Task and the Script component have the provision to extend the default functionality using C# or VB.Net. The script task is used in the control flow as a task. Whereas the script component has been used in the data flow as a source, destination or a transformation.
脚本任务和脚本组件都提供了使用C＃或VB.Net扩展默认功能的规定。 脚本任务在控制流中用作任务。 而脚本组件已在数据流中用作源，目标或转换。
Following are few examples which describe the power of script task
以下是一些描述脚本任务功能的示例
- Querying Active Directory service Interface (AD) from SSIS to extract the list of membership groups for a given user id 从SSIS查询Active Directory服务接口（AD），以提取给定用户ID的成员资格组列表
- Filter source data files based on a number of criteria (size of the file, date and contents of the file, etc.) 根据许多条件（文件大小，日期和内容等）过滤源数据文件
Though there are many advantages of using the script task, it is usually undermined by the developers because of the complexity to debug and fix the issues.
尽管使用脚本任务有很多优点，但是由于调试和解决问题的复杂性，开发人员通常会破坏它。
脚本任务调试 ( Script Task Debugging )
In this blog post, I have consolidated a number of techniques to debug the script task. These debugging techniques have been classified into two groups.
在此博客文章中，我整合了许多调试脚本任务的技术。 这些调试技术已分为两类。
- Design time debugging techniques 设计时调试技术
- Run time debugging techniques 运行时调试技术
设计时调试 ( Design Time Debugging )
This method is suitable while you are developing a package. You will be using the SSDT to debug and investigate.
在开发软件包时，此方法适用。 您将使用SSDT进行调试和调查。
运行时调试 ( Run Time Debugging )
Run time analysis is useful, once the package has been deployed and scheduled to run at a specific time. In this case, you will not be able to use the SSDT to debug the package. Hence we need to use other alternative techniques to understand the procedure during execution.
一旦部署了该程序包并计划在特定时间运行该程序，运行时分析将很有用。 在这种情况下，您将无法使用SSDT调试软件包。 因此，我们需要使用其他替代技术来理解执行过程中的过程。
解决方案概述 ( Solution overview )
To explain multiple debugging facilities, I have created a sample SSIS package with one script task. This script task accept one user variable (Filecount).
为了解释多种调试功能，我创建了一个带有一个脚本任务的示例SSIS包。 此脚本任务接受一个用户变量（Filecount）。
The below mentioned image represents the sample package at high level.
下面的图像代表了高级样品包装。
Using this sample package, we will be exploring the debugging techniques. There are four debugging methods are available.
使用此样本包，我们将探索调试技术。 有四种调试方法可用。
- Using Message Box to print values 使用消息框打印值
- Using Breakpoint options for debugging 使用断点选项进行调试
- Using Logging for the Script Task 使用日志记录执行脚本任务
- Using Try/Catch within Script Task 在脚本任务中使用Try / Catch
The Message Box and Breakpoint options are suitable for design time debugging. The logging and “Try catch” methods are ideal to debug during the run time.
“消息框”和“断点”选项适用于设计时调试。 日志记录和“尝试捕获”方法非常适合在运行时进行调试。
1.留言框 (1. Message Box )
In this technique, we will be using the Message Box (Dialogue Box) to show the value of the variable or a string in the screen. By displaying a string or a variable, the developer will be able to debug. This can be achieved by using the code below.
在这种技术中，我们将使用消息框（对话框）在屏幕上显示变量的值或字符串。 通过显示字符串或变量，开发人员将能够进行调试。 这可以通过使用下面的代码来实现。
Messagebox.show(string value)
Messagebox.show（字符串值）
This is very primitive level debugging to know the details. The below mentioned picture represents the code within the script task to use the Message Box.
这是非常原始的级别调试，可了解详细信息。 下面提到的图片代表脚本任务中使用消息框的代码。
During the execution the SSIS will show a message box with a value as per the picture below.
在执行过程中，SSIS将显示一个消息框，其值如下图所示。
2.断点 (2. Breakpoint)
Setting a breakpoint will help us to stop the debugger during execution. This will help us see the value of the variable at the time of execution. Break point can be set by placing the cursor on a line and pressing the function key F9. Once set, the debugging line will be identified using the red dot in the left margin. The defined breakpoints can be managed in the VSTA and it can be removed from the “Set Breakpoints” dialog box in SSDT designer.
设置断点将有助于我们在执行过程中停止调试器。 这将帮助我们在执行时查看变量的值。 可以通过将光标放在一行上并按功能键F9来设置断点。 设置后，调试行将使用左侧空白处的红点标识。 可以在VSTA中管理定义的断点，并且可以从SSDT设计器的“设置断点”对话框中将其删除。
The below mentioned picture highlight the defined breakpoint in the script task.
下面提到的图片突出显示了脚本任务中定义的断点。
The “Set Breakpoints” dialog box shows the breakpoints in the script task. The breakpoints will be listed in the bottom of the dialog box with the line number and the name of the function. At run time, the visual studio for application (VSTA) will open the script task in read only mode.
“设置断点”对话框显示脚本任务中的断点。 断点将在对话框底部列出，并带有行号和函数名称。 在运行时，Visual Studio for Application（VSTA）将以只读模式打开脚本任务。
Now the breakpoint line will be highlighted and the execution will be paused.
现在，断点行将突出显示，并且执行将暂停。
In the above mentioned picture, we are reading the value of the user variable “Filecount” and assign it to local variable “Filecount”. The value of the variable will be derived and will be displayed in the message box. Now the value of the variable can be evaluated by placing the cursor over the variable.
在上面提到的图片中，我们正在读取用户变量“ Filecount”的值，并将其分配给局部变量“ Filecount”。 该变量的值将被派生并显示在消息框中。 现在，可以通过将光标放在变量上来评估变量的值。
The value of the variable can also be printed in the immediate window as well.
变量的值也可以在立即窗口中打印。
失败场景 (Failure Scenario)
The main purpose of the debugging is to explore and understand the cause of the failure. So let’s have a look at a sample failure scenario.
调试的主要目的是探索并了解失败的原因。 因此，让我们看一个示例故障场景。
To explain the failure scenario, I have purposely misspelled the variable as “user”, instead of “User”. Hence I am expecting the script task to fail, while evaluating the value of the variable.
为了解释故障情况，我故意将变量拼写为“ user”，而不是“ User”。 因此，我期望脚本任务在评估变量值时失败。
In the below mentioned picture, the line has been hit during the execution.
在下面提到的图片中，该行在执行过程中被命中。
As the variable collection has been misspelled, it is expected that the exception will be thrown.
由于变量集合的拼写错误，因此预计将引发异常。
The exception has been caught by the VSTA and details are displayed in the dialog box as per the picture below.
VSTA已捕获到该异常，并且详细信息显示在对话框中，如下图所示。
成功案例 (Success Scenario)
Once the variable has been corrected (as “User”), the same line has been executed successfully. Now the value of the variable has been displayed when you place the cursor over the variable. The below mentioned picture represents the successful execution and the value of the variable.
一旦变量被更正（以“用户”身份），则同一行已成功执行。 现在，将光标放在变量上时，已显示变量的值。 下面提到的图片代表成功执行和变量的值。
The value of the “Filecount” variable can also be printed in the command window. This can be achieved by using the expression “?Filecount”. The value will be calculated and displayed on the next line.
“ Filecount”变量的值也可以在命令窗口中打印。 这可以通过使用表达式“？Filecount”来实现。 该值将被计算并显示在下一行。
运行时调试 ( Run Time Debugging )
While the above mentioned solutions will help us to evaluate the variable during development, but this may not help during run time. As the SSIS package will be executed without manual intervention, we need other way to capture the value for post mortem analysis.
尽管上述解决方案将帮助我们在开发过程中评估变量，但是在运行时可能无济于事。 由于SSIS程序包将在无需人工干预的情况下执行，因此我们需要其他方法来获取价值以进行事后分析。
This can be achieved in two ways and these methodologies have been discussed below with some samples.
这可以通过两种方式来实现，下面已通过一些示例讨论了这些方法。
3.启用日志记录 (3. Enable Logging)
Logging in Integration services will help us to record details such as execution progress and results. SSIS can be used to log, predefined events or the user defined messages. The Log method on the Dts object can be used to log user defined messages. To make use of the logging in the script task, the logging for the script task should be enabled. In addition the “ScriptTaskLogEntry” event should be selected on the “Configure SSIS logs” dialog box. The below mentioned steps will help us to enable and select a suitable log provider.
登录集成服务将帮助我们记录详细信息，例如执行进度和结果。 SSIS可用于记录，预定义事件或用户定义的消息。 Dts对象上的Log方法可用于记录用户定义的消息。 要使用脚本任务中的日志记录，应启用脚本任务的日志记录。 此外，应在“配置SSIS日志”对话框中选择“ ScriptTaskLogEntry”事件。 以下提到的步骤将帮助我们启用和选择合适的日志提供程序。
In the below mentioned picture, the logging has been enabled with the SQL server log provider.
在下面提到的图片中，已使用SQL Server日志提供程序启用了日志记录。
Once the provider has been selected, it is mandatory to select the detailed the event “ScriptTaskLogEntry” as per the picture below.
一旦选择了提供者，就必须根据下图选择详细的事件“ ScriptTaskLogEntry”。
The Log method of Dts object will be called and the user defined message will be passed with the value of the variable. The actual code for logging can be seen below.
Dts对象的Log方法将被调用，并且用户定义的消息将与变量的值一起传递。 实际的记录代码可以在下面看到。
查询日志表 (Querying log table)
After a successful execution of the script task, the SSIS has logged the user defined message in the log table. Now let’s query the SysSSISLog table for the script tasks log entry. The results of the query can be seen below.
成功执行脚本任务后，SSIS已将用户定义的消息记录在日志表中。 现在，让我们在SysSSISLog表中查询脚本任务日志条目。 查询结果可以在下面看到。
4.尝试catch /错误处理 (4. Try catch / Error Handling)
Though you programmed and planned for all circumstances, unexpected exceptions do occur over a period of time. It is possible to handle the unexpected exception in the code.
尽管您已针对所有情况进行了编程和计划，但是在一段时间内确实会发生意外异常。 可以处理代码中的意外异常。
In script task, the unexpected exception can be captured using the try catch block. As per the picture below, the code should be placed in the try block.
在脚本任务中，可以使用try catch块捕获意外的异常。 如下图所示，代码应放在try块中。
If an exception has been found, then the call stack will be redirected to the catch block. The details of the exception will be available in the catch block. The exception will be caught using and the stack can be accessed using the properties.
如果发现异常，则调用堆栈将重定向到catch块。 异常的详细信息将在catch块中提供。 将使用捕获异常，并可以使用属性访问堆栈。
As the exception has occurred, we need to inform the package that script task has failed. This can be done by raising the event Fire.Error in the Dts object. This event details can be logged for future purposes.
由于发生了异常，我们需要通知程序包脚本任务已失败。 这可以通过在Dts对象中引发事件Fire.Error来完成。 可以记录此事件详细信息，以备将来使用。
The below mentioned picture represents the log entry for the try catch block.
下面提到的图片代表try catch块的日志条目。
In addition to log entry the details of the error can also be found on the progress windows as per the image below.
除了日志条目外，错误的详细信息也可以在进度窗口中找到，如下图所示。
摘要 ( Summary )
We have seen few methods to debug the code in the script task. Debugging the code in SSIS is an art. I recommend you to try it out all the methods and see which one fits better in your case.
我们看到很少的方法可以调试脚本任务中的代码。 在SSIS中调试代码是一门艺术。 我建议您尝试所有方法，然后看看哪种方法更适合您的情况。
参考 ( Reference )
- Logging in the Script Task 登录脚本任务
- How to: Debug a Script by Setting Breakpoints in a Script Task 如何：通过在脚本任务中设置断点来调试脚本
- MessageBox.Show Method (String, String, MessageBoxButton) MessageBox.Show方法（字符串，字符串，MessageBoxButton）
翻译自: https://www.sqlshack.com/script-task-debugging-in-sql-server-integration-services-ssis/