DSSetUserStatus in Job
Posted: Wed Feb 12, 2003 3:48 am
I undertook to advise of a generic way to set the user status value within a job, based on a recent project. In this example the job design is:
ODBC ----> Transformer ----> SeqFile
The ODBC stage executes a stored procedure (in SQL Server as it happens) that inserts a new row in a table, generating a guid (guaranteed unique id) value and returning this value and the columns from the row as a recordset. Subsequent jobs need to update this row; it's a row in a control file managing process metadata and restartability.
All columns are transmitted to the Transformer stage and are passed unchanged to the sequential file. In addition, the unique id (as a VarChar(36) for anyone who needs to know) is passed to an additional output column, the derivation for which is:
DSSetUserStatus(InputLink.TableID)
This column then goes into the output file as the return value of the DSSetUserStatus() function, which in my experience is always 0.
Ray Wurlod
Education and Consulting Services
ABN 57 092 448 518
ODBC ----> Transformer ----> SeqFile
The ODBC stage executes a stored procedure (in SQL Server as it happens) that inserts a new row in a table, generating a guid (guaranteed unique id) value and returning this value and the columns from the row as a recordset. Subsequent jobs need to update this row; it's a row in a control file managing process metadata and restartability.
All columns are transmitted to the Transformer stage and are passed unchanged to the sequential file. In addition, the unique id (as a VarChar(36) for anyone who needs to know) is passed to an additional output column, the derivation for which is:
DSSetUserStatus(InputLink.TableID)
This column then goes into the output file as the return value of the DSSetUserStatus() function, which in my experience is always 0.
Ray Wurlod
Education and Consulting Services
ABN 57 092 448 518