Smartconnect variable replaces column name
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.
i had a picture attached but not seeing it. so adding the actual query
set Workflow_Status=6,Workflow_Approval_Status = 0
where Workflow_Status<>6 and VENDORID=’_GPEMPID’
set WorkflowActive=6,Workflow_ApprovalActive = 0
where WorkflowActive<>6 and VENDORID=’HAVKY001′
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 + “‘”
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 MessageBox.show() 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.
Great! Thank you very much Patrick.
If you would like to submit an answer or comment, please sign in to the eOne portal.