Multi insert with scope_identity how to?

1

I have a issue with SSIS OLE DB Command.

What I'm trying to do, is insert into "person" table and grab the newly created PersonID (table identity) from the insert, as I need this to create a relation into a PersonContactInfo table, this should be pretty simple.

I'm unable to write a stored procedure and there are around 10k rows so it should be okay with a OLE DB Command.

I have tried to write this code and it should match the:

DECLARE @PersonID int
---
--- create new person
---
INSERT INTO [dbo].[Personer_LF_TEST] (Fornavne, Efternavne, Oprindelse, OprettetDato, OprettetAf,OprindelseID)
VALUES (?, ?, 'Klimaquiz', ?, 499,7)

SET @PersonID = (SELECT SCOPE_IDENTITY())

---
--- update phonenumber
---
INSERT INTO [dbo].[Kontaktinfo_LF_TEST](PersonID, KontaktInfoTypeID, Notat, Tekst, Oprettet)
VALUES (@PersonID, 5, ?,?, Getdate() )

---
--- update Email
---
INSERT INTO [dbo].[Kontaktinfo_LF_TEST](PersonID, KontaktInfoTypeID, Notat, Tekst, Oprettet)
VALUES (@PersonID, 2,?, ?, Getdate() )

---
--- create persontypecode
---
INSERT INTO [dbo].[PersonArtskoder_LF_TEST](PersonID, ArtskoderID, Notat, Aktiv, CreatedBy, CreatedOn, ModifiedBy, ModifiedOn, StartDato, SlutDato)
VALUES (@PersonID,?, ?, 1, 209, getdate(), 209, getdate(), getdate(), NULL )

I have also tried just to run the first part with success like this:

-- Create new Person
INSERT INTO [dbo].[Personer_LF_TEST](Fornavne, Efternavne, Oprindelse, OprettetDato, OprettetAf, OprindelseID)
VALUES (?, ?,'Klimaquiz',?,499,7)

that inserts the 10k rows

If try and do this, it fails

-- Create new person
INSERT INTO [dbo].[Personer_LF_TEST](Fornavne, Efternavne, Oprindelse, OprettetDato, OprettetAf, OprindelseID)
VALUES(?, ?, 'Klimaquiz', ?, 499, 7)
-- Insert Phone number
INSERT INTO [dbo].[Kontaktinfo_LF_TEST](PersonID, KontaktInfoTypeID, Tekst, Oprettet)
VALUES (1, 5, ?, Getdate())

The error is:

Error at Data Flow Task [OLE DB Command [178]]: SSIS Error Code dts_E_OLEDBERROR. an OLE DB error has occurred.

Error Code: 0x80004005. An OLE DB record is available. Source: " Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: "Insert value list does not match column list"

But the input should match column list.

This is "kinda" the code that I would like the OLD DB Command to execute

DECLARE @PersonID int
---
--- create new person
---
INSERT  INTO [dbo].[Personer_LF_TEST](Fornavne, Efternavne, Oprindelse, OprettetDato, OprettetAf,OprindelseID)
VALUES  ('Simster', 'Mann', 'Klimaquiz', '03-25-2019', 499,7)
SET @PersonID = (SELECT scope_identity())
---
--- update phonenumber
---
INSERT  INTO [dbo].[Kontaktinfo_LF_TEST](PersonID, KontaktInfoTypeID, Notat, Tekst, Oprettet)
VALUES  (@PersonID, 5, 'WEB','22004400', Getdate() )
---
--- update Email
---
INSERT  INTO [dbo].[Kontaktinfo_LF_TEST](PersonID, KontaktInfoTypeID, Notat, Tekst, Oprettet)
VALUES  (@PersonID, 2,'WEB', 'Simster@codewithme.dk', Getdate() )
---
--- create persontypecode
---
INSERT  INTO [dbo].[PersonArtskoder_LF_TEST](PersonID, ArtskoderID, Notat, Aktiv, CreatedBy, CreatedOn, ModifiedBy, ModifiedOn, StartDato, SlutDato)
VALUES  (@PersonID,'30395', 'WEB', 1, 209, getdate(), 209, getdate(), getdate(), NULL )
sql-server
tsql
ssis
asked on Stack Overflow Apr 8, 2019 by simmi potoker • edited Nov 12, 2019 by marc_s

2 Answers

1

If I execute this query with dummy data in SSMS i have no problem executing It,

DECLARE @PersonID int
---
--- create new person
---
INSERT  INTO [dbo].[Personer_LF_TEST](Fornavne, Efternavne, Oprindelse, OprettetDato, OprettetAf,OprindelseID)
VALUES  ('TEST', 'Simmy', 'Klimaquiz', GETDATE(), 499,7)
SET @PersonID = (SELECT scope_identity())
---
--- update phonenumber
---
INSERT  INTO [dbo].[Kontaktinfo_LF_TEST](PersonID, KontaktInfoTypeID, Notat, Tekst, Oprettet)
VALUES  (@PersonID, 5, 'WEB',33445566, Getdate() )
---
--- update Email
---
INSERT  INTO [dbo].[Kontaktinfo_LF_TEST](PersonID, KontaktInfoTypeID, Notat, Tekst, Oprettet)
VALUES  (@PersonID, 2,'WEB', 'simster@codewithme.dk', Getdate() )
---
--- create persontypecode
---
INSERT  INTO [dbo].[PersonArtskoder_LF_TEST](PersonID, ArtskoderID, Notat, Aktiv, CreatedBy, CreatedOn, ModifiedBy, ModifiedOn, StartDato, SlutDato)
VALUES  (@PersonID,'30895', 'WEB', 1, 209, getdate(), 209, getdate(), getdate(), NULL ) 

I get 4 rows inserted with success but i can't get It to work in the OLE DB Command object. My thought was to add a column to the flow and then add a execute sql command after data flow, the execute SQL command would then based on the input in the column either insert, update or drop/do nothing. But this seems like a tedious way around it.

answered on Stack Overflow Apr 8, 2019 by simmi potoker • edited Apr 8, 2019 by a_horse_with_no_name
1

I think the best approach might be to break this into pieces.

Adding an edit to handle this is SSIS using this link http://radacad.com/output-parameter-of-stored-procedure-in-ole-db-command-ssis:

  1. Read your data into data flow.

1.1 (EDIT) Add a derived column and set ID as null(dt_int)

  1. Use an OLE DB command to insert a new row and return scope identity into a column.

2.1 (EDIT) Map your column to output parameter of stored procedure that returns scope_identity()

  1. Use a multicast and set 3 new paths to derived columns
  2. Use derived columns to enter the hardcoded values
  3. Use OLEDB Destination to insert all the records at once.

This allows you to bulk insert the 3 subsequent queries.

Late Edit:

Like I said, I am quick to C# script component and this is an example of how to do that...

how to get the last record number after inserting record to database in access

answered on Stack Overflow Apr 8, 2019 by KeithL • edited Apr 9, 2019 by KeithL

User contributions licensed under CC BY-SA 3.0