I have an issue which is causing my SSIS package failure in its first execution, while on 2nd onward execution the issue is automatically resolved and the SSIS package is executed successfully.
Project: Migrating a package
- SQL Server 2008 R2 to SQL Server, SSDT 2016
- Visual Studio 2008 to Visual Studio 2015
Case:
One of my migrated SSIS packages which has a script task to:
- Execute stored procedure with one OUTPUT parameter to get rows count
- Write the returned rows to a txt file in CSV format.
The stored procedure get executed under this script tasks; returns (thousands, millions of rows) along with one OUTPUT parameter which contains rows count return by the stored procedure.
Problem:
This SSIS Package is failing randomly on 1st execution with error -
"Object cannot be cast from DBNULL to other types"
and on its 2nd execution is executed successfully.
When we Google this error, the most common solution found is to check the Variable value first: whether it's an INT value, and has any value, and then only converts.
However, this solution does not apply here, as when I execute the SSIS package a 2nd time, it gets executed successfully.
One more thing to note here is that when the SSIS Package randomly fails, it writes the CSV file with an incomplete row, and on 2nd execution this issue is also resolved.
It looks like the above error is not actual error and due to unknown reasons not giving actual error.
Is it due to any configuration missing on SSIS / SQL level or any other issue?
Please find the SCRIPT & stored procedure code below.
Every random failure caught at below line of SCRIPT code. However, on 2nd run no such issue appeared. So, looks like its not a conversion issue but more towards 2016 configuration or some different way of coding issue -
listRowCounts.Add(Convert.ToInt32(outputparm.Value));
Please find below the:
script task code part under which the error is coming &
Stored procedure code part by which we are deriving the rows count and setting it in OUTPUT variable
In Script task below code, there are multiple logging points created to know till what point the code is running as-
// 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
--||--**-- --||--**-- --||--**-- --||--**-- --||--**--
