In this blog I will show, how to create a 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 write code using Visual Studio, a reference to many dlls is required in order to create an using statement.

using System;
using System.IO;
// add referenece to Microsoft.SqlServer.ManagedDTS.dll
using Microsoft.SqlServer.Dts.Runtime;
using Microsoft.SqlServer.Dts.Pipeline;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
// add reference to Microsoft.SqlServer.SqlTask.dll
using Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask;

At first we define some public Properties and private fields:

namespace Xtract_IS_Table
{
    public class XtractTableSSISPackage
    {
        public XtractTableSSISPackage()
        {
        }
 
        #region Public Properties
        public string PackageName { get; set; }
        public string SAPConnectionString { get; set; }
        public string SAPTableName { get; set; }
        public string OLEDBConnectionString { get; set; }
        public string OLEDBTableName { get; set; }
        public string PackageFilePath { get; set; }
        public string InternalXML { get; set; }
        #endregion 
 
        #region Private Properties
        private Package pkg;
        private MainPipe dataFlowMainPipe;
        private Executable dataFlowTaskExe;
        private CManagedComponentWrapper InstanceSource;
        private IDTSComponentMetaData100 DataSource;
        private ConnectionManager conMgrSource;
        private ConnectionManager conMgrDestination;
        private IDTSComponentMetaData100 OLEDBDestination;
        private CManagedComponentWrapper InstanceDestination;
 
        #endregion
  • Create a Package. We will create at first the Package as the top-level container.
public Package CreatePackage()
{
try
{
pkg = new Package();
  • Add a Data Flow Task
  • We add now a data flow task to the package, which will be executed within the context of the package.

// Control Task: Data Flow Task
dataFlowTaskExe = pkg.Executables.Add("DTS.Pipeline");
TaskHost dataFlowTaskHost = dataFlowTaskExe as TaskHost;
dataFlowTaskHost.Name = "DynamicDataFlowTask";
dataFlowMainPipe = dataFlowTaskHost.InnerObject as MainPipe;
  • Add an OLE DB Connection Manager:
//Destination Connection Manager
this.conMgrDestination = pkg.Connections.Add("OLEDB");
conMgrDestination.Name = "OLEDBConnectionDestination";
conMgrDestination.ConnectionString = this.OLEDBConnectionString;
  • Add an Xtract Connection Manager:
this.conMgrSource = pkg.Connections.Add("XTRACT");
conMgrSource.Name = "XTRACTConnectionSource";
conMgrSource.ConnectionString = this.SAPConnectionString;
  • Add an Xtract IS Table Source Component
DataSource = dataFlowMainPipe.ComponentMetaDataCollection.New();
DataSource.ComponentClassID = "XtractIS.XtractSourceTable, XtractIS2008, Version=1.0.0.0, Culture=neutral, PublicKeyToken=4b0cc842b94d345e";
 
// Get the design time instance of the component.
InstanceSource = DataSource.Instantiate();
// Initialize the component
InstanceSource.ProvideComponentProperties();
 
DataSource.Name = "XtractIS_XtractSourceTable";
 
// Specify the connection manager.
if (DataSource.RuntimeConnectionCollection.Count > 0)
{
DataSource.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.GetExtendedInterface(pkg.Connections["XTRACTConnectionSource"]);
DataSource.RuntimeConnectionCollection[0].ConnectionManagerID = pkg.Connections["XTRACTConnectionSource"].ID;
}
 
InstanceSource.SetComponentProperty("TableName", this.SAPTableName);
InstanceSource.SetComponentProperty("InternalXML", this.InternalXML);
 
//reinitialize the component
InstanceSource.AcquireConnections(null);
InstanceSource.ReinitializeMetaData();
InstanceSource.ReleaseConnections();
  • Add an OLE DB Destination Component
//Destination Connection
OLEDBDestination = dataFlowMainPipe.ComponentMetaDataCollection.New();
OLEDBDestination.Name = "OLEDBDestination";
OLEDBDestination.ComponentClassID = "DTSAdapter.OLEDBDestination";
 
// Get the design time instance of the component.
InstanceDestination = OLEDBDestination.Instantiate();
// Initialize the component
InstanceDestination.ProvideComponentProperties();
// Specify the connection manager.
if (OLEDBDestination.RuntimeConnectionCollection.Count > 0)
{
OLEDBDestination.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.GetExtendedInterface(pkg.Connections["OLEDBConnectionDestination"]);
OLEDBDestination.RuntimeConnectionCollection[0].ConnectionManagerID = pkg.Connections["OLEDBConnectionDestination"].ID;
}
InstanceDestination.SetComponentProperty("OpenRowset", OLEDBTableName);
InstanceDestination.SetComponentProperty("AccessMode", 0);
//reinitialize the component
InstanceDestination.AcquireConnections(null);
InstanceDestination.ReinitializeMetaData();
InstanceDestination.ReleaseConnections();
  • Connect Data Flow Components and Map Columns
//map the columns
IDTSPath100 path = dataFlowMainPipe.PathCollection.New();
path.AttachPathAndPropagateNotifications(DataSource.OutputCollection[0], OLEDBDestination.InputCollection[0]);
 
IDTSInput100 input = OLEDBDestination.InputCollection[0];
IDTSVirtualInput100 vInput = input.GetVirtualInput();
 
foreach (IDTSVirtualInputColumn100 vColumn in vInput.VirtualInputColumnCollection)
{
IDTSInputColumn100 vCol = InstanceDestination.SetUsageType(input.ID, vInput, vColumn.LineageID, DTSUsageType.UT_READWRITE);
InstanceDestination.MapInputColumn(input.ID, vCol.ID, input.ExternalMetadataColumnCollection[vColumn.Name].ID);
}

Set the appropriate connection strings:

OLEDBConnectionString = "Data Source=localhost;Initial Catalog=DBDEMO;Provider=SQLNCLI10.1;Integrated Security=SSPI;Auto Translate=False",
SAPConnectionString = "USER=XXXX LANG=EN CLIENT=800 SYSNR=05 ASHOST=XXXX PASSWD=XXXX",

We will now configure our package to read the SAP Table T004.
At first we must read the metadata from the SAP data dictionary.
Now generate the appropriate xml file InternalXML.xml:

<?xml version=”1.0″ encoding=”utf-8″ ?>
<StorageTableBase xmlns=”http://tempuri.org/StorageTableBase.xsd”>
<TableColumns>
<ColName>MANDT</ColName>
<ColDescription>Client</ColDescription>
<ColLength>3</ColLength>
<ColSelected>true</ColSelected>
<ABAPType>C</ABAPType>
<Decimals>0</Decimals>
</TableColumns>
<TableColumns>
<ColName>KTOPL</ColName>
<ColDescription>Chart of Accounts</ColDescription>
<ColLength>4</ColLength>
<ColSelected>true</ColSelected>
<ABAPType>C</ABAPType>
<Decimals>0</Decimals>
</TableColumns>
<TableColumns>
<ColName>SAKLN</ColName>
<ColDescription>Significant length of the G/L account number</ColDescription>
<ColLength>2</ColLength>
<ColSelected>true</ColSelected>
<ABAPType>N</ABAPType>
<Decimals>0</Decimals>
</TableColumns>
<TableColumns>
<ColName>DSPRA</ColName>
<ColDescription>Maintenance language for the chart of accounts</ColDescription>
<ColLength>2</ColLength>
<ColSelected>true</ColSelected>
<ABAPType>C</ABAPType>
<Decimals>0</Decimals>
</TableColumns>
<TableColumns>
<ColName>ASPRA</ColName>
<ColDescription>Alternative languages</ColDescription>
<ColLength>10</ColLength>
<ColSelected>true</ColSelected>
<ABAPType>C</ABAPType>
<Decimals>0</Decimals>
</TableColumns>
<TableColumns>
<ColName>KKTPL</ColName>
<ColDescription>Group Chart of Accts</ColDescription>
<ColLength>4</ColLength>
<ColSelected>true</ColSelected>
<ABAPType>C</ABAPType>
<Decimals>0</Decimals>
</TableColumns>
<TableColumns>
<ColName>XSPER</ColName>
<ColDescription>Indicator: is the chart of accounts blocked ?</ColDescription>
<ColLength>1</ColLength>
<ColSelected>true</ColSelected>
<ABAPType>C</ABAPType>
<Decimals>0</Decimals>
</TableColumns>
<TableColumns>
<ColName>NO_VBUND</ColName>
<ColDescription>Trading partner cannot be entered</ColDescription>
<ColLength>1</ColLength>
<ColSelected>true</ColSelected>
<ABAPType>C</ABAPType>
<Decimals>0</Decimals>
</TableColumns>
<TableColumns>
<ColName>INTEG_CO</ColName>
<ColDescription>Type of integration between G/L accounts and cost elements</ColDescription>
<ColLength>1</ColLength>
<ColSelected>true</ColSelected>
<ABAPType>C</ABAPType>
<Decimals>0</Decimals>
</TableColumns>
<TableColumns>
<ColName>LAYOUT_0</ColName>
<ColDescription>G/L account master record: Layout for “Central Processing”</ColDescription>
<ColLength>4</ColLength>
<ColSelected>true</ColSelected>
<ABAPType>C</ABAPType>
<Decimals>0</Decimals>
</TableColumns>
<TableColumns>
<ColName>LAYOUT_P</ColName>
<ColDescription>G/L master data: Layout for “chart of accts processsing”</ColDescription>
<ColLength>4</ColLength>
<ColSelected>true</ColSelected>
<ABAPType>C</ABAPType>
<Decimals>0</Decimals>
</TableColumns>
<TableColumns>
<ColName>LAYOUT_S</ColName>
<ColDescription>G/L master data: Layout for “company code processing”</ColDescription>
<ColLength>4</ColLength>
<ColSelected>true</ColSelected>
<ABAPType>C</ABAPType>
<Decimals>0</Decimals>
</TableColumns>
<XtractProperties>
<PropName>ASYNCPROCESS</PropName>
<PropValue xml:space=”preserve”> </PropValue>
</XtractProperties>
<XtractProperties>
<PropName>PRIMARYKEYPACKAGING</PropName>
<PropValue xml:space=”preserve”> </PropValue>
</XtractProperties>
<XtractProperties>
<PropName>OHSREQUESTID</PropName>
<PropValue />
</XtractProperties>
<XtractProperties>
<PropName>OHSEXTRACTION</PropName>
<PropValue xml:space=”preserve”> </PropValue>
</XtractProperties>
<XtractProperties>
<PropName>BACKGROUNDEXTRACTION</PropName>
<PropValue xml:space=”preserve”> </PropValue>
</XtractProperties>
<XtractProperties>
<PropName>BUFFERLOCATION</PropName>
<PropValue />
</XtractProperties>
</StorageTableBase>

The destination table must be created, in our sample it has the following format:

CREATE TABLE [dbo].[T004](
    [MANDT] [nvarchar](3) NULL,
    [KTOPL] [nvarchar](4) NULL,
    [SAKLN] [nvarchar](2) NULL,
    [DSPRA] [nvarchar](2) NULL,
    [ASPRA] [nvarchar](10) NULL,
    [KKTPL] [nvarchar](4) NULL,
    [XSPER] [nvarchar](1) NULL,
    [NO_VBUND] [nvarchar](1) NULL,
    [INTEG_CO] [nvarchar](1) NULL,
    [LAYOUT_0] [nvarchar](4) NULL,
    [LAYOUT_P] [nvarchar](4) NULL,
    [LAYOUT_S] [nvarchar](4) NULL
) ON [PRIMARY]

Download the whole project

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.