data extraction from SAP r/3 to sql 2000, bpc server

[Former Member]

Hi All,

I have posted a similar thread previously,, and understood how to transfer data from SAP R/3 to SQL 2005, but I need to transfer data from SAP R/3 to SQL 2000 server, not SQL 2005 server.

Any help will be greatly appreciated.

Regards,

Kranthi.

[Former Member · Accepted solution]

In SQL you will use Integration Services (SSIS) as the ETL tool and in SQL 2000 you will need to use DTS. The principles are still te same; you exactract data from the source system to a comma seperated file and when this is done you use the 2 SAP BPC steps in the DTS to load the datafile. These steps are Convert and Load & Process.

-Joost

[Former Member · Accepted solution]

HI Joost,

So,I need to have a comma seperated file in between and can not load directly to sql server as I can do it in SQL 2005?

is my understanding correct or is there any other way to directly load data to SQL 2000?

[Former Member]

ALL:

Pravin provides an excellent overview of your current options. I do, however, want to clarify ONE IMPORTANT point regarding BPC 5.X and SQL2000. While BPC 5.X is available to be installed on SQL2000, it is NOT a recommended practice at CUSTOMERS implementations. We have clearly seen that SQL2005 on a 64 bit Server for AS and the DB is an optimal BPC configuration for BPC 5.X. When building extration and SQL load processes, be advised that starting in SQL2000 using DTS will typically require re-writing of the DTS packages to the SSIS SQL2005 format to optimally perform.

If you have any questions regarding optimal settings, please contact SAP Support or Global Services.

Thanks.

[Former Member]

Alwin,

It is clear that we can load data to BPC from ASCII file or SQL server using DTS.

But how do I load data from SAP R/3 to SQL 2000? Can I do it by using some DTS or some connectors? I need to load both master data and transaction data from R/3.

Please let me know the different ways of achieving it and provide me with some documentation if possible.

Regards,

Nithya

[Former Member]

Nithya,

I think that I misunderstood the question.

Personally I have never uploaded data or metadata directly from SAP R\3 to BPC. So I can't share any experience myself. I also don't have any documentation how to do this.

I hope somebody else has some experience that he\she is willing to share.

Alwin

[pravin_datar]

If you want to extract SAP R/3 data to SQL 2000, here are some alternatives:

1. It becomes very easy if by any chance, you are using SAP BW with SAP R/3. You can extract the data into BW using standard or custom extractors and then send it out with open hub. SQL2000 can pick the flat files from a specified location at regular intervals.

2. You can use Business Object extractors. You can get more info at:

http://www.france.businessobjects.com/pdf/products/dataintegration/rapid_marts_sap_infosheet.pdf

3. If that is not an option, then you need to have a tool that allows being called via RFC. This agent will then execute the required command on behalf of your application and return the result. I do not know if there is a ready-to-use RFC-compliant agent for SQL Server. If you have a tool that does it for you, you might have to check your network connections (ping), permissions etc. and see if you can execute an rSQL from the remote machine on the SQL server.

There is a general solution to connect to nearly any kind of modern server application from a remote site by using simple HTTP requests. Suitable HTTP server for most needs would be:

1. Microsoft IIS

2. Java Tomcat or IBM WebSphere

3. SAP WebAS

In most cases, the IIS would probably be the one as it is installed on the Windows 2000 server anyway.

Define an Active Server Page (ASP.NET) and insert the necessary code in your favorite language to extract the data from the SQL server.

Your ASP.NET page would pack the result in the HTTP response object to be returned. In order to call the page from SAP you call the function modules HTTP_POST (using a HTTP POST request) or HTTP_GET (for HTTP GET request) on the standard RFC destination SAPHTTPA. (Pointing to a SAP utility program saphttp that resides in the SAP binary directory of your SAP database server and can be called from the command line in order to test its functionality without RFC).

Regards

Pravin

[Former Member]

Kranthi,

Sql 2005 and sql 2000 have both the same possibilities. You can import data from an ascii file or directly from a sql table. An example package that does import data directly from an sql (staging) table is "import sql".

The only sitback is that if you have a package created in sql2005, that you have to recreate this in sql2000.

Alwin