Attività Esegui SQL
Data aggiornamento: 15 settembre 2007
L'attività Esegui SQL consente di eseguire istruzioni SQL o stored procedure da un pacchetto. L'attività può includere una o più istruzioni SQL che vengono eseguite in ordine sequenziale. È possibile utilizzare l'attività Esegui SQL per gli scopi seguenti:
- Troncare una tabella o una vista per prepararla per l'inserimento di dati.
- Creare, modificare ed eliminare oggetti di database come tabelle e viste.
- Ricreare tabelle dei fatti e delle dimensioni prima di caricarvi i dati.
- Eseguire stored procedure.
- Salvare in una variabile il set di righe restituito da una query.
Per configurare l'attività Esegui SQL, procedere nel modo seguente:
- Specificare il tipo di gestione connessione da utilizzare per la connessione a un database.
- Specificare il tipo di set di risultati restituito dall'istruzione SQL.
- Specificare un timeout per l'istruzione SQL.
- Specificare l'origine dell'istruzione SQL.
- Indicare se l'attività salta la fase di preparazione per l'istruzione SQL.
- Se si utilizza il tipo di connessione ADO, è necessario indicare se l'istruzione SQL è una stored procedure. Per gli altri tipi di connessione questa proprietà è di sola lettura e ha sempre valore false.
L'attività Esegui SQL può essere usata in combinazione con i contenitori Ciclo Foreach e Ciclo For per eseguire più istruzioni SQL. Questi contenitori consentono di implementare flussi di controllo ripetuti in un pacchetto e possono eseguire più volte l'attività Esegui SQL. Utilizzando ad esempio il contenitore Ciclo Foreach, il pacchetto può enumerare i file in una cartella ed eseguire ripetutamente un'attività Esegui SQL per elaborare l'istruzione SQL archiviata in ogni file.
Connessione a un'origine dei dati
Per connettersi all'origine dei dati su cui eseguire l'istruzione SQL o la stored procedure, l'attività Esegui SQL può utilizzare diversi tipi di gestione connessione, elencati nella tabella seguente.
| Tipo di connessione | Gestione connessione |
|---|---|
EXCEL |
|
OLE DB |
|
ODBC |
|
ADO |
|
ADO.NET |
|
SQLMOBILE |
Creazione di istruzioni SQL
L'origine delle istruzioni SQL utilizzate da questa attività può essere una proprietà dell'attività che contiene un'istruzione, una connessione a un file che contiene una o più istruzioni oppure il nome di una variabile che contiene un'istruzione. Le istruzioni SQL devono essere scritte nel sottolinguaggio del sistema di gestione di database (DBMS) di origine.
Se le istruzioni SQL sono memorizzate in un file, per connettersi al file l'attività utilizzerà una gestione connessione file. Per ulteriori informazioni, vedere Gestione connessione file.
In Progettazione SSIS per creare query SQL è possibile digitare le istruzioni nella finestra di dialogo Editor attività Esegui SQL o utilizzare l'interfaccia utente grafica Generatore query.
[!NOTA] L'attività Esegui SQL non è in grado di elaborate correttamente le istruzioni SQL valide scritte al di fuori dell'attività stessa.
Invio di più istruzioni in un batch
Se un'attività Esegui SQL include più istruzioni, sarà possibile raggrupparle ed eseguirle come batch, utilizzando il comando GO per segnalare la fine del batch. Tutte le istruzioni SQL comprese tra due comandi GO vengono inviate in batch al provider OLE DB per l'esecuzione. Il comando SQL può includere più batch separati da comandi GO.
I tipi di istruzioni SQL che è possibile raggruppare in un batch sono soggetti a restrizioni. Per ulteriori informazioni, vedere Batches of Statements.
Se l'attività Esegui SQL esegue un batch di istruzioni SQL, al batch verranno applicate le regole seguenti:
- Può restituire un set di risultati una sola istruzione, che deve essere la prima del batch.
- Se il set di risultati utilizza associazioni di risultati, le query dovranno restituire lo stesso numero di colonne. Se le query restituiscono numeri di colonne diversi, l'attività avrà esito negativo. Le query eseguite dall'attività, ad esempio DELETE o INSERT, possono tuttavia avere esito positivo anche se l'attività non viene eseguita correttamente.
- Se le associazioni di risultati utilizzano nomi di colonne, la query dovrà restituire colonne con nomi uguali a quelli del set di risultati dell'attività. Se le colonne non sono presenti, l'attività avrà esito negativo.
- Se l'attività utilizza un'associazione di parametri, tutte le query incluse nel batch dovranno avere lo stesso numero e gli stessi tipi di parametri.
Esecuzione di comandi SQL con parametri
Le istruzioni SQL e le stored procedure utilizzano spesso parametri di input, parametri di output e codici restituiti. L'attività Esegui SQL supporta parametri di tipo Input, Output e ReturnValue. Il tipo Input viene utilizzato per i parametri di input, il tipo Output per i parametri di output e il tipo ReturnValue per i codici restituiti.
[!NOTA] È possibile utilizzare parametri in un'attività Esegui SQL solo se il provider di dati li supporta.
I parametri inclusi nei comandi SQL, comprese le query e le stored procedure, vengono mappati a variabili create nell'ambito dell'attività Esegui SQL, in un contenitore padre o nell'ambito del pacchetto. I valori delle variabili possono essere impostati in fase di progettazione o popolati dinamicamente in fase di esecuzione. È inoltre possibile mappare parametri a variabili di sistema. Per ulteriori informazioni, vedere Variabili in Integration Services e Variabili di sistema.
Nella sintassi del comando SQL possono essere utilizzati indicatori di parametro diversi, a seconda del tipo di connessione utilizzato dall'attività Esegui SQL. Per la gestione connessione ADO.NET, ad esempio, l'indicatore di parametro utilizzato nel comando SQL deve avere il formato @varParameter, mentre per la gestione connessione OLE DB deve essere costituito da un punto interrogativo (?).
Anche i nomi che è possibile utilizzare come nomi di parametro nei mapping tra variabili e parametri variano a seconda del tipo di gestione connessione. La gestione connessione ADO.NET, ad esempio, utilizza un nome definito dall'utente e con prefisso @, mentre la gestione connessione OLE DB richiede nomi di parametro costituiti dal valore numerico di un ordinale in base 0.
Nella tabella seguente sono riepilogati i requisiti dei comandi SQL, a seconda dei tipi di gestione connessione utilizzati dall'attività Esegui SQL.
| Tipo di connessione | Indicatore di parametro | Nome di parametro | Comando SQL di esempio |
|---|---|---|---|
ADO |
? |
Param1, Param2, … |
SELECT FirstName, LastName, Title FROM Person.Contact WHERE ContactID = ? |
ADO.NET |
@<parameter name> |
@<parameter name> |
SELECT FirstName, LastName, Title FROM Person.Contact WHERE ContactID = @parmContactID |
ODBC |
? |
1, 2, 3, … |
SELECT FirstName, LastName, Title FROM Person.Contact WHERE ContactID = ? |
EXCEL e OLE DB |
? |
0, 1, 2, 3, … |
SELECT FirstName, LastName, Title FROM Person.Contact WHERE ContactID = ? |
Parametri utilizzati con le gestioni connessioni ADO.NET
Le gestioni connessioni ADO.NET richiedono che il comando SQL utilizzi nomi di parametro come indicatori di parametro. È pertanto possibile mappare direttamente le variabili ai parametri. Se ad esempio la variabile @varName viene mappata a un parametro di nome @parName, fornirà il valore per il parametro @parName.
Parametri utilizzati con le gestioni connessioni EXCEL, ODBC e OLE DB
Le gestioni connessioni EXCEL, ODBC e OLE DB richiedono che il comando SQL utilizzi punti interrogativi (?) come indicatori di parametro e valori numerici in base 0 o in base 1 come nomi di parametro. Se l'attività Esegui SQL utilizza la gestione connessione ODBC, il nome del parametro mappato al primo parametro nella query è 1, altrimenti è 0. Per i parametri successivi, il valore numerico del nome del parametro indica il parametro del comando SQL a cui è mappato il nome di parametro. Il parametro di nome 3, ad esempio, è mappato al terzo parametro, rappresentato dal terzo punto interrogativo (?) nel comando SQL.
Per impostare i nomi dei parametri, le variabili sono mappate ai nomi di parametro e l'attività Esegui SQL utilizza il valore ordinale del nome di parametro per caricare i valori dalle variabili ai parametri.
Se l'attività Esegui SQL utilizza la gestione connessione OLE DB, è disponibile la proprietà BypassPrepare dell'attività. Questa proprietà deve essere impostata su true se l'attività Esegui SQL utilizza istruzioni SQL con parametri.
Quando si utilizza una Gestione connessione OLE DB, non è possibile utilizzare subquery con parametri perché l'attività Esegui SQL non può dedurre informazioni del parametro tramite il provider OLE DB. Tuttavia, è possibile utilizzare un'espressione per concatenare i valori del parametro nella stringa di query e impostare la proprietà SqlStatementSource dell'attività.
A seconda del provider utilizzato dalla gestione connessione, alcuni tipi di dati OLE DB potrebbero non essere supportati. Il driver per Excel, ad esempio, riconosce solo un insieme limitato di tipi di dati. Per ulteriori informazioni sul comportamento del provider Jet utilizzato insieme al driver per Excel, vedere Origine Excel.
Parametri utilizzati con le gestioni connessioni ADO
Le gestioni connessioni ADO richiedono che il comando SQL utilizzi punti interrogativi (?) come indicatori di parametro, ma come nomi di parametro è possibile utilizzare qualsiasi nome definito dall'utente, ad eccezione dei valori integer.
Per impostare i valori dei parametri, le variabili vengono mappate ai nomi di parametro e l'attività Esegui SQL utilizza il valore ordinale del nome di parametro nell'elenco dei parametri per caricare i valori dalle variabili ai parametri.
Utilizzo di parametri con la clausola WHERE
I comandi SELECT, INSERT, UPDATE e DELETE includono spesso la clausola WHERE, per specificare filtri che definiscono le condizioni che ogni riga nelle tabelle di origine deve soddisfare per essere qualificata per un comando SQL. I parametri specificano i valori del filtro per la clausola WHERE.
È possibile utilizzare indicatori di parametro per specificare dinamicamente i valori dei parametri. Le regole che determinano se è possibile utilizzare indicatori di parametro e nomi di parametro in un'istruzione SQL dipendono dal tipo di gestione connessione utilizzato dall'attività Esegui SQL.
Nella tabella seguente sono elencati esempi di comandi SELECT per tipo di gestione connessione. Le istruzioni INSERT, UPDATE e DELETE sono analoghe. Negli esempi l'istruzione SELECT viene utilizzata per recuperare dalla tabella Product del database AdventureWorks i prodotti con ProductID compreso tra i valori specificati da due parametri.
| Tipo di connessione | Sintassi dell'istruzione SELECT |
|---|---|
EXCEL, ODBC e OLEDB |
|
ADO |
|
ADO.NET |
|
Gli esempi richiedono parametri con i nomi seguenti:
- Per le gestioni connessioni EXCEL e OLED DB vengono utilizzati i nomi di parametro 0 e 1. Per il tipo di connessione ODBC vengono utilizzati 1 e 2.
- Per il tipo di connessione ADO è possibile utilizzare qualsiasi nome per i due parametri, ad esempio Param1 e Param2, ma entrambi i parametri devono essere mappati in base alla relativa posizione ordinale nell'elenco di parametri.
- Per il tipo di connessione ADO.NET vengono utilizzati i nomi di parametro @parmMinProductID e @parmMaxProductID.
Utilizzo di parametri con le stored procedure
Anche i comandi SQL che eseguono stored procedure possono utilizzare il mapping dei parametri. Come avviene per le regole delle query con parametri, anche le regole che determinano la modalità di utilizzo di indicatori di parametro e nomi di parametro dipendono dal tipo di gestione connessione utilizzato dall'attività Esegui SQL.
Nella tabella seguente sono elencati esempi di comandi EXEC per tipo di gestione connessione. Gli esempi eseguono la stored procedure uspGetBillOfMaterials nel database AdventureWorks. Tale stored procedure utilizza i parametri di input @StartProductID e @CheckDate.
La sintassi per l'utilizzo dei parametri di output richiede che dopo ogni indicatore di parametro venga specificata la parola chiave OUTPUT. Ad esempio EXEC myStoredProcedure ? OUTPUT.
Per ulteriori informazioni sull'utilizzo di parametri di input e output con le stored procedure Transact-SQL, vedere Parametri (Motore di database), Restituzione di dati utilizzando i parametri OUTPUT e EXECUTE (Transact-SQL).
Recupero dei valori dei codici restituiti
Una stored procedure può restituire un valore integer, denominato codice restituito, per indicare lo stato di esecuzione di una procedura. Per implementare codici restituiti nell'attività Esegui SQL, è necessario utilizzare parametri di tipo ReturnValue.
Nella tabella seguente sono elencati, per tipo di gestione connessione, esempi di comandi EXEC che implementano codici restituiti. In tutti gli esempi viene utilizzato un parametro di input. Le regole che determinano la modalità di utilizzo di indicatori di parametro e nomi di parametro con ReturnValue sono le stesse utilizzate per i parametri di tipo Input e Output.
In alcune sintassi non è supportato l'utilizzo di valori letterali come parametri. In tali casi è necessario specificare il valore del parametro utilizzando una variabile.
Per ulteriori informazioni sull'utilizzo di codici restituiti con le stored procedure Transact-SQL, vedere Restituzione di dati utilizzando un codice restituito e RETURN (Transact-SQL).
Impostazione del tipo di un set di risultati
A seconda del tipo di comando SQL, all'attività Esegui SQL può essere restituito o meno un set di risultati. Ad esempio, le istruzioni SELECT restituiscono in genere un set di risultati, ma questo non avviene per le istruzioni INSERT. Il set di risultati restituito da un'istruzione SELECT può contenere zero, una o più righe. Le stored procedure possono restituire anche un valore integer, detto codice restituito, che indica lo stato dell'esecuzione della procedura. In questo caso il set di risultati è costituito da una sola riga.
L'attività Esegui SQL supporta i tipi di set di risultati seguenti:
- Se la query non restituisce risultati, verrà utilizzato il set di risultati Nessuno. Questo set di risultati può essere utilizzato ad esempio per le query che aggiungono, modificano ed eliminano record in una tabella.
- Se la query restituisce una sola riga, verrà utilizzato il set di risultati Riga singola. Questo set di risultati può essere utilizzato ad esempio per una stored procedure che dà come risultato un codice restituito oppure per un'istruzione SELECT che restituisce un conteggio o una somma.
- Se la query restituisce più righe, verrà utilizzato il set di risultati Set dei risultati completo. Questo set di risultati può essere utilizzato ad esempio per un'istruzione SELECT che recupera tutte le righe di una tabella.
- Se la query restituisce un set di risultati in formato XML, verrà utilizzato il set di risultati XML. Questo set di risultati può essere utilizzato ad esempio per un'istruzione SELECT che include una clausola FOR XML.
Se nell'attività Esegui SQL viene utilizzato il Set dei risultati completo e la query restituisce più set di righe, l'attività restituisce solo il primo di tali set. Se questo set di righe genera un errore, l'attività segnala l'errore. Se altri set di righe generano errori, l'attività non li segnala.
L'attività Esegui SQL converte in stringhe qualsiasi valore restituito dall'istruzione SQL non costituito da stringhe. Vengono ad esempio convertiti in stringhe i valori con tipo di dati uniqueidentifier, bigint, decimal o numeric di SQL Server.
Popolamento di una variabile con un set di risultati
Se il set di risultati restituito da una query è una riga singola, un set di righe o di tipo XML, sarà possibile associarlo a una variabile definita dall'utente.
Se il tipo di set di risultati è Riga singola, è possibile associare una colonna nel risultato restituito a una variabile utilizzando il nome della colonna come nome del set di risultati oppure utilizzare la posizione ordinale della colonna nell'elenco di colonne come nome del set di risultati. Il nome del set di risultati per la query SELECT Color FROM Production.Product WHERE ProductID = ?, ad esempio, potrebbe essere Color o 0. Se la query restituisce più colonne e si desidera accedere ai valori in tutte le colonne, è necessario associare ogni colonna a una variabile diversa. Se si mappano le colonne alle variabili utilizzando numeri come nomi del set di risultati, i numeri riflettono l'ordine in cui le colonne vengono visualizzate nell'elenco di colonne della query. Nella query SELECT Color, ListPrice, FROM Production.Product WHERE ProductID = ?, ad esempio, viene utilizzato 0 per la colonna Color e 1 per la colonna ListPrice. La possibilità di utilizzare un nome di colonna come nome di un set di risultati dipende dal provider per il quale l'attività è configurata. Non tutti i provider consentono l'utilizzo di nomi di colonna.
Alcune query che restituiscono un singolo valore non includono nomi di colonna. L'istruzione SELECT COUNT (*) FROM Production.Product non restituisce ad esempio alcun nome di colonna. Per accedere al risultato restituito è possibile utilizzare la posizione ordinale, 0, come nome del risultato. Affinché sia possibile accedere al risultato restituito tramite il nome di colonna, la query deve includere una clausola AS <alias name> che specifichi un nome di colonna. Nell'istruzione SELECT COUNT (*)AS CountOfProduct FROM Production.Product è specificato il nome di colonna CountOfProduct. Per accedere alla colonna del risultato restituito è quindi possibile utilizzare il nome di colonna, CountOfProduct, o la posizione ordinale, 0.
Se il set di risultati è di tipo Set dei risultati completo o XML, sarà necessario utilizzare 0 come nome del set di risultati.
Quando si mappa una variabile a un set di risultati con il tipo di set di risultati Riga singola, la variabile deve avere un tipo di dati compatibile con quello della colonna contenuta nel set di risultati. Un set di risultati che contiene, ad esempio, una colonna con un tipo di dati String non può eseguire il mapping a una variabile con un tipo di dati numerico. Un set di risultati XML può eseguire il mapping solo a una variabile con il tipo di dati String o Object. Se la variabile ha il tipo di dati String, l'attività Esegui SQL restituisce una stringa e l'origine XML può utilizzare i dati XML. Se la variabile ha il tipo di dati Object, l'attività Esegui SQL restituisce un oggetto DOM (Document Object Model). Un Set dei risultati completo deve eseguire il mapping a una variabile con il tipo di dati Object. Il risultato restituito è un oggetto set di righe. È possibile scrivere attività personalizzate che esplorano l'oggetto set di righe e accedono alle informazioni relative alle colonne e ai dati contenuti.
Nella tabella seguente è disponibile un riepilogo dei tipi di dati delle variabili che è possibile mappare a set di risultati.
| Tipo di set di risultati | Tipo di dati della variabile | Tipo di oggetto |
|---|---|---|
A riga singola |
Qualunque tipo compatibile con la colonna del tipo nel set di risultati. |
Non applicabile |
Set dei risultati predefinito |
Object |
Se l'attività utilizza una gestione connessione nativa, incluse le gestioni connessioni ADO, OLE DB, Excel e ODBC, l'oggetto restituito è un oggetto Recordset ADO. Se l'attività utilizza una gestione connessione gestita, ad esempio la gestione connessione ADO.NET, l'oggetto restituito è un oggetto System.Data.DataSet. |
XML |
String |
String |
XML |
Object |
Se l'attività utilizza una gestione connessione nativa, incluse le gestioni connessioni ADO, OLE DB, Excel e ODBC, l'oggetto restituito è un oggetto MSXML6.IXMLDOMDocument. Se l'attività utilizza una gestione connessione gestita, ad esempio la gestione connessione ADO.NET, l'oggetto restituito è un oggetto System.Xml.XmlDocument. |
La variabile può essere definita nell'ambito dell'attività Esegui SQL o nell'ambito del pacchetto. Se la variabile ha ambito di pacchetto, il set di risultati sarà disponibile per altre attività e contenitori all'interno del pacchetto e per altri pacchetti eseguiti dalle attività Esegui pacchetto o Esegui pacchetto DTS 2000.
Quando si esegue il mapping di una variabile in un set di risultati di Riga singola, i valori che l'istruzione SQL restituisce e che non sono già stringhe potrebbero essere convertiti in stringhe. Se si verifica questa conversione o se questa conversione è implicita o esplicita, dipende dal tipo di gestione connessione utilizzata:
- Con una gestione connessione ADO.NET, non si verifica la conversione.
- Con una gestione connessione ADO o ODBC, si verifica questa conversione implicitamente.
- Con una gestione connessione OLE DB o Excel, la gestione connessione converte in modo esplicito i valori dei tipi seguenti, DBTYPE_I8, DBTYPE_UI8, DBTYPE_NUMERIC, DBTYPE_GUID e DBTYPE_BYTES, in stringhe.
Per informazioni sul caricamento di un set di risultati in una variabile, vedere Procedura: Mapping di set di risultati a variabili in un'attività Esegui SQL.
Voci di log personalizzate disponibili nell'attività Esegui SQL
Nella tabella seguente è indicata la voce di log personalizzata disponibile per l'attività Esegui SQL. Per ulteriori informazioni, vedere Implementazione della registrazione nei pacchetti e Messaggi personalizzati per la registrazione.
| Voce di log | Descrizione |
|---|---|
ExecuteSQLExecutingQuery |
Offre informazioni sulle fasi di esecuzione dell'istruzione SQL. Vengono scritte voci di log quando l'attività acquisisce la connessione al database, quando inizia a preparare l'istruzione SQL e al termine dell'esecuzione dell'istruzione SQL. La voce di log per la fase di preparazione include l'istruzione SQL utilizzata dall'attività. |
Risoluzione dei problemi relativi all'attività Esegui SQL
Da Microsoft SQL Server 2005 Service Pack 2 (SP2) è possibile registrare le chiamate eseguite dall'attività Esegui SQL a provider di dati esterni. Questa nuova funzionalità di registrazione può essere utilizzata per risolvere i problemi relativi ai comandi SQL eseguiti dall'attività Esegui SQL. Per registrare le chiamate eseguite dall'attività Esegui SQL a un provider di dati esterno, abilitare la registrazione dei pacchetti e selezionare l'evento Diagnostic a livello del pacchetto. Per ulteriori informazioni, vedere Risoluzione dei problemi relativi all'esecuzione di pacchetti.
Qualche volta un comando SQL o la stored procedure restituisce più set di risultati. Questi set di risultati non solo includono set di righe che sono il risultato di query SELECT, ma singoli valori che sono il risultato di istruzioni RAISERROR o PRINT. Fatta eccezione per la gestione connessioni ODBC, tutte le altre gestioni connessione ignorano i set di risultati che si presentano dopo il primo set di risultati. Pertanto, queste gestioni connessione ignorano un errore restituito da un comando SQL o una stored procedure quando l'errore non è parte del primo set di risultati.
Configurazione dell'attività Esegui SQL
È possibile impostare le proprietà a livello di programmazione oppure tramite Progettazione SSIS.
Per ulteriori informazioni sulle proprietà che è possibile impostare in Progettazione SSIS, fare clic su uno degli argomenti seguenti:
- Editor attività Esegui XML (pagina Generale)
- Editor attività Esegui SQL (pagina Mapping parametri)
- Editor attività Esegui SQL (pagina Set dei risultati)
- Pagina Espressioni
Per ulteriori informazioni sull'impostazione di queste proprietà in Progettazione SSIS, fare clic sull'argomento seguente:
Configurazione dell'attività Esegui SQL a livello di programmazione
Per ulteriori informazioni sull'impostazione di queste proprietà a livello di programmazione, fare clic sull'argomento seguente:
Vedere anche
Attività
Procedura: Mapping dei parametri delle query alle variabili in un'attività Esegui SQL
Concetti
Contenitore Ciclo Foreach
Contenitore Ciclo For
Attività di Integration Services
Creazione del flusso di controllo di un pacchetto
Altre risorse
Preparazione delle istruzioni SQL
Guida in linea e informazioni
Cronologia modifiche
| Versione | Cronologia |
|---|---|
15 settembre 2007 |
|
12 dicembre 2006 |
|
17 luglio 2006 |
|
14 aprile 2006 |
|
5 dicembre 2005 |
|