Skip to content
+1-888-319-3663

COMMUNITY FORUM

Post Document SQL Task – Using Variable

Nick Gruzewski asked 2 years ago
I cannot figure out what I am doing wrong.   I am using a post document SQL Task to update a flag in my source sql database.   Basically, the map task gets all records it needs to process, then the post document task updates the exported flag to indicate that this transaction is complete.  
My post document task looks like this, I have tired several different ways.  The forum answers indicate that I need single quotes around it.  What is even stranger is that I have the same exact method in a different map task and it works fine.  
declare @accountingtransactionid int;
set @accountingtransactionid = ‘_AccountingTransactionId’
update Funding.AccountingApplyTransactionExportHistory
set
IsExported = 1,
AccountingExportDate = GETUTCDATE()
where FundingAccountingExportTypeId in (1, 4)
and AccountingTransactionId = @accountingtransactionid 
Generated SQL below.  Somehow it is pulling ingTransactionId as the value of the variable.  
declare @accountingtransactionid int;
set @accountingtransactionid = ‘ingTransactionId’
update Funding.AccountingApplyTransactionExportHistory
set
IsExported = 1,
AccountingExportDate = GETUTCDATE()
where FundingAccountingExportTypeId in (1, 4) and AccountingTransactionId = @accountingtransactionid 
 
Thank you for your help. 
Answers
Patrick Roth Staff answered 2 years ago
I would suspect that you have a source field on this map called “_Account” and that the data is empty.
When SC does the replace of the variables in scripts with the data, I believe it does it in alpha order – meaning that _Account would come before _AccountingTransactionid.
And so SC would replace _Account with the value – which this suggests is empty and that would leave you with ‘ingTransactionId’.
When it comes time to replace _AccountingTransactionid, that no longer exists and you still end up with ‘ingTransactionId’.
You might make a global variable – say GBL_TransactionID – and then in the after document script set it to your value in a script task
GBL_TransactionID= _ACCOUNTINGTRANSACTIONID
return true
and then in the script, use the new variable instead.
set @accountingtransactionid = ‘GBL_TransactionID’
 
Patrick Roth Staff replied 2 years ago

I forgot to mention – that it works fine in another map suggests to me that your other map didn’t have the _Account field to be replaced first.

Nick Gruzewski replied 2 years ago

Thank you Patrick. I do have an Account variable, that must be it. What’s odd is that I added a grouping to my map task (Even though it was unnecessary) and it worked. I did that because the other map that was working was grouped. I will try with a unique alias.


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