Hi,
This is regarding one random issue which causing the SSIS package failure in first execution and on 2nd onward execution the issue automatically resolved and SSIS package executed successfully.
Project: -
Migrating: -
-
SQL Server 2008 R2 to SQL Server, SSDT 2016 &
-
Visual Studio 2008 to Visual Studio 2015
Case: -
One of migrated SSIS package which has a script task to: -
-
Execute Store Procedures having one OUTPUT Parameter to get Rows count.
-
Write the returned rows to a txt file in CSV format.
The Store Procedures get executed under this script tasks; returns (thousands, millions of rows) along with one OUTPUT parameter which contains Rows count return by Store Procedure.
Problem: -
This SSIS Package is failing randomly on 1st execution with error -
"Object cannot be cast from DBNULL to other types"
& on 2nd execution executed without this error & successfully.
When we Google this error online than the most common solution found is that to check the Variable value first whether it's a INT value and has any value and then only converts.
However, this solution not applies here as when we execute the SSIS package 2nd time then it gets executed successfully.
One more thing to note here is that when the SSIS Package randomly fails then it writes the CSV file with incomplete row also & on 2nd execution this also resolved.
Could anyone please advice how to solve this issue where
- The SSIS package is failing randomly on 1st execution with incomplete rows output in CSV file and
failing with "Object cannot be cast from DBNULL to other types".
Here, 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?