none
SSIS_SAP Connection RRS feed

  • Question

  • Hi All,

    I am facing a issue given below.

    I have a SSIS Package where I am using a Script Component for transferring the data from SAP Server to my SQL Server 2005. In my SSIS I have SAP Components to use SAP Connection, SAP Command and SAP Data reader Objects.

    The requirement is to Pass a Table parameter to the SAP Command and retrieve the result set and store into a table. But it is throwing me an error: RFC Communication Error. Error Message 'Data Error (Invalid Data type 17) In a remote function call.

    NOTE: The error is showing when i am passing the tables to the EXEC statements otherwise it is working fine when i am passing only the date as param1 (@WBS_ENDDATE)

    Below is my Script:

     

    ' Microsoft SQL Server Integration Services user script component

    ' This is your new script component in Microsoft Visual Basic .NET

    ' ScriptMain is the entrypoint class for script components

     

    Imports System

    Imports System.Data

    Imports System.Math

    Imports System.Xml

    Imports System.IO

    Imports Microsoft.SqlServer.Dts.Runtime

    Imports Microsoft.SqlServer.Dts.Pipeline

    Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper

    Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

    Imports Microsoft.Adapter.SAP

     

     

    Public Class ScriptMain

        Inherits UserComponent

        Public Overrides Sub CreateNewOutputRows()

            '

            ' Add rows by calling AddRow method on member variable called "<Output Name>Buffer"

            ' E.g., MyOutputBuffer.AddRow() if your output was named "My Output"

            '

            Dim sapConnection As SAPConnection

            Dim sapCmd As SAPCommand

            Dim sapRdr As SAPDataReader

     

            Dim wbsenddate As String

            Dim param, param1, param2, param3 As New SAPParameter

     

            param =  New SAPParameter("@param", ParameterDirection.Input)

            param1 = New SAPParameter("@param1", ParameterDirection.Input)

            param2 = New SAPParameter("@param2", ParameterDirection.Input)

     

            sapConnection = CType(Connections.Connection.AcquireConnection(Nothing), SAPConnection)

            sapCmd = sapConnection.CreateCommand()

     

     

      

            sapCmd.CommandText = "EXEC ZRAM_XXX_XXXXX @COMPANYCODE=@param, @COSTCENTER=@param2, @WBS_ENDDATE=@param1"

     

     

            'Creating the Input Table

             Dim dtcompany, dtcostctr As New DataTable

            Dim drcompany, drcostctr As DataRow

            dtcompany.Columns.Add("SIGN")

            dtcompany.Columns.Add("OPTION1")

            dtcompany.Columns.Add("LOW")

            dtcompany.Columns.Add("HIGH")

            drcompany = dtcompany.NewRow

            drcompany("SIGN") = "I"

            drcompany("OPTION1") = "EQ"

            drcompany("LOW") = "IN02"

            drcompany("HIGH") = ""

            dtcompany.Rows.Add(drcompany)

     

            dtcostctr.Columns.Add("SIGN")

            dtcostctr.Columns.Add("OPTION1")

            dtcostctr.Columns.Add("LOW")

            dtcostctr.Columns.Add("HIGH")

            drcostctr = dtcostctr.NewRow

            drcostctr("SIGN") = "I"

            drcostctr("OPTION1") = "BT"

            drcostctr("LOW") = "UKORA"

            drcostctr("HIGH") = "UKSAP"

            dtcostctr.Rows.Add(drcostctr)

     

            param1.Value = "20060401"

            param.Value = dtcompany

            param2.Value = dtcostctr

     

            sapCmd.Parameters.Add(param)

            sapCmd.Parameters.Add(param2)

            sapCmd.Parameters.Add(param1)

     

            sapCmd.Prepare()

            MsgBox("statement execution")

            sapCmd.ExecuteReader()

            MsgBox("statement execution done")

            'While (sapRdr.Read())

            '    MsgBox("Hi")

            '    RAOutputBuffer.AddRow()

            '    RAOutputBuffer.COCD = sapRdr.GetString(1)

            '    RAOutputBuffer.CostCtr = sapRdr.GetString(2)

            '    RAOutputBuffer.PersNo = sapRdr.GetString(3)

            '    RAOutputBuffer.DueDays = sapRdr.GetString(19)

            'End While

        End Sub

    End Class

     

    Please can you help me for this?

    Thank you,
    Sandy.

    • Moved by Duane Douglas Wednesday, May 6, 2009 8:03 AM more appropriate (From:SQL Server Integration Services)
    Wednesday, May 6, 2009 6:02 AM

All replies

  • this is more of an ado.net question than an ssis question.

    moving to the ado.net forum for further assistance.
    Duane Douglas, MCAD | http://www.ssisbi.com | Please mark the post(s) that answered your question.

    SSIS Business Intelligence

    Wednesday, May 6, 2009 8:02 AM