Connection Manager not getting Iniialized

Question

Hi,

I'm currently writing a C# code in Script Component which is making a connection to MSSQL DB using ADO.NET Connection Manager. But it returns a NullReferenceException every time when I run it. I have tried the sample code as mentioned in the link
https://learn.microsoft.com/en-us/sql/integration-services/extending-packages-scripting-data-flow-script-component-types/creating-a-destination-with-the-script-component?view=sql-server-ver16

but it still fails.

I have provided the basic code for reference.

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Net;
using System.Text;
using System.Threading;
using Microsoft.SqlServer.Dts.Pipeline;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
    private Dictionary<String, SqlDbType> columnTypeMapping;
    private SqlConnection rawConnection;
    IDTSConnectionManager100 connMgr;
    
    public override void PreExecute()
    {
        columnTypeMapping = GetColumnTypeMapping(this.Variables.MappingString);
    }
    private static Dictionary<string, SqlDbType> GetColumnTypeMapping(String mapString)
    {
        Dictionary<String, SqlDbType> columnTypeMapping = new Dictionary<String, SqlDbType>();
        foreach (var pair in mapString.Split(','))
        {
            var keyValue = pair.Split(':');
            SqlDbType value = (SqlDbType)Enum.Parse(typeof(SqlDbType), keyValue[1]);
            columnTypeMapping.Add(keyValue[0], value);
        }
        return columnTypeMapping;
    }
    public override void AcquireConnections(object Transaction)
    {
        connMgr = this.Connections.destinationDb;
        rawConnection = (SqlConnection)connMgr.AcquireConnection(null);
    }
    public override void ReleaseConnections()
    {
        connMgr.ReleaseConnection(rawConnection);
    }
    
    public override void Input0_ProcessInputRow(Input0Buffer Row)
    {
        String keyPosition = "";
        bool fireAgain = true;
        //SqlTransaction transaction = null;
        try
        {
 			//Doing SOmething here
            
        }
        catch (System.NullReferenceException NPE)
        {
            this.ComponentMetaData.FireError(25, this.ComponentMetaData.Name, "Null POINTER at " + keyPosition + " - " + NPE.StackTrace, string.Empty, 0, out fireAgain);
            //transaction.Rollback();
            throw;
        }
        catch (Exception ex)
        {
            this.ComponentMetaData.FireError(0, this.ComponentMetaData.Name, keyPosition + " - " + ex.StackTrace, string.Empty, 0, out fireAgain);
            //transaction.Rollback();
            throw;
        }
    }
}

Screenshot of error i get.

Any Help will be appreciated.

Answer 1

but it still fails.

Debug your code to see on which line of code the exception raises.

Answer 2

Hi @Mittal, Umang ,

The error message indicates a NullReferenceException in your SSIS Script Component, which usually happens when an object being referenced is not instantiated (null).

Here are a few things in your code that could cause this error, it is recommended that you use debug mode to determine exactly where the error occurred.

1. Connection Object Not Initialized:
   • Ensure that AcquireConnections is properly initializing the connection object. It might not be acquiring the connection correctly from this.Connections.destinationDb.
2. Uninitialized Variables or Missing Mapping String:
   • The PreExecute method assumes that this.Variables.MappingString exists. If it is not properly set in the SSIS package, it will cause the mapping to be null or empty, resulting in issues when splitting strings.

Best Regards.

Jiachen Li

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment". Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.