How to import newly created GUID into multiple tables in same SSIS package

• chris.asaipillai 94746

  SSChasing Mays

  Points: 628

  More actions

  January 27, 2016 at 11:09 am

  #399633

  Hi

  I have an SSIS package which creates a GUID called [ThirdPartyGUID]

  I am writing the contents of an Excel spreadsheet into a main table called 'Airplanes'

  This has a GUID field called [ThirdPartyGUID] which is a FK field into another table called ThirdPartyInsurance

  In order for me to successfully populate the column [ThirdPartyGUID] in Airplanes, i need to populate the table

  ThirdPartyInsurance first.

  What's the best flow method of doing this, ie populating the ThirdPartyInsurance table first and then moving on

  with the final population of the Airplanes table in the same package?

  I thought of using Multicast....but not sure of this

• Lowell

  SSC Guru

  Points: 323482

  More actions

  January 27, 2016 at 12:12 pm

  #1854800

  there is a neat opportunity here to use the OUTPUT clause;

  it allows you to capture things like new guids or identities into a table, and then consume it on a subsequent insert/update etc.

  here's a rough example, that shows capturing of a guid and a value that was inserted; with that table, you could tie it back to your source you were inserting from, to insert into the next table

  DECLARE @MyResults TABLE(

  TheID UNIQUEIDENTIFIER ,

  newcode VARCHAR(30),

  oldcode VARCHAR(30) )

  CREATE TABLE [dbo].[ThirdPartyInsurance] (

  [ThirdPartyGUID] UNIQUEIDENTIFIER NOT NULL CONSTRAINT [DF__ThirdPart__Third__6B00BBAC] DEFAULT (newsequentialid()),

  VARCHAR(30) NULL,

  CONSTRAINT [PK__ThirdPar__15C583426918733A] PRIMARY KEY CLUSTERED ([ThirdPartyGUID] asc))

  INSERT INTO ThirdPartyInsurance(code)

  OUTPUT

  INSERTED.ThirdPartyGUID,

  INSERTED.code,

  NULL

  INTO @MyResults

  SELECT * FROM (

  SELECT 'aliceblue' UNION ALL SELECT 'antiquewhite' UNION ALL

  SELECT 'aqua*' UNION ALL SELECT 'aqua*' UNION ALL

  SELECT 'aquamarine' UNION ALL SELECT 'azure' UNION ALL

  SELECT 'beige' UNION ALL SELECT 'bisque' UNION ALL

  SELECT 'black*' UNION ALL SELECT 'black*' UNION ALL

  SELECT 'blanchedalmond' UNION ALL SELECT 'blue*' UNION ALL

  SELECT 'blue*' UNION ALL SELECT 'blueviolet' UNION ALL

  SELECT 'brown' UNION ALL SELECT 'burlywood' UNION ALL

  SELECT 'cadetblue'

  )MySampleData --just an inline placehoder for your real source table.

  SELECT * FROM @MyResults

  INSERT INTO Airplanes(ThirdPartyGUID,code,othercolumns)

  SELECT * t1.TheId,t2.code,t2.othercolumns FROM @MyResults t1

  INNER JOIN MySampleData t2 ON t1.code=t2.code

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!