SSIS populating cross-reference table

Question

Destination tables are as follows:

The source table is as follows:

Client

CustomerId FirstName LastName Email Address1 Address2 City Postcode

The table of persons in the target table is the base table (which will later be inherited by the new client table). So I'm trying to export a row from one table and populate 3 tables at the destination.

I managed to do it as follows:

• Get records from the source table (Customer)
• Create an empty AddressId field
• Fill in the address table using the OLE DB command line (it calls a stored procedure that returns SCOPE_IDENTITY (), which is displayed in the AddressId field)
• Repeat step 3 to populate the Person table (and retrieve PersonId
• Configure the PersonAddress cross-reference table using the PersonId and AddressId fields.

A screenshot of this package is below.

The biggest problem with this approach is that the OLE DB command task inserts line by line and makes the whole package extremely slow. Is it possible to achieve the same, but using fast boot?

I can do this with the OLE DB Command, which calls a stored procedure, and then

Answer 1

I do not think you need SSIS. You can use the OUTPUT INSERT clause, which returns all identification keys to a temporary table

Let's try to reproduce your script ...

---

set nocount on go create table Customer (CustomerId int, CustomerName varchar(100) null, Address1 varchar(100) null, Address2 varchar(100) ) create table [Person] (PersonId int identity, PersonName varchar(100) null) create table [Address] (AddressId int identity, AddressLine varchar(100) null) create table [PersonAddress] (AddressId int, PersonId int ) go -- create some data... insert into Customer (CustomerId) values ( 1000000 + convert(int, RAND() * 1000000) ) go 1000 update Customer set CustomerName = 'CustomerName ' + convert(varchar, CustomerId), Address1 = 'Address1 ' + convert(varchar, CustomerId), Address2 = 'Address2 ' + convert(varchar, CustomerId) go declare @identities_Person table ([rownumber] int identity, id int) declare @identities_Address table ([rownumber] int identity, id int) insert into Person (PersonName) output inserted.PersonId into @identities_Person select c.CustomerName from Customer c order by c.CustomerId insert into [Address] (AddressLine) output inserted.AddressId into @identities_Address select c.Address1 from Customer c order by c.CustomerId insert into [PersonAddress] (PersonId, AddressId) select p.id, a.id from @identities_Address a inner join @identities_Person p on p.rownumber = a.rownumber select * from PersonAddress pa inner join [Address] a on a.AddressId = pa.AddressId inner join [Person] p on p.PersonId = pa.PersonId