0

I've created a custom connection manager for SSDT BI (SSIS) which will connect to an Oracle database using Oracle's managed data access library (Oracle.ManagedDataAccess.dll).

On my development machine, this connection manager, and the custom "data source" pipeline component which goes with it, will work fine. When I deploy the package to the server and try to run it, the package will always fail with an "AccessViolationException".

The server is running Windows Server 2012R2 with Sql Server 2014

So far, debugging hasn't give me much info. When I step though my code, my empty, default, constructor gets called but nothing else. As soon as it exits the constructor I get the exception. No other methods within my custom class are ever called.

Here's the information which is dumped to the windows event log.

Application: ISServerExec.exe Framework Version: v4.0.30319 Description: The process was terminated due to an unhandled exception. Exception Info: System.AccessViolationException Stack: at Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSPackagePersist100.LoadPackageFromXML(System.Object, Boolean, Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSEvents100) at Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSPackagePersist100.LoadPackageFromXML(System.Object, Boolean, Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSEvents100) at Microsoft.SqlServer.Dts.Runtime.Package.LoadFromXML(System.String, Microsoft.SqlServer.Dts.Runtime.IDTSEvents) at Microsoft.SqlServer.Dts.Runtime.Project.LoadPackage(Microsoft.SqlServer.Dts.Runtime.IProjectStorage, Microsoft.SqlServer.Dts.Runtime.Package, System.String, Microsoft.SqlServer.Dts.Runtime.IDTSEvents) at Microsoft.SqlServer.Dts.Runtime.PackageItem.Load(Microsoft.SqlServer.Dts.Runtime.IDTSEvents) at Microsoft.SqlServer.Dts.Runtime.PackageItem.LoadPackage(Microsoft.SqlServer.Dts.Runtime.IDTSEvents) at Microsoft.SqlServer.IntegrationServices.Server.ISServerExec.ISServerExecutionEvents.LoadPackage(Microsoft.SqlServer.Dts.Runtime.PackageItem) at Microsoft.SqlServer.IntegrationServices.Server.ISServerExec.ProjectOperator.StartPackage() at Microsoft.SqlServer.IntegrationServices.Server.ISServerExec.ProjectOperator.PerformOperation() at Microsoft.SqlServer.IntegrationServices.Server.ISServerExec.ExecuteMain.Main(System.String[])

jamiet
  • 10,501
  • 14
  • 80
  • 159
nitedmn
  • 94
  • 10

2 Answers2

0

I've run into a similar issue before. The resolution was the sql server agent not having correct permissions to use the dll file.

Possible Fixes:

  • Check the location of the dll on the server.
  • Confirm permissions to use the dll.

Sorry I can't provide more precise fixes, the DBAs at my site ended up fixing it for me and this was the summary they gave me after.

  • I gave the service account, used to run all .dtsx packages, full admin access to the server and still have the same issue. Is there any chance you can find out exactly what was changed? – nitedmn Mar 02 '15 at 20:47
  • I asked my DBA, he replied: "A dll was not registered in the hosting SQL Server. There was a native installation that had to be carried out for the source system the C# code was accessing." –  Mar 02 '15 at 21:22
  • Sorry. Not really. I configured the account, the test package runs as, with full administrator access to the server and I still have the same problem. I would think that this would solve any permissions issues. – nitedmn Mar 04 '15 at 21:37
0

can you sketch for me the outline of the SSIS package?

I have a hunch that this problem can occur when nesting Sequence Container objects in SSIS 2008. When you create a package with SQL Business Intelligence Development Studio 2008, and nest Sequence Containers, and then upgrade the package to SSIS 2012 or greater, and then open the package in SQL Server Data Tools 2012 or greater, the package will not display the same way as it did in 2008. The outer sequence container will not be attached to anything.

John Zabroski
  • 2,212
  • 2
  • 28
  • 54