SSIS package runs fine manually but throws error via JOB

Question

SSIS Package Info
I have a package which manipulate some data(received from an API), writes it into SQL Server Table and also converts it to ADODB Recordset to send it remotely to SAP. The package has no SQL or file connections (I'm connecting and writing into the database via c# script).

Problem
Now, this package runs without error(writes the results into SQL Database Table and Send it to SAP) when I run it from Data Tools. When I deploy it to SQL Server and assign this to a job and run it, it fails.
The error is: The Execute method for the task has the error code 0x80004003 (The value can not be NULL. Parameter name: adodb) returned.
The package runs till the step where it writes the data successfully to SQL table. The error is when it tries to send the data to SAP remotely with ADODB Recordset, which it apparently finds null.

What I tried so far
I have tried building it fresh and deploying it. It had no effect.
I have also changed the protection level of the project which has also no effect on it.
Since I have no SQL connection, the most available answers on SO to this problem are of no help.

More Detail
The entire main process is inside a Script Task. Inside this task, the result of the API is written to the SQL Table:

            string SqlQuery= "INSERT INTO xxx (xx, xx)" +
                                    "VALUES(" +
                                    "'" + val1 + "'," +
                                    "'" + val2 + "')";
            try
            {
                SqlConnection con = new SqlConnection(
                                 "Data Source=xxx;Initial Catalog=xxx;User ID=xx;Password=xx");
                con.Open();

                SqlCommand cmd = new SqlCommand(SqlQuery, con);
                cmd.ExecuteNonQuery();
                con.Close();
            }
            catch (Exception e)
            {

            }

After this, the value (val1, val2) is converted into recordset:

            ADODB.Recordset result = new ADODB.Recordset();
            ADODB.Fields resultFields = result.Fields;

            resultFields.Append("VAL1_LINE",
                                ADODB.DataTypeEnum.adVarChar,
                                255,
                                ADODB.FieldAttributeEnum.adFldIsNullable,
                                null);

            result.Open(System.Reflection.Missing.Value
                    , System.Reflection.Missing.Value
                    , ADODB.CursorTypeEnum.adOpenStatic
                    , ADODB.LockTypeEnum.adLockOptimistic, 0);


            foreach (string row in stringVar.Split('\n'))
            {
                result.AddNew(System.Reflection.Missing.Value,
                              System.Reflection.Missing.Value);
                resultFields[0].Value = Regex.Replace(row, @"\t", "");
            }

After this, the recordset is sent to the RFC function for SAP.
I don't think that the code above helps as the code has no problem running manually. Also, the script task has no variables.
Any help would be appreciated.

It would be helpful if you could provide screenshots or details on the parameter, how it is created and how it is passed to the script task. — Joe C, Aug 22 '19 at 12:38
Where in the package is there a parameter named `adodb`? I don't see it in any of your code or description? — Tab Alleman, Aug 22 '19 at 13:16
@TabAlleman Yeah there is no parameter like that at all. I think the error means the ADODB object variable is null. But I'm only assuming this. I can confirm that there is nothing called ADODB as I developed this package from scratch. — SSharma, Aug 22 '19 at 13:19
You might get a more meaningful error if you got rid of the try/squelch code. Having a `try` with an empty `catch` block means "don't tell me if there's an error. just pretend nothing's wrong". — Tab Alleman, Aug 22 '19 at 14:27
How have you deployed the package to the server (file system, ssisdb?). In my years of experience with SSIS - if it works fine from Visual Studio/Data Tools but throws errors when running as a job it's almost always permission based. Also, what version Visual Studio/Data Tools, what version of SQL Server? — LaraRaraBoBara, Aug 22 '19 at 17:37
@TabAlleman I tried that, it gave me an error in SSMS 'An exception has occurred'. — SSharma, Aug 23 '19 at 07:08
@FembotDBA This time it was not the case of permission. The problem was with the properties of the assembly reference for adodb. I have added an answer below. My problem is resolved now. — SSharma, Aug 26 '19 at 08:39

score 1 · Accepted Answer · answered Aug 26 '19 at 08:37

I could find the error this way:

    string text = e.Message.ToString(); 
    string path = @"path\logFile.txt";
    System.IO.File.AppendAllText(path, text);

I added the above code in all try&catch and found the error to be 'ADODB.FieldsToInternalFieldsMarshaler' in the assembly yxz could not be loaded..
This led me to SO Link.
All I did is: Scipt Task > References > adodb > Right click and click properties > Embed Interop Types > False. This solved my problem.

SSIS package runs fine manually but throws error via JOB

1 Answers1