Analisi di formati di file di testo non standard con il componente script
Quando i dati di origine sono disposti in un formato non standard, può risultare utile consolidare tutta la logica di analisi in un singolo script anziché concatenare più trasformazioni di Integration Services per ottenere lo stesso risultato.
Esempio 1: analisi di record delimitati da righe
Esempio 2: divisione di record padre e figlio
[!NOTA]
Se si desidera creare un componente da riutilizzare più facilmente con più attività Flusso di dati e più pacchetti, è possibile utilizzare il codice di questo esempio di componente script come punto iniziale per un componente del flusso di dati personalizzato. Per ulteriori informazioni, vedere Sviluppo di un componente del flusso di dati personalizzato.
Esempio 1: analisi di record delimitati da righe
In questo esempio viene illustrato come utilizzare il componente script per analizzare in una tabella di destinazione un file di testo in cui ogni colonna di dati appare in una riga distinta.
Per ulteriori informazioni sulla configurazione del componente script per l'utilizzo come trasformazione nel flusso di dati, vedere Creazione di una trasformazione sincrona con il componente script e Creazione di una trasformazione asincrona con il componente script.
Per configurare l'esempio di componente script
Creare e salvare un file di testo denominato rowdelimiteddata.txt che contiene l'origine dati seguente:
FirstName: Nancy LastName: Davolio Title: Sales Representative City: Seattle StateProvince: WA FirstName: Andrew LastName: Fuller Title: Vice President, Sales City: Tacoma StateProvince: WA FirstName: Steven LastName: Buchanan Title: Sales Manager City: London StateProvince:Aprire Management Studio e connettersi a un'istanza di SQL Server.
Selezionare un database di destinazione e aprire una nuova finestra Query. Nella finestra Query eseguire lo script seguente per creare la tabella di destinazione:
create table RowDelimitedData ( FirstName varchar(32), LastName varchar(32), Title varchar(32), City varchar(32), StateProvince varchar(32) )Aprire BI Development Studio e creare un nuovo pacchetto di Integration Services denominato ParseRowDelim.dtsx.
Aggiungere una gestione connessione file flat al pacchetto, denominarla RowDelimitedData e configurarla per la connessione al file rowdelimiteddata.txt creato in un passaggio precedente.
Aggiungere una gestione connessione OLE DB al pacchetto e configurarla per la connessione all'istanza di SQL Server e al database in cui è stata creata la tabella di destinazione.
Aggiungere un'attività Flusso di dati al pacchetto, quindi fare clic sulla scheda Flusso di dati di Progettazione SSIS.
Aggiungere un'origine file flat al flusso di dati e configurarla per l'utilizzo della gestione connessione RowDelimitedData. Nella pagina Colonne di Editor origine file flat selezionare l'unica colonna esterna disponibile.
Aggiungere un componente script al flusso di dati e configurarlo come trasformazione. Connettere l'output dell'origine file flat al componente script.
Fare doppio clic sul componente script per visualizzare Editor trasformazione Script.
Nella pagina Colonne di input di Editor trasformazione Script selezionare l'unica colonna di input disponibile.
Nella pagina Input e output di Editor trasformazione Script selezionare Output 0 e impostare il relativo oggetto SynchronousInputID su 0. Creare 5 colonne di output, tutte di tipo stringa [DT_STR] con lunghezza 32:
FirstName
LastName
Title
City
StateProvince
Nella pagina Script di Editor trasformazione Script fare clic su Modifica script e immettere il codice illustrato nella classe ScriptMain dell'esempio. Chiudere l'ambiente di sviluppo dello script ed Editor trasformazione Script.
Aggiungere una destinazione SQL Server al flusso di dati. Configurarlo per l'utilizzo della gestione connessione OLE DB e della tabella RowDelimitedData. Connettere l'output del componente script a questa destinazione.
Eseguire il pacchetto. Al termine dell'esecuzione del pacchetto, esaminare i record nella tabella di destinazione di SQL Server.
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
Dim columnName As String
Dim columnValue As String
' Check for an empty row.
If Row.Column0.Trim.Length > 0 Then
columnName = Row.Column0.Substring(0, Row.Column0.IndexOf(":"))
' Check for an empty value after the colon.
If Row.Column0.Substring(Row.Column0.IndexOf(":")).TrimEnd.Length > 1 Then
' Extract the column value from after the colon and space.
columnValue = Row.Column0.Substring(Row.Column0.IndexOf(":") + 2)
Select Case columnName
Case "FirstName"
' The FirstName value indicates a new record.
Me.Output0Buffer.AddRow()
Me.Output0Buffer.FirstName = columnValue
Case "LastName"
Me.Output0Buffer.LastName = columnValue
Case "Title"
Me.Output0Buffer.Title = columnValue
Case "City"
Me.Output0Buffer.City = columnValue
Case "StateProvince"
Me.Output0Buffer.StateProvince = columnValue
End Select
End If
End If
End Sub
public override void Input0_ProcessInputRow(Input0Buffer Row)
{
string columnName;
string columnValue;
// Check for an empty row.
if (Row.Column0.Trim().Length > 0)
{
columnName = Row.Column0.Substring(0, Row.Column0.IndexOf(":"));
// Check for an empty value after the colon.
if (Row.Column0.Substring(Row.Column0.IndexOf(":")).TrimEnd().Length > 1)
// Extract the column value from after the colon and space.
{
columnValue = Row.Column0.Substring(Row.Column0.IndexOf(":") + 2);
switch (columnName)
{
case "FirstName":
// The FirstName value indicates a new record.
this.Output0Buffer.AddRow();
this.Output0Buffer.FirstName = columnValue;
break;
case "LastName":
this.Output0Buffer.LastName = columnValue;
break;
case "Title":
this.Output0Buffer.Title = columnValue;
break;
case "City":
this.Output0Buffer.City = columnValue;
break;
case "StateProvince":
this.Output0Buffer.StateProvince = columnValue;
break;
}
}
}
}
Esempio 2: divisione di record padre e figlio
In questo esempio viene illustrato come utilizzare il componente script per analizzare in tabelle di destinazione padre e figlio correttamente normalizzate un file di testo in cui una riga del separatore precede una riga di record padre seguita da un numero indefinito di righe di record figlio. Questo semplice esempio può essere facilmente adottato per file di origine che utilizzano più di una riga o colonna per ogni record padre e figlio, purché esista la possibilità di identificare l'inizio e la fine di ogni record.
.gif "Nota di attenzione") Attenzione |
|---|
Questo esempio viene riportato a scopo puramente dimostrativo. Se viene eseguito più di una volta, verranno inseriti valori di chiave duplicati nella tabella di destinazione. |
Per ulteriori informazioni sulla configurazione del componente script per l'utilizzo come trasformazione nel flusso di dati, vedere Creazione di una trasformazione sincrona con il componente script e Creazione di una trasformazione asincrona con il componente script.
Per configurare l'esempio di componente script
- Creare e salvare un file di testo denominato parentchilddata.txt che contiene l'origine dati seguente:
******** PARENT 1 DATA child 1 data child 2 data child 3 data child 4 data ******** PARENT 2 DATA child 5 data child 6 data child 7 data child 8 data ********
Aprire SQL Server Management Studio e connettersi a un'istanza di SQL Server.
Selezionare un database di destinazione e aprire una nuova finestra Query. Nella finestra Query eseguire lo script seguente per creare le tabelle di destinazione:
CREATE TABLE [dbo].[Parents]( [ParentID] [int] NOT NULL, [ParentRecord] [varchar](32) NOT NULL, CONSTRAINT [PK_Parents] PRIMARY KEY CLUSTERED ([ParentID] ASC) ) GO CREATE TABLE [dbo].[Children]( [ChildID] [int] NOT NULL, [ParentID] [int] NOT NULL, [ChildRecord] [varchar](32) NOT NULL, CONSTRAINT [PK_Children] PRIMARY KEY CLUSTERED ([ChildID] ASC) ) GO ALTER TABLE [dbo].[Children] ADD CONSTRAINT [FK_Children_Parents] FOREIGN KEY([ParentID]) REFERENCES [dbo].[Parents] ([ParentID])Aprire Business Intelligence Development Studio e creare un nuovo pacchetto di Integration Services denominato SplitParentChild.dtsx.
Aggiungere una gestione connessione file flat al pacchetto, denominarla ParentChildData e configurarla per la connessione al file parentchilddata.txt creato in un passaggio precedente.
Aggiungere una gestione connessione OLE DB al pacchetto e configurarla per la connessione all'istanza di SQL Server e al database in cui sono state create le tabelle di destinazione.
Aggiungere un'attività Flusso di dati al pacchetto, quindi fare clic sulla scheda Flusso di dati di Progettazione SSIS.
Aggiungere un'origine file flat al flusso di dati e configurarla per l'utilizzo della gestione connessione ParentChildData. Nella pagina Colonne di Editor origine file flat selezionare l'unica colonna esterna disponibile.
Aggiungere un componente script al flusso di dati e configurarlo come trasformazione. Connettere l'output dell'origine file flat al componente script.
Fare doppio clic sul componente script per visualizzare Editor trasformazione Script.
Nella pagina Colonne di input di Editor trasformazione Script selezionare l'unica colonna di input disponibile.
Nella pagina Input e output di Editor trasformazione Script selezionare Output 0, rinominarlo in ParentRecords e impostare il relativo oggetto SynchronousInputID su Nessuno. Creare 2 colonne di output:
ParentID (chiave primaria), di tipo integer con segno a quattro byte [DT_I4]
ParentRecord, di tipo stringa [DT_STR] con lunghezza 32.
Creare un secondo output e denominarlo ChildRecords. L'oggetto SynchronousInputID del nuovo output è già impostato su Nessuno. Creare 3 colonne di output:
ChildID (chiave primaria), di tipo integer con segno a quattro byte [DT_I4]
ParentID (chiave esterna), sempre di tipo integer con segno a 4 byte [DT_I4]
ChildRecord, di tipo stringa [DT_STR] con lunghezza 50.
Nella pagina Script di Editor trasformazione Script fare clic su Modifica script. Nella classe ScriptMain immettere il codice illustrato nell'esempio. Chiudere l'ambiente di sviluppo dello script ed Editor trasformazione Script.
Aggiungere una destinazione SQL Server al flusso di dati. Connettere l'output ParentRecords del componente script a questa destinazione. Configurarlo per l'utilizzo della gestione connessione OLE DB e della tabella Parents.
Aggiungere un'altra destinazione SQL Server al flusso di dati. Connettere l'output ChildRecords del componente script a questa destinazione. Configurarlo per l'utilizzo della gestione connessione OLE DB e della tabella Children.
Eseguire il pacchetto. Al termine dell'esecuzione del pacchetto, esaminare i record padre e figlio nelle due tabelle di destinazione di SQL Server.
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
Static nextRowIsParent As Boolean = False
Static parentCounter As Integer = 0
Static childCounter As Integer = 0
' If current row starts with separator characters,
' then following row contains new parent record.
If Row.Column0.StartsWith("***") Then
nextRowIsParent = True
Else
If nextRowIsParent Then
' Current row contains parent record.
parentCounter += 1
Me.ParentRecordsBuffer.AddRow()
Me.ParentRecordsBuffer.ParentID = parentCounter
Me.ParentRecordsBuffer.ParentRecord = Row.Column0
nextRowIsParent = False
Else
' Current row contains child record.
childCounter += 1
Me.ChildRecordsBuffer.AddRow()
Me.ChildRecordsBuffer.ChildID = childCounter
Me.ChildRecordsBuffer.ParentID = parentCounter
Me.ChildRecordsBuffer.ChildRecord = Row.Column0
End If
End If
End Sub
public override void Input0_ProcessInputRow(Input0Buffer Row)
{
int static_Input0_ProcessInputRow_childCounter = 0;
int static_Input0_ProcessInputRow_parentCounter = 0;
bool static_Input0_ProcessInputRow_nextRowIsParent = false;
// If current row starts with separator characters,
// then following row contains new parent record.
if (Row.Column0.StartsWith("***"))
{
static_Input0_ProcessInputRow_nextRowIsParent = true;
}
else
{
if (static_Input0_ProcessInputRow_nextRowIsParent)
{
// Current row contains parent record.
static_Input0_ProcessInputRow_parentCounter += 1;
this.ParentRecordsBuffer.AddRow();
this.ParentRecordsBuffer.ParentID = static_Input0_ProcessInputRow_parentCounter;
this.ParentRecordsBuffer.ParentRecord = Row.Column0;
static_Input0_ProcessInputRow_nextRowIsParent = false;
}
else
{
// Current row contains child record.
static_Input0_ProcessInputRow_childCounter += 1;
this.ChildRecordsBuffer.AddRow();
this.ChildRecordsBuffer.ChildID = static_Input0_ProcessInputRow_childCounter;
this.ChildRecordsBuffer.ParentID = static_Input0_ProcessInputRow_parentCounter;
this.ChildRecordsBuffer.ChildRecord = Row.Column0;
}
}
}
|
.gif "Icona di Integration Services (piccola)")
Rimanere aggiornati con Integration Services