BODS - SQL Server VARCHAR(Max) source field to target VARCHAR [SOLVED]

Scenario: We had this scenario, where the SQL server database column in our source table was created as VARCHAR(MAX), in order to accommodate large descriptions. This field is to be stored into a VARCHAR(65000) field on the target table. 

Issue: When importing this VARCHAR(MAX) source field, since BODS doesn't have the same datatype defined, this gets imported as LONG datatype in BODS. When you try to map this LONG filed to target VARCHAR field, there is a conversion error that comes up. 

Resolution: The resolution to this problem is to convert the LONG datatype identified by BODS to a VARCHAR field. The CAST. and other commonly used Conversion functions are not capable enough to handle this scenario. We referred to the BODS designer documentation and found that there are two functions to convert Long datatypes to Varchar and vice-versa, 

• LONG_TO_VARCHAR(TableName.ColumnName, IndexPosition, Length) - Converts Long datatype to Varchar. 

             The function expects three parameters, 

1. TableName.ColumnName, suppose your table is STUDENTS and column name is REMARKS, This part of the function will be ' STUDENTS.REMARKS '.
2. Maximum Size - Second argument is the maximum size of the long column to be considered for conversion.
3. Starting Position(Optional argument) - The third argument would be the Index position to start from for the conversion.

Screenshot from Reference Guide,

        

        Example - 

            LONG_TO_VARCHAR( <column_name>, <max_size>, <start_position> )

            LONG_TO_VARCHAR( STUDENT.REMARKS, 10000, 1) 

        The above function says, convert STUDENT.REMARKS field into a varchar datatype from Long, starting from index position 1 and go up till string index position of 10000. This converted field can now be stored into a VARCHAR field in transform/target.

Parameterize Schema/Owner Name in SAP BODS SQL scripting function

Scenario: Requirement was to parameterize a schema name in SQL function in BODS scripting language. Schema name should be fetched from the datastore connection and used dynamically in job.

 You can follow the below steps to get this done,

Step 1: First fetch the schema name from the datastore name. Declare a variable in workflow, say $LV_TableOwnerDB to store the schema name. WE can use db_owner pre-built function for the same as below,

# Get the schema/owner name from from datastore using alias name.

$LV_TableOwnerDB = db_owner( 'DATASTORE_NAME', 'ALIAS_NAME');

 print('TABLE OWNER  = '||$LV_TableOwnerDB);

Now $LV_TableOwnerDB variable would store the schema name of your target database.

Step 2: Now declare another variable where you store the SQL Script to be executed.

$LV_Temp = 'UPDATE '||CAST($LV_TableOwnerDB,'VARCHAR(15)')||'.TABLE_NAME SET ACTIVE_FL =\'Y\' WHERE ACTIVE_FL=\'N\'';

Print($LV_Temp);

Step 3: Now pass the variable with SQL stored as argument to the sql function as the second parameter below.

sql('DATASTORE_NAME',$LV_Temp);

Suppose the Datatstore name is 'EDW_HR' alias name defined in datastore is 'ALIAS_1', the script would be as below,

# Get the schema/owner name from from datastore using alias name.

$LV_TableOwnerDB = db_owner( 'EDW_HR', 'ALIAS_1'); print('TABLE OWNER  = '||$LV_TableOwnerDB);
$LV_Temp = 'UPDATE '||CAST($LV_TableOwnerDB,'VARCHAR(15)')||'.TABLE_NAME SET ACTIVE_FL =\'Y\' WHERE ACTIVE_FL=\'N\'';Print($LV_Temp);
sql('EDW_HR',$LV_Temp);

Let us know if this helped. :)

Script to terminate BODS Job on condition

Scenario: We have a metadata table for extract runs, before running an extract job, we need to check whether the previous run was successful. This can be done in Data Services using scripting.

Solution:

Use the following script

if ( sql('DS_Datastore_Name','SELECT EXTRACT_STATUS FROM EXTRACT_RUN WHERE EXTRACT_NAME ='Extract 1') != 'Completed'  )
begin
raise_exception_ext( 'Job terminated due to error in previous run. Correct the previous load and re-run again',1);
end

In the above code, we write a script where in we use an if condition and inside the if condition with the help of a SQL function, we write code to check if the previous run was completed or not.  If not completed, it goes inside the if condition where we have written the below code,

raise_exception_ext( 'Job terminated due to error in previous run. Correct the previous load and re-run again',1);

This code will make sure that a exception is raised and there by failing the job.

SAP Data Services SQL Transform with variable - Invalid Pseudocolumn error [Solved]

Scenario: I had a scenario where in I needed to achieve a lead, lag column logic in BODS and had to do with a SQL Transform. Here I have a filter on date, i.e filter CreateDateTime column less than extract date. So I have a variable $PV_ExtractDate where in I pass my extract date value.

which would make my query look something as below,

SELECT COL_A, COL_B FROM SALES WHERE CreateDateTime < $PV_ExtractDate

The above syntax in SQL Transform was creating an error for me as the value was not being passed to the variable.

Error: SQL submitted to ODBC data source <SAMPLE> resulted in error <[Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid pseudocolumn "$PV_ExtractDate".>

Cause: BODS doesn't recognize $PV_ExtractDate as a variable value in SQL Transform, this can be corrected by using the following syntax,

SELECT COL_A, COL_B FROM SALES WHERE CreateDateTime < {$PV_ExtractDate}

Note: It is advised not to go with a SQL Transform, unless no workaround is feasible in SAP BODS. This is because you are writing code that is specific to a particular database, hence if the source system is changed to a new type of database an issue might occur.

New column in SAP BODS Template Table not reflecting in another Dataflow

Scenario: I am pivoting and creating a few flags into a template table in BODS and in the corresponding workflow I am using this template table in another dataflow to aid the data load.

I have now added a new flag to the template table, ran the load for a first time. The template table now has the new flag column in it in the database. Now I go to the dataflow and try to use the new flag that was created, but I am not able to see the same. 

Cause: This is a common issue, changes to template tables not getting reflected in BODS. It is not possible to import template table into BODS, if you right click a template table and do it, the table will move to table definitions in the datastore.  

Resolution: The resolution is to drag in another instance of the temp table, say if your template table name is TEMP in flow, dragging a new table and joining to existing source will create a new alias named TEMP_1. Now delete the old template table reference and again drag in a new reference, now since the existing alias(TEMP) is gone, this new reference will be named as TEMP. Now you may delete the TEMP_1 reference.  

Once done you should be able to see the change, i.e the new flag in the dataflow. 

tags: SAP BODS Data Services Designer template table not reflecting in new dataflow. 

gen_row_num_by_group function in SAP BODS not creating sequence properly [SOLVED]

Issue: A week back, I had written a post on how we used the gen_row_num_by_group function create a sequence of numbers based on a particular column in BODS. I ran into an issue sometime later, where the sequences were not getting properly generated.

Cause: This is because the input source was not properly sorted. When you use this particular function based on a column, you should note that the input to the query transform should be properly sorted first. This is very important, if you miss this then the data will not be properly sorted and hence create wrong sequence numbers.

Resolution: The way to resolve this issue is to have your input data sorted. For example, if you are creating a sequence based on ClassId column as below,

gen_row_num_by_group(ClassId)

You should in this case make sure that the data coming to the query transform be grouped on this particular column, i.e ClassId in this example. This makes sure the sequence gets generated accordingly. 

Please do write to us, if you found this useful. 

Found erroneous expression <> in SAP BODS Designer [SOLVED]

Issue: SAP BODS shows no error on validation, but comes up with this 'Found erroneous expression <> ' error at times. 

Found erroneous expression error in SAP Data Services Designer

Cause:  This is because your workflow, or dataflow might not have proper calls to variables/parameters that are being used. The variables may not be properly referenced. The value of the parameter probably is not being passed from the parent object(job,workflow) to the child object(workflow, dataflow). 

Resolution: Check for parameters used and whether they are correctly being referenced from the child object. Solved the problem for me.  

References

1. http://www.forumtopics.com/busobj/viewtopic.php?p=1036501

Dynamically Generate a Target Flat File Format from Dataflow in SAP BODS Designer

Scenario: To generate a flat file format from inside a data-flow rather than typing each column, datatype and size.

Solution: Being new to SAP BODS designer, I found it really hard to type out each column in a flat file format, along with the datatype and it's corresponding size. It can become a tiresome process. 

The easiest solution is to go to one of your query transforms and on the schema out pane(right pane), right click on the parent of your 'Schema out' as in screenshot below,

SAP BODS - Create File Format Generation Option ( Enlarge )

Now from the list that opened up, select 'Create File Format'. This will open up a flat file format window for you with same column names, datatypes and lengths as in the schema out part of your query. 

You can then save the same, edit the properties and use in your BODS dataflow for data loading.  

Generate a number sequence grouped on a column/key in SAP BODS

Scenario: Create a sequence number grouped or based on another key/column value from the source result set. 

eg: consider the following data-set

Now in the target dataset/table, we require a new column which would sequentially assign a number starting from 1 for each student under a particular class. The result set then would look something like below, 

Now we have this new column (StudentSeqNbr) which sequentially numbers each student under a particular ClassId. The aim is to create such a sequential column using SAP BODS.

Solution: SAP BODS has an inbuilt function which will let you create such a sequence and can be mapped inside a Query transform.

Gen Rownum function in SAP BODS( Click to enlarge )

Using this function and the below syntax, a sequence can be created for the scenario above.

gen_row_num_by_group(ClassId)

Syntax:

gen_row_num_by_group(ColumnName)

UPDATE: The input should be sorted for this function to work properly to create a sequence.