Import SAP Data Using SQL Server Management Studio
This section provides information on how to use the SQL Server Management Studio to import data from an SAP system into a SQL Server database. This section provides instruction on how to create an SSIS package that you can execute to import data. This section also provides information on how to execute the SSIS package.
Prerequisites
Before performing the procedures provided in this topic, make sure:
Data Provider for SAP (E.g. SAP Logon) is installed on the computer.
SQL Server Business Intelligence Development Studio is installed on the computer.
Custom RFCs (Z_EXTRACT_DATA_OO & Z_EXECUTE_SAP_QUERY) are installed. See Install Custom RFCs for the Data Provider for SAP
To import data using SQL Server Management Studio
Start the SQL Server Management Studio.
In the Connect to Server dialog box, specify the values to connect to a SQL Server database and click Connect. The Microsoft SQL Server Management Studio opens.
In the Object Explorer, expand the SQL Server name, expand Databases, and right-click the database into which you will be exporting the tables from the SAP system. From the context menu, point to Tasks, and click Import Data. This starts the SQL Server Import and Export Wizard.
Read the information on the welcome screen and click Next.
In the Choose a Data Source dialog box, from the Data Source drop-down list .NET Framework Data Provider for mySAP Business Suite. The dialog box lists the different connection parameters to connect to an SAP system. A typical connection string to connect to an SAP system using the Data Provider for SAP requires:
The connection parameters for a connection type. The Data Provider for SAP supports connection types A, B, and D. To connect to an SAP system you must provide connection parameters for any one of these connection types. For example, for connection type A, you must provide the name of the application server host and the system number.
The login information to connect to an SAP system such as username and password.
For more information about the connection string to connect to an SAP system using the Data Provider for SAP, see Read about Data Provider for the SAP Connection String.
In the Choose a Data Source dialog box, specify:
The connection parameters for any one connection type.
The login information to connect to an SAP system.
Whether you want to enable SAP GUI debugging.
Whether you want to use RFC SDK tracing.
Click Next.
In the Choose a Destination dialog box:
From the Destination drop-down list, select SQL Native Client.
From the Server name drop-down list, select a SQL server name.
Select an authentication mode.
From the Database drop-down list, select the database to which you want to import the SAP table.
Click Next.
In the Specify Table Copy or Query dialog box, choose the Write a query to specify the data to transfer option and click Next.
In the Provide a Source Query dialog box, specify a SELECT query to filter the data to be imported into the SQL Server. For more information about the grammar for a SELECT query for the Data Provider for SAP, see Syntax for a SELECT Statement SAP.
Click the Parse button to validate the query and click OK in the pop-up dialog box. Click Next.
In the Select Source Tables and Views dialog box, select the check box against the source and destination tables. The source is the query you specified to retrieve data from SAP. The destination is the table that will be created in the SQL Server database.
The wizard creates a default mapping between the source and destination table fields. However, you can change the mappings according to your requirement. To change the field mappings, click Edit Mappings.
In the Column Mappings dialog box, you can:
Change the names of columns in the destination table.
Ignore certain columns in the destination table.
Change the data type for fields in destination table.
Change other field attributes such as nullable, size, precision, and scale.
Click OK.
In the Select Source Tables and Views dialog box, click Next.
In the Save and Execute Package dialog box,
Select the Execute immediately check box to execute the query.
Select the Save SSIS Package check box to save the query as a package and execute it later. If you chose to save the package, you must also specify whether you want to save the package in the SQL Server or the file system.
From the Package protection level drop-down list, select a protection level for the package and specify credentials where required.
Click Next.
If you chose to save the package, proceed to next step. Otherwise, skip to step 15.
In the Save SSIS Package dialog box, specify:
Name for the package
Description for the package
If you chose to save the package to a SQL server, select a SQL Server from the Server name drop-down list.
If you chose to save the package to the file system, specify the name and location of the file in the File name text box.
Click Next.
In the Complete the Wizard dialog box, review the summary of actions that the wizard will perform, and click Finish.
In the Performing Operations dialog box, the wizard starts executing tasks to import the information from SAP into a SQL Server database table. The status for each task is displayed in the wizard.
After all the tasks are successfully executed, click Close. If a task fails, see the corresponding error message, fix the issue, and rerun the wizard.
Running the SSIS Package
If you chose to save the SSIS package, you can run it to retrieve the most recent information from the SAP system. This section provides information on how to run the package if you chose to save it to the file system.
To run the package from Windows Explorer
From the Windows Explorer, navigate to the location where you saved the package, and double-click the package.
On the Execute Package Utility dialog box, click Execute.
The Package Execution Progress dialog box displays the progress of the different tasks.
After all the tasks are successfully executed, click Close.
On the Execute Package Utility dialog box, click Close.
For more information about running packages, see https://go.microsoft.com/fwlink/?LinkId=94972. For any other information related to SSIS packages, see https://go.microsoft.com/fwlink/?LinkId=94973.
Verifying the Results
After executing the package, you must verify the results by going to the SQL Server database to which the SAP data is imported. Executing the package should have created a table in destination database and populated with the values from the SAP table.