Programmatically creating a package with a SAP .NET data provider: AcquireConnections fails
I'm trying to programmatically alter a package which uses a SAP .NET connector. When configuring and running the package from BIDS everything goes well. But when i try to call the AcquireConnections(vbNull) method from code , the following exception is thrown: Unable to cast COM object of type 'System.__ComObject' to interface type 'System.Transactions.IDtcTransaction'. This operation failed because the QueryInterface call on the COM component for the interface with IID '{0FB15084-AF41-11CE-BD2B-204C4F4F5020}' failed due to the following error: No such interface supported (Exception from HRESULT: 0x80004002 (E_NOINTERFACE)). I've used the following code: 'Load template package Dim SSIS As Application = New Application() Dim Package As Package = SSIS.LoadPackage(TemplatePackage, Nothing) 'Load dataflow Dim Dftask As Executable = Package.Executables("DFT xxx") Dim thMainPipe As TaskHost = CType(Dftask, TaskHost) 'Configure the ADO.NET Source component Dim dataFlowTask As MainPipe = CType(thMainPipe.InnerObject, MainPipe) Dim SourceComponent As IDTSComponentMetaData100 = dataFlowTask.ComponentMetaDataCollection("Dummy source") 'Get the design time instance of the OLE db source component. Dim srcDesignTimeComponent As IDTSDesigntimeComponent100 = SourceComponent.Instantiate() 'Set properties srcDesignTimeComponent.SetComponentProperty( "SqlCommand", "SELECT * from " + Table) ' Set the connection manager SourceComponent.RuntimeConnectionCollection(0).ConnectionManager = DtsConvert.GetExtendedInterface(Package.Connections("SAP")) SourceComponent.RuntimeConnectionCollection(0).ConnectionManagerID = Package.Connections( "SAP").ID 'Retrieve the column metadata srcDesignTimeComponent.AcquireConnections(vbNull) srcDesignTimeComponent.ReinitializeMetaData() srcDesignTimeComponent.ReleaseConnections()
October 22nd, 2010 2:19am

Quick check - is your project .Net Framework 3.5? 4.0 seems not to work.James Beresford @ www.bimonkey.com SSIS / MSBI Consultant in Sydney, Australia SSIS ETL Execution Control and Management Framework @ SSIS ETL Framework on Codeplex
Free Windows Admin Tool Kit Click here and download it now
October 22nd, 2010 4:17am

I've seen this before when the connector is not found, perhaps the .dll is missing? If it is not missing, try deregistering the dll and then register it again.
October 22nd, 2010 4:19am

I suggest you un-install and re-install the driver for SAP.Arthur My Blog
Free Windows Admin Tool Kit Click here and download it now
October 22nd, 2010 4:22am

I tried the .Net framework 3.5. (first had to fix the problem with the wrong version of Microsoft.SQLServer.msxml6_interop.dll, see https://connect.microsoft.com/VisualStudio/feedback/details/551532/cannot-build-project-in-2010-when-project-has-reference-to-ssis-2008-dlls-sqltask-manageddts-etc?wa=wsignin1.0 and http://blogs.msdn.com/b/jason_howell/archive/2010/08/18/visual-studio-2010-solution-build-process-give-a-warning-about-indirect-dependency-on-the-net-framework-assembly-due-to-ssis-references.aspx) But unfortunately srcDesignTimeComponent.AcquireConnections(vbNull) throws the exception InvalidCastException was unhandled Unable to cast object of type 'Microsoft.VisualBasic.VariantType' to type 'System.Transactions.IDtcTransaction'.
October 22nd, 2010 10:22am

Try - srcDesignTimeComponent.AcquireConnections(Nothing) http://www.sqlis.com | http://www.konesans.com
Free Windows Admin Tool Kit Click here and download it now
October 22nd, 2010 11:32am

Unbelievable, that was it! vbNull works fine on AcquireConnections on an OLEDB source but somehow it doesn't on an ADO source. thanks a bunch! :-)
October 22nd, 2010 3:01pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics