sql-server - 为什么我的 SSIS 包在第一次运行时失败,但在第二次运行时没有?“对象不能从 DBNULL 转换为其他类型”
问题描述
我有一个问题导致我的 SSIS 包在第一次执行时失败,而在第 2 次执行时,问题自动解决并且 SSIS 包成功执行。
项目:迁移包
- SQL Server 2008 R2 到 SQL Server、SSDT 2016
- Visual Studio 2008 到 Visual Studio 2015
案子:
我迁移的 SSIS 包之一具有以下脚本任务:
- 使用一个 OUTPUT 参数执行存储过程以获取行数
- 将返回的行写入 CSV 格式的 txt 文件。
存储过程在此脚本任务下执行;返回(数千,数百万行)以及一个 OUTPUT 参数,其中包含存储过程返回的行数。
问题:
此 SSIS 包在第一次执行时随机失败并出现错误 -
“对象不能从 DBNULL 转换为其他类型”
并在其第二次执行成功执行。
当我们google这个错误时,发现最常见的解决方法是先检查Variable值:是否是INT值,是否有值,然后才转换。
但是,此解决方案不适用于此处,因为当我第二次执行 SSIS 包时,它会成功执行。
这里要注意的另一件事是,当 SSIS 包随机失败时,它会写入包含不完整行的 CSV 文件,并且在第二次执行时,此问题也得到了解决。
看起来上述错误不是实际错误,并且由于未知原因没有给出实际错误。
是因为 SSIS / SQL 级别缺少任何配置还是任何其他问题?
请在下面找到 SCRIPT 和存储过程代码。
在 SCRIPT 代码下面的行中捕获的每个随机故障。但是,在第二次运行时没有出现这样的问题。因此,看起来它不是转换问题,而是更多地针对 2016 配置或一些不同的编码方式问题-
listRowCounts.Add(Convert.ToInt32(outputparm.Value));
请在下面找到:
出现错误的脚本任务代码部分&
存储过程代码部分,我们通过它导出行数并将其设置在 OUTPUT 变量中
在代码下方的脚本任务中,创建了多个日志记录点,以了解代码运行到什么时候 -
// Write to sysout
Console.WriteLine("Test<NUMBER>");
--||--**-- --||--**-- --||--**-- --||--**-- --||--**--
SCRIPT code part for WriteData only--
public void WriteData(string strExamRegionCode, string strdivisionCode, string strResultFileName, DateTime dtmStdDate)
{
SqlConnection conn = new SqlConnection(strConnectionStringLog);
SqlCommand cmd = null;
SqlDataReader rdr = null;
try
{
//Open the connection
conn.Open();
// Create the command
cmd = new SqlCommand("usp_Exam_ResultManifest_ProcList_Get", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@Exam_region_code", strExamRegionCode));
cmd.Parameters.Add(new SqlParameter("@Exam_division_code", strdivisionCode));
cmd.Parameters.Add(new SqlParameter("@Result_file_name", strResultFileName));
// Return the resultset
rdr = cmd.ExecuteReader();
// Write to sysout
Console.WriteLine("Test12");
// Fail if no rows returned
if (!rdr.HasRows)
{
// Log error to database
string strCustomMessage = "No data returned by calling stored procedure usp_Exam_ResultManifest_ProcList_Get with parameters: " + strExamRegionCode + ", " + strdivisionCode + ", " + strResultFileName + ", " + dtmStdDate.ToString("yyyyMMdd");
LogCustomMessage(strConnectionStringLog, "OnError", strMachineName, strUserName, strPackageName, strPackageID, strExecutiondivisionGUID, strContainerStartTime, 100, strCustomMessage);
// Write to sysout
Console.WriteLine(strCustomMessage);
// Fail the package - error will be written to table sysssislog
throw new MyAppException(strCustomMessage);
}
else
{
// Call the Result Get stored procedure(s) to retrieve the Result data
while (rdr.Read())
{
// Get the stored procedure name
string strResultGetStoredProcedureName = rdr["Result_get_storedprocedure_name"].ToString();
// Write to sysout
Console.WriteLine("Test13");
try
{
SqlConnection connFDA = new SqlConnection(strConnectionStringResult);
SqlCommand cmdFDA = null;
SqlDataReader rdrFDA = null;
//Open the connection
connFDA.Open();
// Run the sproc to return the Result data
cmdFDA = new SqlCommand(strResultGetStoredProcedureName, connFDA);
cmdFDA.CommandType = CommandType.Text;
SqlParameter parm1 = new SqlParameter("@Std_date", SqlDbType.DateTime);
parm1.Value = dtmStdDate;
parm1.Direction = ParameterDirection.Input;
cmdFDA.Parameters.Add(parm1);
SqlParameter parm2 = new SqlParameter("@Exam_Source_Section_division", SqlDbType.VarChar);
parm2.Value = strdivisionCode;
parm2.Direction = ParameterDirection.Input;
cmdFDA.Parameters.Add(parm2);
SqlParameter outputparm = new SqlParameter("@rows_returned", SqlDbType.Int);
outputparm.Direction = ParameterDirection.Output;
outputparm.Size = int.MaxValue;
cmdFDA.Parameters.Add(outputparm);
// Write to sysout
Console.WriteLine("Test14");
if (Dts.Variables["strForceRecompileObjects"].Value.ToString().Contains(strResultGetStoredProcedureName))
{
cmdFDA.CommandText = cmdFDA.CommandText + " @Std_date, @Exam_Source_Section_division, @rows_returned OUT WITH RECOMPILE;";
// Write to sysout
Console.WriteLine("Test15");
}
else
{
cmdFDA.CommandText = cmdFDA.CommandText + " @Std_date, @Exam_Source_Section_division, @rows_returned OUT;";
// Write to sysout
Console.WriteLine("Test16");
}
// Result file generation timeout issue
cmdFDA.CommandTimeout = 1600;
// Write to sysout
Console.WriteLine("B4_cmdFDA_Execution");
// Return the resultset
rdrFDA = cmdFDA.ExecuteReader();
// Write to sysout
Console.WriteLine("AFTER_cmdFDA_Execution");
if (rdrFDA.HasRows)
{
// Write to sysout
Console.WriteLine("rdrFDA has rows.");
}
else
{
// Write to sysout
Console.WriteLine("rdrFDA has NO rows.");
}
// Write to sysout
Console.WriteLine("TT");
// Write to sysout
Console.WriteLine("Test17");
try
{
// Loop through the Result data and write to the file
while (rdrFDA.Read())
{
// Write the row data to the file
string strRowData = rdrFDA["row_data"].ToString();
// Write to sysout
//Console.WriteLine("Test18");
int intControlId = Convert.ToInt32(rdrFDA["Result_control_id"]);
if (!listControlIds.Contains(intControlId))
{
listControlIds.Add(intControlId);
// Write to sysout
Console.WriteLine("Test19");
}
WriteFile(strRowData);
// Write to sysout
//Console.WriteLine("Test20");
}
}
catch (Exception ex)
{
// Log error to database
string strCustomMessage = "Error rdrFDA.Read - stored procedure " + strResultGetStoredProcedureName + " with parameters: " + dtmStdDate.ToString("yyyyMMdd") + ", " + strdivisionCode + ", Error: " + ex.Message;
LogCustomMessage(strConnectionStringLog, "OnError", strMachineName, strUserName, strPackageName, strPackageID, strExecutiondivisionGUID, strContainerStartTime, 100, strCustomMessage);
// Write to sysout
Console.WriteLine(strCustomMessage);
// Fail the package - error will be written to table sysssislog
throw;
}
// Close the reader
rdrFDA.Close();
// Write to sysout
Console.WriteLine("Test21");
// Write to sysout
Console.WriteLine("abc");
Console.WriteLine("xyz" + Convert.ToString(outputparm.Value));
Console.WriteLine("def");
// Keep track of row counts - for the trailer row (MUST be after closing the reader)
listRowCounts.Add(Convert.ToInt32(outputparm.Value));
// Write to sysout
Console.WriteLine("Test22");
// Close the connection
connFDA.Close();
}
catch (Exception ex)
{
// Log error to database
string strCustomMessage = "Error retrieving data for writing to the output file - stored procedure " + strResultGetStoredProcedureName + " with parameters: " + dtmStdDate.ToString("yyyyMMdd") + ", " + strdivisionCode + ", Error: " + ex.Message;
LogCustomMessage(strConnectionStringLog, "OnError", strMachineName, strUserName, strPackageName, strPackageID, strExecutiondivisionGUID, strContainerStartTime, 100, strCustomMessage);
// Write to sysout
Console.WriteLine(strCustomMessage);
// Fail the package - error will be written to table sysssislog
throw;
}
}
rdr.Close();
conn.Close();
}
}
catch (Exception ex)
{
// Log error to database
string strCustomMessage = "Error retrieving data for writing to the output file with parameters: " + strExamRegionCode + ", " + strdivisionCode + ", " + strResultFileName + ", " + dtmStdDate.ToString("yyyyMMdd") + ", Error: " + ex.Message;
LogCustomMessage(strConnectionStringLog, "OnError", strMachineName, strUserName, strPackageName, strPackageID, strExecutiondivisionGUID, strContainerStartTime, 100, strCustomMessage);
// Write to sysout
Console.WriteLine(strCustomMessage);
// Fail the package - error will be written to table sysssislog
throw;
}
}
--||--**-- --||--**-- --||--**-- --||--**-- --||--**--
--||--**-- --||--**-- --||--**-- --||--**-- --||--**--
---Stored proedure code where setting OUTPUT parameter value ONLY: -
CREATE PROCEDURE [dbo].[p_result_get_SchoolItems_exam_v18]
@std_date DATETIME = NULL,
@exam_Source_Section_division VARCHAR(10) = NULL,
@rows_returned INT OUTPUT,
@debug TINYINT = 0
WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
/*
** Declare and set error tracking and debugging variables
*/
DECLARE @ProcName sysname,
@Error int,
@Raiserror int,
@CustomErrorSeverity int ,
@CustomErrorState int,
@ErrorSeverity int ,
@ErrorState int,
@Msg varchar(255),
@Rowcount int,
@RowCnt int;
SET @ProcName = object_name(@@procid);
SET @Error = 0;
SET @Raiserror = 0;
SET @Msg = '';
SET @Rowcount = 0;
SET @RowCnt = 0;
SET @CustomErrorSeverity = 11;
SET @CustomErrorState = 1;
/*
** Declare variables used to implement procedure specific functionality
*/
DECLARE @default_date datetime;
DECLARE @working_date datetime;
DECLARE @exam_region_code varchar(10);
DECLARE @SchoolID varchar(8);
DECLARE @result_control_id int;
SELECT @default_date = '29991231';
BEGIN TRY
IF (@debug>=1) PRINT @ProcName + ' : ' + convert(varchar(30),getdate(),109) + ': Entering procedure ...';
--To avoid NULL/DBNULL issues coming in SSIS Package execution due to below direct SET via @@ROWCOUNT; added same Query as above but with COUNT(1) only.
--SET @rows_returned = @@ROWCOUNT;
SELECT @RowCnt = COUNT(1)
FROM dbo.t_result_SchoolItems_exam result
JOIN dbo.t_result_VerificationList_exam con
ON result.result_control_id = con.result_control_id
AND con.exam_division_code = result.exam_division_code
JOIN dbo.t_result_name_exam n
ON con.result_name_id = n.result_name_id
JOIN dbo.t_result_Active_Verification_id_exam curr
ON con.result_control_id = curr.result_control_id
AND curr.exam_division_code = result.exam_division_code
WHERE n.result_name = 'PatternD book'
AND con.exam_region_code = @exam_region_code
AND con.exam_bus_date = @std_date
AND result.exam_division_code = @exam_Source_Section_division
--ORDER BY result.system_id, result.Roll_ID, result.Ce_value_local_ledgerK, result.due_local_ledgerK, result.cash_amount_local_ledgerK
OPTION (RECOMPILE);
SET @rows_returned = @RowCnt;
END TRY
BEGIN CATCH
SELECT @Raiserror = 300000 + error_number() ,
@ErrorSeverity = error_severity() ,
@ErrorState = error_state() ,
@Msg = @ProcName + ': ' + isnull ( error_message() , @Msg ) + ' , Error Number = ' + isnull ( convert ( varchar , error_number()) , '' )
+ ' , Error Line = ' + isnull ( convert ( varchar , error_line()) , 'N/A' );
RAISERROR (@Msg, @ErrorSeverity, @ErrorState);
RETURN @Raiserror;
END CATCH;
GO
--||--**-- --||--**-- --||--**-- --||--**-- --||--**--
解决方案
在没有看到脚本任务和存储过程的代码的情况下,很难具体诊断出什么是错误的,但我会接受一些破解。
在您的脚本任务中,您正在运行一个存储过程,它返回一个结果集以及一个输出参数。在某个地方,您有一个来自存储过程的 NULL。数据库“东西”中的 NULL 被转换为 .NET 框架内的复杂类型。我不能写
int i = null;
因为我的变量 i 是原始类型。它必须有一个有意义的值。我可以写
int? i = null;
因为?
表明这是一个可为空的类型。SQL 语言中没有这种复杂性,因此很乐意将 null 分配给变量。
DECLARE @i int = NULL;
在您的脚本任务中,您需要检查您尝试访问 DataRow/DataTable/DataSet 对象的所有位置以及输出参数的 Parameters.Value 属性。
为什么它在第一次之后起作用?
我不能说。也许存储过程中内置了“获取自上次执行以来的所有数据”的逻辑,并且新运行在某些字段中没有 NULL。这也可以解释为什么文件被部分填充。如果您在访问数据库时弄清楚该行在数据库中的位置,您可能会发现 NULL 坐在那里。
参考阅读
推荐阅读
- javascript - 使用包装器在 Python/Javascript 中模仿传递引用 - 好的做法?
- r - 在大型分箱数据集上使用“ggplot”时的内存泄漏
- wordpress - 利用浏览器缓存使用 .htaccess
- java - 正则表达式查找带方括号的字符串并替换
- ubuntu-14.04 - iopl() 间歇性失败
- java - Android - 如何在新 API 中请求位置更新?
- laravel - 具有 Laravel 权限的 Vue.js
- reactjs - 想要使用在 ReactJS 中具有某些特定功能的 Calendar
- c# - 如何从 XmlNodeList 中删除特定项目
- python - 将 3 个不同 For 循环的结果显示到表格中