Example TurboIntegrator script

This script populates the staging tables and starts the stored procedure triggerimportbatchjobs.sql.

About the script

This is an example only. You must write a script that is specific to your environment.

The INSERT statement does not have all of the fields shown in the script.

The VALUES statement contains three NULL values. The associated fields cannot be BLANK. You must either provide a value for those fields if they apply to your organization or keep the NULL values.

Important: When you specify a value in the DATA or EPILOG sections of the script, enclose the value by two single quotation marks (''), not one double quotation mark (").

DATA - Populates XSTAGEFACT with the TM1 view's data

ODBCOPEN ( 'Controller', 'Cognos Controller database user ID','Cognos Controller database password' );

SQLQuery1 = 'INSERT INTO xstagefact (
ST_ID,
     ST_PERIOD,
     ST_ACTUALITY,
     ST_COMPANY,
     ST_CURRENCY,
     ST_ACCOUNT,
     ST_AMOUNT,
     ST_EXTDIM1,
     ST_C_COMPANY,
     ST_TRANAMOUNT,
     ST_TRANCURR)';

SQLQuery2 = 'VALUES (
''tempid'',
     ''%CCR_PERIOD%'',
     ''%CCR_ACTUALITY%'',
     ''%CCR_COMPANY%'',
     ''%CCR_CURRENCY%'',
     ''%CCR_ACCOUNT%'',
     ''%CCR_AMOUNT%'',
     NULL,
     NULL,
     ''0'',
     NULL );';

ODBCOutPut( 'Controller', sql1, sql2, EXPAND(sql3), EXPAND(sql4));
ODBCCLOSE ( 'Controller' );



EPILOG - Sets an accurate st_id (in DATA we populated with a temporary id since the column is NOT NULL) (you may have multiple 
'batches' of imports and each batch's st_id should be unique, as they may not all be using the same import specification). Also 
creates the batch_id. In Controller, users will submit the batch to bring the data from the staging table to the 
financial table (xdbnn).

ODBCOpen('Controller', 'Cognos Controller database user ID','Cognos Controller database password');

sql1 = ' declare @st_id as varchar(8)
set @st_id = ''TM1PM''+ case
when (select max(right(st_id, len(st_id)-5))
from fastnet.xstagefact where left(st_id, 5) = ''TM1PM'') is null
then cast(1 as varchar(3))
else cast((select max(right(st_id, len(st_id)-5)) +1
from fastnet.xstagefact where left(st_id, 5) = ''TM1PM'')
as varchar(3))
end';

sql2 = ' Update xstagefact set st_id = @st_id where st_id = ''tempid'' ';


sql3 = ' declare @spec as varchar(12)
set @spec = ''TM1_BS''
exec usp_triggerimportbatchjobs @st_id, @spec,''D'','''',''ADM'',''1'',''2009-09-09'' ';

ODBCOutPut('Controller',sql1, sql2, sql3);
ODBCClose('Controller');