Building SSIS Package with Xtract IS Table Programmatically

In this blog I will show, how to create an SSIS package with Xtract IS Table programmatically. Our Package will extract data from an SAP table and load it to the sql server.

This scenario is very helpful to create your own packages, that you can load and reconfigure for different projects and customers.

In this sample we will:

• create a Package
• add a data flow task
• add connection managers
• add an Xtract IS Table source component
• add an OLE DB destination component
• connect data flow components and map columns

to read more, please refer to: http://www.theobald-software.com/blog/?p=316

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

Extracting data from SAP BW/BI via Export DataSources with Xtract IS

To extract data from an SAP BW/BI system, Xtract IS suite provides 3 different components supporting the following interfaces:

MDX engine: Xtract IS BWCube component supports the MDX engine and enables the extraction of InfoCubes and BEx Queries.

Open Hub Services: Xtract IS OHS component supports the Open Hub Services that allow you to distribute data from BW/BI system using Infospokes or Open Hub Destinations.

Export DataSource: An Export DataSource enables the extraction of the following InfoProviders: InfoCubes, DataStores and InfoObjects. In contrast to Export DataSources, DataSources are provided to transfer data from SAP ERP or an external System to BI. Xtract IS DeltaQ component supports both DataSources.

In this blog I will focus on Export DataSources and describe how to generate them in SAP BW/BI and how to use them with Xtract IS DeltaQ.

LinqToERP: LINQ to SAP with ERPConnect and Visual Studio 2010

ERPConnect 4.0 has been released. This new Release contains a LINQ to SAP Provider for Visual Studio 2010.

Afther the installation of ERPConnect you must start the program LinqToERPSetupVS2010.exe from the installation folder (C:\Program Files (x86)\ERPConnect) to install LinqToERP.

In Visual Studio create new Project and add New Item. Select the template LINQ to ERP.

This add the file LINQtoERP1.erp to your Solution. Double-Click the file to open the designer.

As you see in the toolbox, LINQ to SAP supports following SAP Object types: Table (and View), Query, IDoc, BAPI and RFC (Function), InfoCube and InfoQuery (BWCube).

Select the table component from the toolbox. Drag and drop it to the designer. The connection dialog opens. Fill in the logon credential and host information and click ok.

The search table dialog opens. Search for a table and select it from the list.

In our sample we add the following 3 Tables:

KNA1: General Customer Data

VBAK: Header data of Sales Documents

VBAP: Item data of Sales Documents

In this blog we create a sample with three master/details Datagridviews with the Tables Customers, SalesHeaders and SalesItems.

create a DataSet

using (SAPContext sc = new SAPContext(username, password))

{

DataSet ds = new DataSet("LinqToSAP");

ds.Locale = System.Globalization.CultureInfo.InvariantCulture;

Here we have used an extension method to convert the Linq Expression to a DataTable. For more Information see the following site: http://weblogs.asp.net/stevesloka/archive/2008/01/28/linq-to-datatable.aspx

Create a customers table and add it to the DataSet

var customerQuery = from c in sc.KNA1List

where c.LAND1 == "DE"

select c;

DataTable customerTable = customerQuery.ToADOTable(rec => new object[] { customerQuery });

customerTable.TableName = "Customers";

ds.Tables.Add(customerTable);

Create a salesHeader Table and add it to the DataSet

var salesHeaderQuery = from s in sc.VBAKList

where s.WAERK == "DEM" || s.WAERK == "EUR"

select s;

DataTable salesHeaderTable = salesHeaderQuery.ToADOTable(rec => new object[] { salesHeaderQuery });

salesHeaderTable.TableName = "SalesHeader";

ds.Tables.Add(salesHeaderTable);

Create a Relation between Customers and SalesHeader

DataRelation rel = new DataRelation(

"CustomerSalesRelation",

ds.Tables["Customers"].Columns["KUNNR"],

ds.Tables["SalesHeader"].Columns["KUNNR"],false);

ds.Relations.Add(rel);

Create a SalesItems Table and add it to the dataset

var salesItemsQuery = from i in sc.VBAPList

where i.WAERK == "DEM" || i.WAERK == "EUR"

select i;

DataTable salesItemsTable = salesItemsQuery.ToADOTable(rec => new object[] { salesItemsQuery });

salesItemsTable.TableName = "SalesItems";

ds.Tables.Add(salesItemsTable);

Create a Relation between SalesHeader and SalesItems

DataRelation rel2 = new DataRelation(

"SalesItemsRelation",

ds.Tables["SalesHeader"].Columns["VBELN"],

ds.Tables["SalesItems"].Columns["VBELN"], false);

ds.Relations.Add(rel2);

Define 3 Datagridviews with the corresponding Bindingsources. Define DataSource and DataMember for each BindingSource.

customerbs.DataSource = ds;

customerbs.DataMember = "Customers";

salesHeaderbs.DataSource = customerbs;

salesHeaderbs.DataMember = "CustomerSalesRelation";

salesItemsbs.DataSource = salesHeaderbs;

salesItemsbs.DataMember = "SalesItemsRelation";

Now we have developed a sample with SAP to LINQ in 10 minutes.

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: