Inserisci una singola riga e restituisce la sua chiave primaria

In SSIS, come faccio a utilizzare l’attività Esegui SQL per inserire una singola riga senza parametri e recuperare la chiave primaria in modo da poterla impostare sulla variabile utente? La mia domanda di inserimento è semplicemente:

INSERT INTO [AdWords.ImportData] (EndDate) VALUES (null) 

Bella domanda, mi ci sono voluti alcuni tentativi per capirlo. Dichiarare una variabile SSIS di tipo Int32 (a meno che non sia necessario dimensionare per un bigint o numerico). Ho scelto tablePk come mio.

opzione 1

Esegui attività SQL

  • Scheda Generale

ResultSet: None

SQL

 INSERT INTO dbo.ImportData (EndDate) VALUES (NULL); SELECT ? = SCOPE_IDENTITY() 
  • Scheda Mappatura parametri

Nome variabile: Utente :: tablePk

Direzione: uscita

Tipo di dati: lungo

Nome parametro: 0

Dimensione del parametro: -1

opzione 2

Questa era la soluzione originale in quanto non riuscivo a ingannare come ottenere il segnaposto ? in una query normale . Non poteva essere semplice come quello che avevo sopra, tranne che lo era.

L’unica differenza è la query utilizzata

SQL

 DECLARE @sql nvarchar(500) , @paramDef nvarchar(500) SELECT @sql = N'INSERT INTO dbo.ImportData (EndDate) VALUES (NULL); SELECT @ident = SCOPE_IDENTITY();' , @paramDef = N'@ident int OUTPUT' EXECUTE sp_executesql @sql, @paramDef, @ident = ? OUTPUT 

Opzione 3

Se si sta utilizzando un stream di dati, delineo un approccio su Come aggiungere il set di risultati da un’istruzione T-SQL a un stream di dati? In breve, è necessario aggiungere una colonna nel stream di dati prima di un comando OLE DB. All’interno del comando OLE DB, la colonna vuota verrà mappata in un parametro OUTPUT dalla stored procedure e quindi, non appena la procedura memorizzata viene triggersta, sostituirà la colonna con il valore della procedura.

In alternativa alla versione di Bilinkc, senza parametri:

Esegui attività SQL

Scheda Generale ResultSet: Single Row

SQL

 INSERT INTO dbo.ImportData (EndDate) VALUES (NULL); SELECT SCOPE_IDENTITY() AS LastId 

Nella mapping del set di risultati a riga singola, immettere LastId nella casella del nome del risultato e mappare sulla variabile.

Potrebbe essere leggermente più veloce con un singolo parametro di output (la versione di bilinkc), dipende da come SSIS lo fa ‘sotto le copertine’ e se crea un datareader completo rispetto a una singola chiamata sp_ExecuteSQL con parametro di output.

Ecco un’altra alternativa che penso sia molto pulita. Usa la syntax OUTPUT e il Result Set al posto di Parameter Mapping, che richiede meno configurazione.

 insert dbo.ImportData (EndDate) output inserted.Id values (NULL); 
  • Sostituisci l’ Id in questa istruzione con il nome della colonna di identity framework.
  • Usa “Singola riga” come tipo di Risultati.
  • Nessuna mapping dei parametri
  • Aggiungi questo alla scheda Set risultati: Nome risultato: 0 (che significa la prima colonna) e seleziona il nome della variabile es. User::tablePk (Tipo variabile: Int32)