Integration Services and Identity Columns

Question

I'm a little new to SSIS, and although the whole system seems simple, I don't understand the conceptual process that I need to go through in this scenario:

• You need to match the tables of invoices and invoices from the source database to two equivalent tables in the destination database — with different identification values.

• For each account inserted, I need to get the identifier that it was assigned, and then insert all of its lines that refer to this new identity

• In the invoices there is a surrogate number (invoice number), however, they may also encounter account numbers in the target system, so they will also need to be renumbered.

This should be a common integration scenario - is there a common solution?

Answer 1

Chris KL - You are right that this is more difficult than you might expect. I have three methods for this that work in different situations:

• If the data you download is small (hundreds or thousands, but not hundreds of thousands), you can do this: use the OLEDB command, which performs one insert for each parent row and returns the identifier value; then downstream of this, attach the output from this to the child strings and insert them. Benefit: Intuitive. Disadvantage: it does not scale well. This method is documented online and owes you Google.

• If we are talking about a larger system where you need bulk loading, then there are two more flavors:

. ( , ), , SSIS script , id, Identity Insert On, Identity Insert Off. script - SSIS . : , . : , - . .

. , , , - , : , , (-, ). Lookup, - . , , - .

Answer 2

, / , . , , , .

- IDENTITY. , , , , IDENTITY_INSERT.

SET IDENTITY_INSERT MyTable ON

INSERT INTO MyTable (
       MyIdCol,
       Etc…
       ) 
SELECT SourceIdCol,
       Etc…
  FROM MySourceTable

    SET IDENTITY_INSERT MyTable OFF

, , , . ... (, IDENTITY) ? . , , - SourceSystemId, .

, : SSIS IDENTITY_INSERT. , , - SQL, . script . SQL, , .