Accessing OLEDB Connection Managers in a Script

Tested results

iSeries Driver Version 5 Release 3 SQL Server 9.0.3042 (SP2) Do not work
iSeries Driver Version 5 Release 4 SQL Server 9.0.4226 (SP3) Work

Error related to the V5R3
http://www-01.ibm.com/support/docview.wss?uid=nas2a802b6eb29d32cac8625726c0041efc9

http://technet.microsoft.com/en-us/library/ms135939(SQL.90).aspx

With ADO.Net connection manager, I can use ODBC destination in SSIS (2005) to access AS400.  The problem is the driver is not stable.  It works fine when I push about 10 columns with 10 rows.  When I try to push 40 columns and 100 rows, the error “wrong number of parameters” fired at the second rows.  The first row went in fine.  I checked the ODBC log.  It seems to me the driver did not send the parameters.

[Script Component [175]] Error: System.Data.Odbc.OdbcException: ERROR [07002] [IBM][iSeries Access ODBC Driver]Wrong number of parameters.   

The good news is the “Native OLE DB\IBM DB2 UDB for iSeries IBMDASQL OLE DB Provider” works and now available in Standard version.  The problem is it is too slow.

The following is the configuration for a similar driver.

http://social.msdn.microsoft.com/Forums/en/sqlintegrationservices/thread/4128e62f-92d1-4125-baa3-fdd9fed59d48

1.     Create a Data Source for the AS/400, using provider NATIVE OLE DB\IBM DB2 UDB FOR ISERIES IBMDA400 OLE DB PROVIDER, WITH THE FOLLOWING CHANGES

  • · CATALOG LIBRARY LIST is the AS/400 library
  • · USER ID should be populated
  • · PASSWORD should be populated
  • · PERSIST SECURITY INFO should be TRUE
  • · DATA SOURCE should be your machine DNS name (tho IP might work)
  • · INITIAL CATALOG should be the actual machine name (mine is populated when I click on the dropdown)

2.     Create a Connection Manager for the above data source

3.     Create an OLE DB source/destination…

  • · OLE DB Connection Manager from above
  • · Data access mode = Table or View (or SQL Command for a OLE DB Source)
  • · Name of Table/View should be  …  If you config is correct, this dropdown will contain a list of them to select from…
  • · On the ‘Advance Editor’ screen, under Component Properties, set
    • o   VALIDATE EXTERNAL METADATA = FALSE
    • o   ALWAYS USE DEFAULT CODE PAGE = TRUE  
  • · We had to turn on JOURNALLING on the DB2 tables to write to them.  (This may or may not be a requirement…)

—- sample codes for ODBC destination by using the Script Component—-

Imports System 
Imports System.Data 
Imports System.Data.Odbc 
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper 
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Public Class ScriptMain 
    Inherits UserComponent

    Private m_cnn_mgr As IDTSConnectionManager90 
    Private m_cnn As OdbcConnection 
    Private m_cmd As OdbcCommand

    Public Overrides Sub AcquireConnections(ByVal Transaction As Object) 
  m_cnn_mgr = Me.Connections.destination 
  m_cnn = CType(m_cnn_mgr.AcquireConnection(Nothing), OdbcConnection) 
    End Sub

    Public Overrides Sub PreExecute() 
  m_cmd = New OdbcCommand("INSERT INTO " + Variables.PREFIX + "POSDTL(id, ......) VALUES(?, ......)", m_cnn)
  m_cmd.Parameters.Add(New OdbcParameter("@id", OdbcType.VarChar, 2)) 
  ...... 
    End Sub

    Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer) 
  With m_cmd 
  .Parameters("@id").Value = Row.id 
  ......
  .ExecuteNonQuery() 
  End With 
    End Sub

    Public Overrides Sub ReleaseConnections() 
  m_cnn.Close() 
    End Sub 
End Class

—- sample codes for OLE DB destination by using the Script Component —-

http://blogs.msdn.com/mattm/archive/2008/08/22/accessing-oledb-connection-managers-in-a-script.aspx

Dim cm As ConnectionManager 
Dim cmParam As Wrapper.IDTSConnectionManagerDatabaseParameters90 
Dim conn As OleDb.OleDbConnection

cm = Dts.Connections("oledb") 
cmParam = CType(cm.InnerObject, Wrapper.IDTSConnectionManagerDatabaseParameters90) 
conn = CType(cmParam.GetConnectionForSchema(), OleDb.OleDbConnection)

—- ADO.NET destination by using Script Component —-

http://msdn.microsoft.com/en-us/library/ms135939(v=SQL.90).aspx#Y1920

 

—– ODBC .NET Provider (.NET 1.0) ——————-

http://support.microsoft.com/kb/310988

 

—– IBM iSeries Access SP history ——————-

http://www-03.ibm.com/systems/i/software/access/windows/sphist.html

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s