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]
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.
Second Step: Script Component
Define a script component of type transformation (see http://msdn.microsoft.com/en-us/library/ms137640.aspx ).
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:
