Importing data from SAP using parameters

In the previous entry, ‘Importing data from SAP’, I detailed how to import data from SAP to SQL Server, but every time that process is executed, it imports all available data from the SAP query to SQL Server, so there is no filtering. This forces us to delete all the previous data and reinsert it over and over again. In a more realistic scenario, the desirable is to fetch only the ‘delta’ (new and updated data) from SAP and update SQL Server accordingly. Typically the process should get the last Time Stamp value from SQL Server and launch a query to SAP where the returned rows have a higher Time Stamp value. But if you have tried to do this with SAP and SQL Server using SSIS with the BizTalk Adapter for SAP, you would have encounter one big problem:

The BizTalk/SSIS ADO.NET driver for SAP doesn’t support parameters in a SQL-kind query!

In my case I was trying to execute a query like this:

SELECT <fields list> FROM BKPF WHERE CPUDT >=?

Where ‘?’ will be a parameter of type string in the date format used by the ADO.NET driver (YYYYMDD).

After some research I’ve found a solution to this scenario, let’s have a look at it:

Solution overview

To keep this post short I will concentrate only in the SAP data extraction part and I will not cover the dimension and fact tables.

When querying SAP I like to keep the query window as small as possible. For that I create a staging database in SQL Server to dump the SAP query results. This database later will serve as data source for the data warehouse that will be the foundation for the MOLAP cube:

 

The SSIS package that extracts data from SAP and loads it in the Staging database has the following steps:

  1. First step is to TRUNCATE the table in the staging database that will receive the SAP data.
  2. In the second step I recover the last Time Stamp in the Fact table in the DW database. Time Stamps are generated for each row prior to be loaded into the fact table.
  3. This is the most key step. Here I prepare the SAP query with the parameter to get only the new records (from the last Time Stamp until now).
  4. Using the query created in the third step, this data flow task fetch the targeted data from SAP and loads it into the Staging database in SQL Server

Now let’s see each step in detail:

First Step

This is the simplest step of all. It consists on an Execute SQL Task that executes the following T-SQL sentence in the Staging database:

TRUNCATE TABLE [dbo].[BKPF]

Data is kept in this table from extraction to extraction as a way to monitor which data has being loaded. This is reason why the table needs to be truncated before the new extraction process occurs.

Second Step

In this second Execute SQL Task I get the last Time Stamp in the Fact table in the DW database by executing the following T-SQL code:

SELECT ISNULL((SELECT TOP 1 TimeStamp FROM FactParkDoc ORDER By TimeStamp DESC),0) As TimeStamp

The reason to have the “SELECT ISNULL(<expr>,0) As TimeStamp” is because that the Fact table could be empty, which will make this query to return no rows and the SSIS task to fail.

The result of the query is stored in a SSIS variable called LastTimeStamp of Data Type equal to DateTime:

To tell SSIS to store the result of the query in that variable you need to configure the ResultSet property of the Execute SQL Task to Single row value. You can do this in the General tab in the Execute SQL Task editor.

Third Step

In this step I prepare the query that later will be sent to SAP. Due to the ADO.NET SAP adapter doesn’t support dynamic parameters, we need to pre-format the query as a fixed string with the parameter values already in a string fashion and use this, now static query, to fetch data from SAP. I like to use the Script Task to build this query string and save it into a SSIS variable called SAPQuery of DataType equal to String.

The code to create the query string is as follows:

public void Main() {

DateTime LastTimeStamp = new DateTime();

LastTimeStamp = System.Convert.ToDateTime(Dts.Variables[“LastTimeStamp”].Value);

Dts.Variables[“SAPQuery”].Value = “SELECT BELNR, BKTXT, BLART, BSTAT, CPUDT, CPUTM, BLDAT, BUDAT, GJAHR, MONAT, USNAM, PPNAM, TCODE FROM BKPF WHERE CPUDT >='” + LastTimeStamp.Date.ToString(“yyyyMMdd”) + “‘”;

Dts.TaskResult = (int)ScriptResults.Success;

}

To run this code you need to configure the Script Task to use the two SSIS variables that we have discussed already:

 

SAPQuery variable needs to be of a ReadWriteVariable type as we are updating its value during runtime.

Note: Date formats in the ADO.NET adapter for SAP follow the format described in this MSDN article about the SELECT syntax for the SAP adapter: http://msdn.microsoft.com/en-us/library/cc185153(v=bts.10).aspx

Forth Step

This Data Flow task contains the following components inside:

 

As you can see this is a normal data transfer SSIS package, where a query is first executed against the data source, followed by some data transformation to finally load the data into the data destination repository.

Our problem is that the query to the data source needs to have a parameter and the SAP adapter doesn’t support parameters. The solution is to pass the whole query string created in the third step directly to the data source, following the next steps:

  • In the Data Flow components create at least the Data Source task and the Data Destination task.
    • In the Data Source task type the real query but without the WHERE clause:

SELECT BELNR, BKTXT, BLART, BSTAT, CPUDT, CPUTM, BLDAT, BUDAT, GJAHR, MONAT, USNAM, PPNAM, TCODE FROM BKPF

  • Map the data source and data destination columns in the Data Destination task as usual.
  • Return to the Control Flow tab (exit the Data Flow)
  • Open the Properties of the Data Flow task by right-clicking on top of it and select ‘Properties’ option
  • Go to Expressions and click the little button with the 3 dots
  • In the Property Expressions Editor window, in the Property column choose the option with the name of your Data Source task in the Data Flow, in my case is ‘From BKPF in SAP’. You will see two Properties with this name, choose the one that has SqlCommand at the end:
  • In the Expressions column, choose the SSIS variable that contains the query string created in the third step. In my case SAPQuery. Ensure that this variable is of the String data type.

NOTE: At compile time SSIS will match the default expression in SAPQuery to the ADO.NET Data Source task in the Data Flow task. To avoid an error message about not matching metadata set the default value of SAPQuery to the query string being built without the WHERE clause: SELECT BELNR, BKTXT, BLART, BSTAT, CPUDT, CPUTM, BLDAT, BUDAT, GJAHR, MONAT, USNAM, PPNAM, TCODE FROM BKPF 

With this configuration SSIS will change at runtime the query used in the Data Source task in the Data Flow by the query stored in the SSIS variable SAPQuery, created in the third step. So, you will have your SAP query with parameters in a very elegant way.

Advertisements
Tagged with: , , , , , ,
Posted in SAP, SSIS
2 comments on “Importing data from SAP using parameters
  1. Jayesh says:

    Hi, I tried the same thing, the error which i am getting in this is

    Error: The type of the value being assigned to variable “User::SapQuery” differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.
    Error: The wrapper was unable to set the value of the variable specified in the ExecutionValueVariable property.

    Please help me out on this ASAP…

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 24 other followers

Categories
%d bloggers like this: