I have a table [FCA_Batch] that will have a new batch row every time I process a feed that comes in as CSV and I insert into a SQL Table [FCA_staging]
Every row inserted into FCA_Staging will get the FCA_Batch_id of the row craeted to store metadata about that process.
I want to create the new row in FCA_Batch with Execute SQL Task. My SQL is
INSERT INTO [CA_BackFeed].[dbo].[FCA_Batch]
([Process_datetime])
VALUES
(getdate())
GO
SELECT ident_current('FCA_Batch')
I have ResultSet = Single row and on the result set tab
Resultname = NewID var name = User::BatchID
But when I execute I am getting
Error: No result rowset is associated with the execution of this query.
[Execute SQL Task] Error: An error occurred while assigning a value to variable "BatchID": "Exception from HRESULT: 0xC0015005".
Obviously I'm missing something. I'm sure this matter of creating a "header" row before creating the "child" rows is not uncommon so any suggestion on how to fix this or for an alternative best-practice implementation greatly appreciated.
TIA
Charles Hankey
Though a good deal is too strange to be believed, nothing is too strange to have happened.
- Thomas Hardy
Half the harm that is done in this world is due to people who want to feel important. They don't mean to do harm-- but the harm does not interest them. Or they do not see it, or they justify it because they are absorbed in the endless struggle to think well of themselves.
-- T. S. Eliot
Democracy is two wolves and a sheep voting on what to have for lunch.
Liberty is a well-armed sheep contesting the vote.
- Ben Franklin
Pardon him, Theodotus. He is a barbarian, and thinks that the customs of his tribe and island are the laws of nature.
