Incorporare un'attività di profilazione dei dati nel flusso di lavoro del pacchetto

Si applica a:SQL Server SSIS Integration Runtime in Azure Data Factory

Il profiling dati e la pulizia non sono attività potenziali per un processo automatizzato nelle fasi iniziali. In SQL Server Integration Services, il risultato dell'attività di profilatura dei dati richiede in genere un'analisi visiva e una valutazione umana per determinare se le violazioni segnalate sono significative o eccessive. Anche dopo il riconoscimento di problemi di qualità dei dati, è comunque necessario definire con attenzione un piano ben studiato per tentare di individuare l'approccio migliore per la pulizia.

Tuttavia, una volta stabiliti i criteri per la qualità dei dati, è possibile automatizzare operazioni periodiche di analisi e pulizia dell'origine dati. Considerare gli scenari seguenti:

  • Controllo della qualità dei dati prima di un caricamento incrementale. Utilizzare l'attività di profilazione dei dati per calcolare il profilo del rapporto di valori Null della colonna per i nuovi dati destinati alla colonna CustomerName nella tabella Customers. Se la percentuale di valori nulli è superiore al 20%, invia un messaggio e-mail contenente l'output del profilo all'operatore e termina il pacchetto. In caso contrario, continuare il caricamento incrementale.

  • Automazione della pulizia quando vengono soddisfatte le condizioni specificate. Utilizzare l'attività Profilazione dati per calcolare il profilo di inclusione dei valori della colonna Stato rispetto a una tabella di riferimento degli stati e quello della colonna ZIP Code/Postal Code rispetto a una tabella di riferimento dei CAP. Se l'attendibilità dell'inclusione dei valori di stato è minore dell'80%, ma quella dei valori di ZIP Code/Postal Code è maggiore del 99%, emergono due indicazioni. Innanzitutto, i dati relativi allo stato sono errati. In secondo luogo, i dati relativi al codice ZIP/codice postale sono corretti. Avvia un'attività di flusso di dati che ripulisca i dati relativi allo stato/provincia eseguendo una ricerca del valore corretto dello stato/provincia in base al valore corrente del CAP/codice postale.

Dopo aver ottenuto un flusso di lavoro in cui è possibile incorporare l'attività Flusso di dati, è necessario identificare i passaggi richiesti per aggiungere questa attività. Nella sezione seguente viene descritto il processo generale di incorporazione dell'attività Flusso di dati. Le ultime due sezioni descrivono come connettere l'attività Flusso di dati o direttamente a un'origine dati oppure ai dati trasformati nel Flusso di dati.

Definizione di un flusso di lavoro generale per l'attività Flusso di dati

La procedura seguente descrive l'approccio generale per utilizzare i risultati dell'attività di profilazione dati nel flusso di lavoro di un pacchetto.

Per utilizzare l'output dell'attività di profilazione dei dati tramite codice in un pacchetto

  1. Aggiungere e configurare l'attività di profilazione dei dati in un pacchetto.

  2. Configurare le variabili del pacchetto che conterranno i valori che si desidera recuperare dai risultati del profilo.

  3. Aggiungere e configurare un'attività Script. Collegare l'attività Script all'attività di profilazione dei dati. Nell'attività Script, scrivere il codice che legge i valori desiderati dal file di output dell'attività di Profilazione dati e popola le variabili del pacchetto.

  4. Nei vincoli di precedenza che connettono l'attività Script ai rami a valle nel flusso di lavoro, scrivere espressioni che utilizzano i valori delle variabili per indirizzare il flusso di lavoro.

Quando si integra l'attività di profilazione dei dati nel flusso di lavoro di un pacchetto, tenere presenti queste due caratteristiche dell'attività:

  • Output dell'attività. L'attività di profilazione dei dati scrive il proprio output in un file o in una variabile del pacchetto in formato XML secondo lo schema DataProfile.xsd. Pertanto, è necessario eseguire una query sull'output XML se si desidera utilizzare i risultati del profilo nel flusso di lavoro condizionale di un pacchetto. Puoi utilizzare facilmente il linguaggio di query XPath per interrogare questo output XML. Per esaminare la struttura di questo output XML, è possibile aprire un file di output di esempio o lo schema stesso. Per aprire il file di output o lo schema, è possibile usare Microsoft Visual Studio, un altro editor XML o un editor di testo, ad esempio il Blocco note.

    Nota

    Alcuni risultati del profilo visualizzati nel Visualizzatore profilo dati sono valori calcolati che non si trovano direttamente nell'output. Ad esempio, l'output del profilo del rapporto di valori null nella colonna contiene il numero totale di righe e il numero di righe che contengono valori null. È necessario eseguire una query su questi due valori, quindi calcolare la percentuale di righe che contengono valori Null per ottenere il rapporto di valori Null nella colonna.

  • Input dell'attività. L'attività di profilazione dei dati legge i dati di input dalle tabelle di SQL Server. Pertanto, è necessario salvare i dati presenti in memoria in tabelle di staging se si desidera eseguire il profiling di dati già caricati e trasformati nel flusso di dati.

Nelle sezioni seguenti questo flusso di lavoro generale viene applicato al profiling di dati provenienti direttamente da un'origine dati esterna o trasformati dall'attività Flusso di dati. Viene inoltre illustrato come gestire i requisiti di input e di output dell'attività Flusso di dati.

Collegamento diretto dell'attività di profilazione dei dati a una fonte dati esterna

L'attività di profilazione dei dati consente di profilare dati provenienti direttamente da una fonte dati. Per illustrare questa funzionalità, nell'esempio seguente viene utilizzata l'attività di profiling-dati per calcolare un profilo del rapporto di null per colonna nelle colonne della tabella Person.Address nel database AdventureWorks2025. Quindi, questo esempio utilizza un'attività Script per recuperare i risultati dal file di output e popolare le variabili del pacchetto, che possono essere utilizzate per controllare il flusso di lavoro.

Nota

Per questo semplice esempio è stata selezionata la colonna AddressLine2, in quanto contiene una percentuale elevata di valori Null.

L'esempio è costituito dai passaggi seguenti:

  • Configurazione dei gestori connessioni che si collegano all'origine dati esterna e al file di output che conterrà i risultati del profilo.

  • Configurazione delle variabili del pacchetto che conterranno i valori necessari all'attività di profilazione dei dati.

  • Configurazione dell'attività di profilazione dei dati per calcolare il profilo del rapporto di valori null della colonna.

  • Configurazione dell'attività Script per elaborare l'output XML dell'attività di profilazione dei dati.

  • Configurazione dei vincoli di precedenza che controlleranno quali ramificazioni successive del flusso di lavoro verranno eseguite in funzione dei risultati dell'attività di profilazione dei dati.

Configurare i gestori delle connessioni

In questo esempio, sono presenti due gestori delle connessioni:

  • Un gestore di connessione ADO.NET che si connette al database AdventureWorks2025.

  • Un gestore di connessione file che crea il file di output che conterrà i risultati dell'attività di profilazione dei dati.

Per configurare i gestori delle connessioni

  1. In SQL Server Data Tools (SSDT) creare un nuovo pacchetto di Integration Services.

  2. Aggiungere una gestione connessione ADO.NET al pacchetto. Configurare questo gestore di connessione per utilizzare il provider di dati .NET per SQL Server (SqlClient) e per connettersi a un'istanza disponibile del database AdventureWorks2025.

    Per impostazione predefinita, il nome della gestione connessione è <nome server>.AdventureWorks1.

  3. Aggiungere un gestore di connessione file al pacchetto. Configurare questo gestore connessioni per creare il file di output per l'attività di profilatura dei dati.

    In questo esempio viene utilizzato il nome file DataProfile1.xml. Per impostazione predefinita, la gestione connessione e il file hanno lo stesso nome.

Configurare le variabili del pacchetto

In questo esempio vengono utilizzate due variabili del pacchetto:

  • La variabile ProfileConnectionName trasmette il nome del gestore di connessione File all'attività Script Task.

  • La variabile AddressLine2NullRatio passa il rapporto di valori Null calcolato per questa colonna dall'attività Script al pacchetto.

Per configurare le variabili del pacchetto che conterranno i risultati del profilo

  • Nella finestra Variabili aggiungere e configurare le due variabili del pacchetto seguenti:

    • Immettere il nome ProfileConnectionNameper una delle variabili e impostare il tipo di questa variabile su String.

    • Immettere il nome AddressLine2NullRatioper l'altra variabile e impostare il tipo di questa variabile su Double.

Configurare l'attività di profilazione dei dati

L'attività di profilazione dei dati deve essere configurata come segue:

  • Per usare come input i dati forniti dalla gestione connessione ADO.NET.

  • Per eseguire un profilo del rapporto di valori null della colonna nei dati di input.

  • Per salvare i risultati del profilo nel file associato al gestore connessione File.

Per configurare l'attività di Data Profiling

  1. Aggiungere un'attività di profilazione dei dati al flusso di controllo.

  2. Apri l'Editor attività di profilazione dei dati per configurare l'attività.

  3. Nella pagina Generale dell'editor, per Destinazione, selezionare il nome della gestione connessione file configurata in precedenza.

  4. Nella pagina Richieste di profilo dell'editor, crea un nuovo profilo del rapporto di valori null nella colonna.

  5. Nel riquadro Proprietà richiesta, per ConnectionManager, selezionare la gestione connessione ADO.NET configurata in precedenza. Quindi, per TableOrView, selezionare Person.Address.

  6. Chiudere l'Editor delle attività di profilazione dei dati.

Configurare attività Script

L'attività Script deve essere configurata per recuperare i risultati dal file di output e popolare le variabili del pacchetto configurate in precedenza.

Per configurare l'attività Script

  1. Aggiungere un'attività Script al flusso di controllo.

  2. Collegare l'attività Script all'attività di profilazione dei dati.

  3. Aprire l'Editor attività Script per configurare l'attività.

  4. Nella pagina Script selezionare il linguaggio di programmazione preferito. Quindi, rendere disponibili le due variabili del pacchetto per lo script:

    1. Per ReadOnlyVariablesselezionare ProfileConnectionName.

    2. Per ReadWriteVariablesselezionare AddressLine2NullRatio.

  5. Selezionare Modifica script per aprire l'ambiente di sviluppo dello script.

  6. Aggiungere un riferimento allo spazio dei nomi System.Xml.

  7. Immettere il codice di esempio che corrisponde al linguaggio di programmazione in uso:

    Imports System  
Imports Microsoft.SqlServer.Dts.Runtime  
Imports System.Xml  

Public Class ScriptMain  

  Private FILENAME As String = "C:\ TEMP\DataProfile1.xml"  
  Private PROFILE_NAMESPACE_URI As String = "https://schemas.microsoft.com/DataDebugger/"  
  Private NULLCOUNT_XPATH As String = _  
    "/default:DataProfile/default:DataProfileOutput/default:Profiles" & _  
    "/default:ColumnNullRatioProfile[default:Column[@Name='AddressLine2']]/default:NullCount/text()"  
  Private TABLE_XPATH As String = _  
    "/default:DataProfile/default:DataProfileOutput/default:Profiles" & _  
    "/default:ColumnNullRatioProfile[default:Column[@Name='AddressLine2']]/default:Table"  

  Public Sub Main()  

    Dim profileConnectionName As String  
    Dim profilePath As String  
    Dim profileOutput As New XmlDocument  
    Dim profileNSM As XmlNamespaceManager  
    Dim nullCountNode As XmlNode  
    Dim nullCount As Integer  
    Dim tableNode As XmlNode  
    Dim rowCount As Integer  
    Dim nullRatio As Double  

    ' Open output file.  
    profileConnectionName = Dts.Variables("ProfileConnectionName").Value.ToString()  
    profilePath = Dts.Connections(profileConnectionName).ConnectionString  
    profileOutput.Load(profilePath)  
    profileNSM = New XmlNamespaceManager(profileOutput.NameTable)  
    profileNSM.AddNamespace("default", PROFILE_NAMESPACE_URI)  

    ' Get null count for column.  
    nullCountNode = profileOutput.SelectSingleNode(NULLCOUNT_XPATH, profileNSM)  
    nullCount = CType(nullCountNode.Value, Integer)  

    ' Get row count for table.  
    tableNode = profileOutput.SelectSingleNode(TABLE_XPATH, profileNSM)  
    rowCount = CType(tableNode.Attributes("RowCount").Value, Integer)  

    ' Compute and return null ratio.  
    nullRatio = nullCount / rowCount  
    Dts.Variables("AddressLine2NullRatio").Value = nullRatio  

    Dts.TaskResult = Dts.Results.Success  

  End Sub  

End Class  

    using System;  
using Microsoft.SqlServer.Dts.Runtime;  
using System.Xml;  

public class ScriptMain  
{  

  private string FILENAME = "C:\\ TEMP\\DataProfile1.xml";  
  private string PROFILE_NAMESPACE_URI = "https://schemas.microsoft.com/DataDebugger/";  
  private string NULLCOUNT_XPATH = "/default:DataProfile/default:DataProfileOutput/default:Profiles" + "/default:ColumnNullRatioProfile[default:Column[@Name='AddressLine2']]/default:NullCount/text()";  
  private string TABLE_XPATH = "/default:DataProfile/default:DataProfileOutput/default:Profiles" + "/default:ColumnNullRatioProfile[default:Column[@Name='AddressLine2']]/default:Table";  

  public void Main()  
  {  

    string profileConnectionName;  
    string profilePath;  
    XmlDocument profileOutput = new XmlDocument();  
    XmlNamespaceManager profileNSM;  
    XmlNode nullCountNode;  
    int nullCount;  
    XmlNode tableNode;  
    int rowCount;  
    double nullRatio;  

    // Open output file.  
    profileConnectionName = Dts.Variables["ProfileConnectionName"].Value.ToString();  
    profilePath = Dts.Connections[profileConnectionName].ConnectionString;  
    profileOutput.Load(profilePath);  
    profileNSM = new XmlNamespaceManager(profileOutput.NameTable);  
    profileNSM.AddNamespace("default", PROFILE_NAMESPACE_URI);  

    // Get null count for column.  
    nullCountNode = profileOutput.SelectSingleNode(NULLCOUNT_XPATH, profileNSM);  
    nullCount = (int)nullCountNode.Value;  

    // Get row count for table.  
    tableNode = profileOutput.SelectSingleNode(TABLE_XPATH, profileNSM);  
    rowCount = (int)tableNode.Attributes["RowCount"].Value;  

    // Compute and return null ratio.  
    nullRatio = nullCount / rowCount;  
    Dts.Variables["AddressLine2NullRatio"].Value = nullRatio;  

    Dts.TaskResult = Dts.Results.Success;  

  }  

}

    Nota

    Il codice di esempio mostrato in questa procedura mostra come caricare da un file l'output dell'attività di profilatura dei dati. Per caricare invece il risultato dell'attività di Profilatura dati da una variabile del pacchetto, vedere il codice di esempio alternativo riportato di seguito a questa procedura.

  8. Chiudete l'ambiente di sviluppo dello script, quindi chiudete l'Editor attività script.

Codice alternativo: lettura dell'output del profilo da una variabile

La procedura precedente mostra come caricare l'output dell'attività di profilatura dei dati da un file. Un metodo alternativo consiste nel caricare questo output da una variabile del pacchetto. Per caricare l'output da una variabile, è necessario apportare le seguenti modifiche al codice di esempio:

  • Chiamare il metodo LoadXml della classe XmlDocument anziché il metodo Load .

  • Nell'editor attività Script aggiungere il nome della variabile del pacchetto che contiene l'output del profilo all'elenco ReadOnlyVariables dell'attività.

  • Passare il valore stringa della variabile al metodo LoadXML come illustrato nell'esempio di codice seguente. In questo esempio viene utilizzato "ProfileOutput" come nome della variabile del pacchetto che contiene l'output del profilo.

    Dim outputString As String  
outputString = Dts.Variables("ProfileOutput").Value.ToString()  
...  
profileOutput.LoadXml(outputString)  

    string outputString;  
outputString = Dts.Variables["ProfileOutput"].Value.ToString();  
...  
profileOutput.LoadXml(outputString);

Configurare i vincoli di precedenza

I vincoli di precedenza devono essere configurati in modo da controllare quali rami successivi nel flusso di lavoro vengono eseguiti in base ai risultati dell'attività di profilazione dei dati.

Per configurare i vincoli di precedenza

  • Nei vincoli di precedenza che connettono l'attività Script ai rami a valle nel flusso di lavoro, scrivere espressioni che utilizzano i valori delle variabili per indirizzare il flusso di lavoro.

    Ad esempio, è possibile impostare Operazione valutazione del vincolo di precedenza su Espressione e vincolo. È quindi possibile utilizzare @AddressLine2NullRatio < .90 come valore dell'espressione. In questo modo il flusso di lavoro segue il percorso selezionato quando le attività precedenti vengono completate e quando la percentuale di valori Null nella colonna selezionata è minore del 90%.

Collegamento dell'attività di profilazione dei dati ai dati trasformati del flusso di dati

Invece di profilare i dati direttamente da un'origine dati, è possibile profilare i dati che sono già stati caricati e trasformati nel flusso di dati. L'attività Profiling dati funziona, tuttavia, solo per dati persistenti, non per dati in memoria. Pertanto, è necessario utilizzare dapprima un componente di destinazione per salvare i dati trasformati in una tabella di staging.

Nota

Quando si configura l'attività di profilazione dei dati, è necessario selezionare tabelle e colonne esistenti. Pertanto, è necessario creare la tabella di staging in fase di progettazione prima di poter configurare l'attività. In altri termini, questo scenario non consente l'utilizzo di una tabella temporanea creata in fase di esecuzione.

Dopo aver salvato i dati in una tabella di staging, è possibile effettuare le azioni seguenti:

  • Utilizzare l'attività Data Profiling per profilare i dati.

  • Utilizzare un'attività Script per leggere i risultati come descritto in precedenza in questo argomento.

  • Utilizzare questi risultati per indirizzare il flusso di lavoro successivo del pacchetto.

La procedura seguente illustra l'approccio generale per utilizzare l'attività di profiling dei dati al fine di eseguire il profiling dei dati trasformati dal flusso di dati. Molti passaggi sono simili a quelli descritti in precedenza per il profiling dei dati provenienti direttamente da un'origine dati esterna. Può essere necessario rivedere questi passaggi precedenti per ulteriori informazioni su come configurare i vari componenti.

Per utilizzare l'attività di profilazione dei dati nel flusso di dati

  1. In SQL Server Data Tools (SSDT) creare un pacchetto.

  2. Nel flusso di dati aggiungere, configurare e connettere le origini e le trasformazioni appropriate.

  3. Nel flusso di dati, aggiungi, configura e connetti un componente di destinazione che salva i dati trasformati in una tabella di staging.

  4. Nel flusso di controllo, aggiungere e configurare un'attività di profilazione dei dati che calcola i profili desiderati sui dati trasformati nella tabella di staging. Collegare l'attività di profilazione dei dati all'attività di flusso di dati.

  5. Configurare le variabili del pacchetto che conterranno i valori che si desidera recuperare dai risultati del profilo.

  6. Aggiungere e configurare un'attività Script. Collegare l'attività Script all'attività di profilazione dei dati. Nell'attività Script, scrivere codice in grado di leggere i valori desiderati dall'output dell'attività di profilazione dei dati e di popolare le variabili del pacchetto.

  7. Nei vincoli di precedenza che connettono l'attività Script ai rami a valle nel flusso di lavoro, scrivere espressioni che utilizzano i valori delle variabili per indirizzare il flusso di lavoro.

Contenuti correlati

  • Last updated on