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 (see Xtract IS website).

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

We will create an 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 belong to the same IDoc. The Table has a field IDocStatus, where the result of the IDoc operation will be saved. 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 takes the IDocs as input and sends them to the SAP system. The component has one output with two columns: ID and IDocStatus.
  • An OLE DB Command which updates the SQL table with the IDocStatus.

Here is an overview of our Data Flow Task:

First Step: Prepare Table in 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 sample 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. Within SSIS you can use the Merge Join Transformation to join input data.

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

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 to it: 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.

Namespaces

using ERPConnect;
using ERPConnect.Idocs;

Define private Fields

R3Connection con;
Idoc idoc;
IdocSegment e1edk01;
int InternalID;
string status;

Acquire Connection

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 command 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 your SAP system (transaction we02):

And check the result in your SQL Data table:

Written by Khoder Elzein

Khoder is responsible for presales and always has an attentive ear for our customers, prospectives and partners. When travelling in Germany or abroad, he provides customer support at PoCs, workshops and on training courses. He also looks after the further development of our software solutions. Khoder has been working in IT since the turn of the millennium; he has been a member of team Theobald since 2009. When it comes to private interests, family, nature and reading feature at the top of his list – along with innovative fusion cuisine, as you may infer from his favourite dish of Swabian cheese noodles with tabouleh.