How to extract SAP BW Queries with SSIS

How to extract SAP BW Queries
with SSIS
We have to create a new SSIS project
Start Visual Studio. Click „File“  „New“  „Project…“(1)
Select „Integration Services“  “Integration Services Project“ (2)
Enter project name (3) and click OK to create the project (4)
First we have to create SSIS variables (we will need them later)
Right Click Mouse  Variables to open variables pane (1)
Enter variables, define Data Type, and if needed enter value (2)
Now the variables are ready to use in the package
Now we have a blank Control Flow.
Drag and drop a Data Flow Task item into the Control Flow (1)
Double-click on the task to switch to the data flow view (2).
In the Data Flow Task we need a new Xtract connection
Right-click (1) and select „New Connection“ (2)
Select „XTRACT“ (1)
Click „Add…“ to add the Xtract connection manager (2)
Double-click to open Xtract IS Connection Manager(1)
Fill in logon credentials (2) and host information (3)
Test the connection (4) and click OK to confirm (5)
Drag and drop Xtract IS BW Cube component (1)
Double click to automatically assign connection manager to the
Xtract IS BW Cube component (2)
Click OK to confirm (3)
The Data Source dialog opens
Click to open Look Up dialog (1)
Fill in BW Query name(2) and click to search (3)
Select your BW query (1)
Click OK to confirm the selection (2)
All dimensions, their properties and measures are shown on the left
side (1)
Drag and drop cube elements to the right (2)
Click for preview (3)
Now you can have a look at the preview.
Click on the “Variables” link to open the variables dialog (1)
You can use scalar parameters (2) and SSIS variables (3)
Click to enter complex selections (4) Close all dialogs with OK
Drag and drop a Data Flow Destination.
In our example we use the OLE DB Destination (1)
We need another connection manager for the destination
Right-click in this area and select „New OLE DB Connection…“ (1)
Set up the connection manager in the dialog to an SQL Server
Drag the Xtract output flow path to the OLE DB Destination (1)
Double-click to open the OLE DB Destination editor (2)
Select an existing table from the dropdown list… (1)
or create a new one (2)
If you decide to create a new table, the „Create“ statement is
automatically generated from the meta data (1)
Click OK to create table and return to the previous screen (2)
Click on Mappings and the mapping between source and
destination elements is generated automatically (1)
Click OK to confirm the mapping (2)
Now let‘s execute the package (1)
Xtract IS is fetching the data from SAP
The extraction of the data finished succesfully.
53 rows are sent to the OLE DB Destination (1)
The output windows contains the log details (2)
If you have any questions or comments, please write at:
[email protected]