Using SQL Stored Procedure as destination with identity return value
Jamie asked 5 years ago
Hi There,
I have a SQL Stored Procedure as a destination in a map. This stored procedure adds a record to a table and subsequently returns the ID of the newly added record via the SQL SCOPE_IDENTITY() function. I would like to be able to get this value and re update the source record with the new value. Is it possible to retrieve a value from a “destination” procedure? Any hints or workaround ideas would be much appreciated!
I have a SQL Stored Procedure as a destination in a map. This stored procedure adds a record to a table and subsequently returns the ID of the newly added record via the SQL SCOPE_IDENTITY() function. I would like to be able to get this value and re update the source record with the new value. Is it possible to retrieve a value from a “destination” procedure? Any hints or workaround ideas would be much appreciated!
Answers
Patrick Roth Staff answered 5 years ago
No, we can’t retrieve any returned data from that proc call – the call is assumed one way and the only return would be the two “out” error parameters.
From a workaround perspective:
1. don’t use a SQL proc destination, instead set the destination to a text file or something (since we need a destination)
Then in the ‘before document’ or ‘after document’ script you could use a .NET task to call the proc. Or actually maybe a SQL Task since now you are connected right to SQL and can call the proc directly with the source data fields.
Then since it is YOUR proc call you can capture the output of that identity either through the output param you have or I think the select SCOPE_IDENTITY() that would give you that after the call.
Once you have that, then you can update whatever you want since again is your script call.
Otherwise, assuming that we don’t want to do this ourselves, leave what you have as-is.
then use an ‘after document success’ to query the target tables directly with the key fields (or what you have for key fields anyway)
select MYIDENTITYCOLUMN from MYTABLE where KeyField1 = ‘_KEYDATA’ order by KeyField1 desc
So since it was a success, there should be data.
and since order by descending if you don’t have a unique key other than that identity column, we should still find the last one just inserted.
the only way this would fail is if you had multiple integrations running at the same time with the same KeyField1 and now one COULD pull the same data as the 2nd map.
If that is a concern then you need to do it with your own SQL call as the first example.
patrick
From a workaround perspective:
1. don’t use a SQL proc destination, instead set the destination to a text file or something (since we need a destination)
Then in the ‘before document’ or ‘after document’ script you could use a .NET task to call the proc. Or actually maybe a SQL Task since now you are connected right to SQL and can call the proc directly with the source data fields.
Then since it is YOUR proc call you can capture the output of that identity either through the output param you have or I think the select SCOPE_IDENTITY() that would give you that after the call.
Once you have that, then you can update whatever you want since again is your script call.
Otherwise, assuming that we don’t want to do this ourselves, leave what you have as-is.
then use an ‘after document success’ to query the target tables directly with the key fields (or what you have for key fields anyway)
select MYIDENTITYCOLUMN from MYTABLE where KeyField1 = ‘_KEYDATA’ order by KeyField1 desc
So since it was a success, there should be data.
and since order by descending if you don’t have a unique key other than that identity column, we should still find the last one just inserted.
the only way this would fail is if you had multiple integrations running at the same time with the same KeyField1 and now one COULD pull the same data as the 2nd map.
If that is a concern then you need to do it with your own SQL call as the first example.
patrick
Jamie replied 5 years ago Smart ideas! Thanks so much.