Extracting an Open Hub Destination based on InfoObject from SAP BW with SSIS / Xtract IS into SQL Server

In the following tutorial (pdf) I show how to create an Open Hub Service (OHS) destination and a Data Transer Process (DTP) on SAP BW 7.3 based on InfoObject Texts. After that I create a Process chain to execute the Data Transfer Process.

In the second step I use the Xtract IS OHS component within the Server Server Integration Services (SSIS) to extract the OHS Destination from BW and to load to a SQL Server table.

Links:
OHS with Xtract IS (pdf)
http://www.theobald-software.com/download/xtractis/ClickThroughXtractISOHS.pdf Xtract IS:
http://www.theobald-software.com/en/products/xtractis.htm 
SAP Open Hub Service:
http://help.sap.com/saphelp_nw04/helpdata/en/ce/c2463c6796e61ce10000000a114084/frameset.htm

A BDC Sample with Visual Studio connecting SAP using ERPConnect Services Runtime

This sample is a Visual Studio 2012 solution that creates a BDC / BCS model (external content type) in SharePoint connected to SAP using ERPConnect Services Runtime (ECS).

Activating Datasources in the SAP OLTP System

To be able to extract OLTP Datasources from SAP ERP, you first of all have to activate the relevant DataSources in the SAP OLTP system. After activating the DataSources you can extract data from all the active DataSources using the DeltaQ component.

Thoses steps have to be carried out in the SAP ERP system (OLTP). No SAP BW system is required.

How to use an SQL Configuration Table with Xtract IS

SQL Server Integration Services (SSIS) provides package configurations that you can use to update the values of properties at run time.

In this blog I will show how to store your package configuration in an SQL table. In my sample I will use Xtract IS to load the SAP table T001 for company codes using a where statement. The goal is to use the SQL configuration table to update the value of the where statement and the value of the SAP connection string.

Performing lookups by joining sap data with reference dataset in SSIS

In an SAP table many fields can belong to the table key. In some scenarios when you extract an SAP table with Xtract IS to load it into an SQL table, you want to replace the sap key fields by another custom key, e.g. a GUID.

read more on: http://www.theobald-software.com/blog/?p=247

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: