Trying to do a update/insert from SQL 2005 query to Access 2003 linked table.
In the Script Transformation I get this error.
Unable to cast COM object of type 'System.__ComObject' to class type 'System.Data.OleDb.OleDbConnection'. Instances of types that represent COM components cannot be cast to types that do not represent COM components; however they can be cast to interfaces as long as the underlying COM component supports QueryInterface calls for the IID of the interface.
Destinatoin Oledb connection is Native OLEDB Jet 4 to Access 2003 database.
Private sqlConn As OleDb.OleDbConnection
Private sqlCmd As OleDb.OleDbCommand
Private sqlParam As OleDb.OleDbParameter
Private connstring As String
Public Overrides Sub AcquireConnections(ByVal Transaction As Object)
connMgr = Me.Connections.ConnectionOLE
'sqlConn = CType(connMgr.AcquireConnection(Nothing), SqlConnection)
connstring = connMgr.ConnectionString
sqlConn = CType(connMgr.AcquireConnection(Nothing), OleDb.OleDbConnection)
End Sub
Any help would be appreciated.
The OLEDB connection manager is not using managed OleDb classes but the native OleBD interfaces. AcquireConnection returns a COM object that is created from IDBCreateSession::CreateSession call.
HTH,
Ovidiu Burlacu
You are correct. The destination connection was using the Native OLE DB for Jet 4.
I created a Data Source using .NET OLE DB provider for Jet 4 and I got connected in the Script Component OK.
New Problem.
My insert OLE DB command worked fine which uses the original destination connection (Native), But my update OLE DB command using same Native connection is erroring out on each input row of the OLE DB Command component.
[UpdateRow [2727]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80004005 Description: "Numeric field overflow.".
Connection string on Destination OLE DB connection is:
Data Source=C:\Projects\Data\TSLists.mdb;Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;OLE DB Services=-1;
UPDATE: The table in the Access database I am trying to update is a linked table representing a SharePoint 2003 list. What is interesting is you can push (insert) data into this table, but you can not update data in the same linked table from the same OLE DB connection in SSIS.
