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)
www.theobald-software.com
2
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
www.theobald-software.com
3
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).
www.theobald-software.com
4
In the Data Flow Task we need a new Xtract connection
Right-click (1) and select „New Connection“ (2)
www.theobald-software.com
5
Select „XTRACT“ (1)
Click „Add…“ to add the Xtract connection manager (2)
www.theobald-software.com
6
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)
www.theobald-software.com
7
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)
www.theobald-software.com
8
The Data Source dialog opens
Click to open Look Up dialog (1)
Fill in BW Query name(2) and click to search (3)
www.theobald-software.com
9
Select your BW query (1)
Click OK to confirm the selection (2)
www.theobald-software.com
10
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)
www.theobald-software.com
11
Now you can have a look at the preview.
www.theobald-software.com
12
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
www.theobald-software.com
13
Drag and drop a Data Flow Destination.
In our example we use the OLE DB Destination (1)
www.theobald-software.com
14
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
www.theobald-software.com
15
Drag the Xtract output flow path to the OLE DB Destination (1)
Double-click to open the OLE DB Destination editor (2)
www.theobald-software.com
16
Select an existing table from the dropdown list… (1)
or create a new one (2)
www.theobald-software.com
17
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)
www.theobald-software.com
18
Click on Mappings and the mapping between source and
destination elements is generated automatically (1)
Click OK to confirm the mapping (2)
www.theobald-software.com
19
Now let‘s execute the package (1)
www.theobald-software.com
20
Xtract IS is fetching the data from SAP
www.theobald-software.com
21
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)
www.theobald-software.com
22
If you have any questions or comments, please write at:
[email protected]
www.theobald-software.com
23