Using ERPConnect to send IDocs to your SAP system in ETL process based on SQL Server Integration Services (SSIS)

For SSIS based integration processes with SAP R/3, ERP and BI/BW systems, Xtract IS offers many components for sap interface technologies: SAP Table/View, SAP Query, SAP BAPI/RFC, BW Cube (InfoCube, InfoQuery), BW Hierachy, Open Hub Services (OHS), ABAP Report, SAP DeltaQ and BWLoader to load Data into SAP Bw (see http://www.theobald-software.com/en/products/xtractis.htm).

Here is a sample scenario, if you want to send Idocs with SSIS, where the Data are in SQL Tables.

We will create a SSIS Data Flow Task with 3 Components:

- An OLE DB Source for an SQL Database table which contains the Data for the IDocs. In this sample, rows with the same ID belongs to the same Idoc. The Table has a field IDocStatus which saves the result of the IDoc operation. This Table will be the source for our custom script component in SSIS. The input Data must be sorted by ID.

- A SSIS script component of type transformation which sends the Idocs to the sap system. The component has one output with two columns: ID and IDocStatus.

- An OLE DB Command wich updates the SQL Datatable with the IDocStatus.

Here is an overview of our Data Flow Task:

First Step: Prepare Table in your SQL Database

Create an SQL table for the Idocs. :

CREATE TABLE [dbo].[IDoc](

[ID] [int] NULL,

[MANDT] [nvarchar](3) NOT NULL,

[EBELN] [nvarchar](10) NOT NULL,

[EBELP] [nvarchar](5) NOT NULL,

[WAERS] [nvarchar](5) NULL,

[MATNR] [nvarchar](18) NULL,

[MENGE] [int] NULL,

[IDocStatus] [nvarchar](200) NULL,

CONSTRAINT [PK_IDoc] PRIMARY KEY CLUSTERED

(

[MANDT] ASC,

[EBELN] ASC,

[EBELP] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

Fill your Idoc table with sampe data:

If your Idoc data resides in many Tables, you can create a view und use it as an OLE DB Source. Or you can use an SQL Command in your OLE DB source which joins the tables.

In your Data Flow define an OLE DB Source and select as source the sql table (see http://msdn.microsoft.com/en-us/library/ms141696.aspx)

Second Step: Script Component

Define a script component of type transformation (see http://msdn.microsoft.com/en-us/library/ms137640.aspx ).

Define the input columns:

Define an output and add two columns: ID and IdocStatus

In the script Transformation Editor click on Edit Script ... to write your custom code.

· Add Reference to ERPConnect

· Add ERPConnect to your references.

· The ERPConnect.dll must be deployed to the GAC.

· There is a peculiar limitation of VSA that requires you to place any DLLs that you want to reference into the Microsoft.Net\Framework folder of your development machine.

· Use namespace

using ERPConnect;

using ERPConnect.Idocs;

· Define private Fields

R3Connection con;

Idoc idoc;

IdocSegment e1edk01;

int InternalID;

string status;

· AcquireConnection

public override void AcquireConnections(object Transaction)

{

// SAP Connection

con = new R3Connection("sapsystem", 05, "username", "password", "EN", "800");

ERPConnect.LIC.SetLic("yourLicenceKeyIfNotDemo");

con.Open();

}

· ProcessInput

public override void Input0_ProcessInput(Input0Buffer Buffer)

{

while (Buffer.NextRow())

{

Input0_ProcessInputRow(Buffer);

}

if (Buffer.EndOfRowset())

{

// Process last IDoc

if (idoc != null)

{

SendIDoc();

WriteOutput();

}

Output0Buffer.SetEndOfRowset();

}

}

· ProcessInputRow

public override void Input0_ProcessInputRow(Input0Buffer Row)

{

if (Row.ID != this.InternalID)

{

if (idoc!=null)

{

SendIDoc();

WriteOutput();

}

InitializeIDoc(Row);

}

AddIDocSegment(Row);

}

· Define private Methods

private void WriteOutput()

{

Output0Buffer.AddRow();

Output0Buffer.ID = this.InternalID;

Output0Buffer.IDocStatus = status;

}

private void SendIDoc()

{

idoc.SendAndWait();

status = idoc.GetCurrentStatus().Description;

}

private void AddIDocSegment(Input0Buffer Row)

{

// Create item segment

IdocSegment e1edp01 = idoc.CreateSegment("E1EDP01");

e1edp01.Fields["MENGE"].FieldValue = Row.MENGE;

idoc.Segments.Add(e1edp01);

// Create Object identification (material number in this case)

IdocSegment e1edp19 = idoc.CreateSegment("E1EDP19");

e1edp19.Fields["QUALF"].FieldValue = "002"; // 002 for material number

e1edp19.Fields["IDTNR"].FieldValue = Row.MATNR;// material number

e1edp01.ChildSegments.Add(e1edp19);

this.InternalID = Row.ID;

}

private void InitializeIDoc(Input0Buffer Row)

{

this.InternalID = Row.ID;

// IDoc Initialization

idoc = con.CreateEmptyIdoc("ORDERS04", "");

idoc.MESTYP = "ORDERS";

// Fill information about idoc sender

idoc.SNDPRN = "1172"; // Partner number

idoc.SNDPRT = "KU"; // Partner type

//only if asynchron

idoc.SNDPOR = "TRFC";

idoc.RCVPOR = "SAP" + con.SID;

idoc.RCVPRN = "T90CLNT090";

idoc.RCVPRT = "LS";

e1edk01 = idoc.CreateSegment("E1EDK01");

idoc.Segments.Add(e1edk01);

e1edk01.Fields["CURCY"].FieldValue = Row.WAERS;

}

· Release SAP Connection

public override void ReleaseConnections()

{

con.Close();

}

Third Step: Write the Idoc status to the sql table

Add an OLE DB Command to your Data Flow (see http://msdn.microsoft.com/en-us/library/ms141138.aspx ).

Select the connection manager to your Database.

Set the Property SQL commant to:

UPDATE [testDB].[dbo].[IDoc] SET [IDocStatus] = ? WHERE [ID] = ?

And map the columns on the tab Columns mappings.

Now you can run your SSIS Package:

Check the IDoc status in your SAP system (transaction we02):

And check the result in your SQL Data table: