Script component unable to acquire existing database connection

• ErickTreeTops

  Ten Centuries

  Points: 1253

  More actions

  November 17, 2015 at 5:32 pm

  #148855

  I'm trying to re-use a connection from my package inside a "Source" script component.

  public override void AcquireConnections(object Transaction)

  {

  try

  {

  connMgr = this.Connections.Connection;

  sqlConn = (SqlConnection)connMgr.AcquireConnection(null);

  }

  catch (Exception ex)

  {

  throw;

  }

  }

  I then use the connection like this

  public override void PreExecute()

  {

  try

  {

  base.PreExecute();

  using (SqlDataAdapter da = new SqlDataAdapter())

  {

  da.SelectCommand = new SqlCommand(Variables.GetSAPDetailsStoredProc.ToString(), sqlConn);

  SqlParameter p = new SqlParameter("@List", Variables.FundingSourcesToProcess);

  da.SelectCommand.Parameters.Add(p);

  da.SelectCommand.CommandType = CommandType.StoredProcedure;

  DataSet ds = new DataSet();

  da.Fill(ds, "result");

  HDR = ds.Tables[0];

  HTL = ds.Tables[1];

  Item = ds.Tables[2];

  DateTimeStamp = Variables.DateTimeStamp;

  }

  }

  catch (Exception ex)

  {

  throw;

  }

  When I run this inside Visual studio but pointing at our test database it works perfectly.

  I then deploy the package onto our Test server where the test database sits. and the code stops working. The error is a bit obscure

  > System.NullReferenceException: Object reference not set to an instance of an object.

  at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.HandleUserException(Exception e)

  at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.PreExecute()

  at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostPreExecute(IDTSManagedComponentWrapper100 wrapper)

  But from what I can understand my SQLconn object is null. Meaning it did not acquire a connection on the test server.

  Any ideas why this would behave different when running in SSISDB then when running from visual studio ?