Truncating data in tables

dataservices job design
1

Hi All

Need some advise on how to use system configurations to dele data from table using SQL scripts.

I have created two different schema on the SQL database “dbo”, and “env”. We have the same table on both schema called “EMPLOYEE_GENERAL”. In BODS I have created two different datastore connections with Alias, to point to different schema, and created two different system configuration, and assigned different data store configurations.

If I create a dataflow, with drop and recreate as the target table, I can run the job with different Sys Config, and the data get deleted, and created as per the requirement.

I have removed the “Drop and Recreate” option, and added a truncate statement to delete the data. Without the actual schame name, the script created as below

SQL (‘DS_STG_OBJ_INIT’,'Delete from EMPLOYEE_GENERAL)

If I run the job, with any Sys Config, it deletes the data from “dbo” schema. I can change the script and confditionally use it with the schema name, which works.

SQL (‘DS_STG_OBJ_INIT’,'Delete from dbo.EMPLOYEE_GENERAL)
SQL (‘DS_STG_OBJ_INIT’,'Delete from enr.EMPLOYEE_GENERAL)

Any idea, how I can use the ALIAS name, instead of the scheme name, so I don’;t need to maintain and update this script with further schemas…

amuh10 :uk: (BOB member since 2012-04-11)

2

Hi,

I don’t have a DS at hand to check it, but i do remember that there is a datastore_filed_value() (or the sort) builtin function, which give some data store parameters such as user, Server and DB name, etc. You can inspect other fields in order to get what you want.

An extra idea would be to combine the output of the function with an internal table with the extra data that is outisde of the scope of the function. That is, get something which is relate to the ALIAS or the DS config, and then use it to get the extra data from that table. In your case should be the exact ALIAS.

Here is a post with an example of it use.
https://blogs.sap.com/2014/03/27/custom-function-to-get-database-name-from-a-datastore/

Regards,

Andrés

aidelia :argentina: (BOB member since 2006-02-02)