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 )
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.
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.1 (EDIT) Add a derived column and set ID as null(dt_int)
2.1 (EDIT) Map your column to output parameter of stored procedure that returns scope_identity()
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
User contributions licensed under CC BY-SA 3.0