Skip to content


Smartconnect variable replaces column name

Jothikrishnan Narasimmaraj asked 2 years ago
How do I avoid this kind of errors. It replaced _Status which is part of a column name with value of a column called _Status.
I’m trying to integrate Payroll Employees from another system and create Employee Vendor automatically with workflow status as approved.

Thank you
Jothikrishnan Narasimmaraj replied 2 years ago

i had a picture attached but not seeing it. so adding the actual query

update PM00200
set Workflow_Status=6,Workflow_Approval_Status = 0
where Workflow_Status<>6 and VENDORID=’_GPEMPID’


update PM00200
set WorkflowActive=6,Workflow_ApprovalActive = 0
where WorkflowActive<>6 and VENDORID=’HAVKY001′

Patrick Roth Staff answered 2 years ago
Use two scripts for this
1. create global variable such as GBL_TEXT
2 create .NET script in the same spot as your SQL task is
GBL_TEXT = “update PM00200 set Workflow_Status=6,Workflow_Approval_Status = 0 “
GBL_TEXT = GBL_TEXT + ” where Workflow_Status<>6 and ENDORID='” + _GPEMPID + “‘”
return true
Above we set our global variable to our sql text.  And the interesting thing about .net is that the replacement of the global variables doesn’t happen inside the double quotes so your column names are going to be correct.  The actual data variable – _GPEMPID  – does as it is outside the double quotes due to concatination
We use a so we can evaluate our global and see that it is (or should be) correct
3 use the global in our SQL Task
Now the SQL Task script is easy – just
we don’t need OTHER code here because GBL_TEXT now contains our complete code for the script and is correct at this point.
Jothikrishnan Narasimmaraj replied 2 years ago

Great! Thank you very much Patrick.


If you would like to submit an answer or comment, please sign in to the eOne portal.