Quando abbiamo la necessità di leggere da un file csv, oppure delimitato da tabulazioni, utilizzare il flat file connection manager risulta utilissimo.
Con esso infatti è possibile leggere il file sorgente convertendo ogni colonna in un vero e proprio campo (fortemente tipizzato) da poter utilizzare poi nelle nostre trasformazioni successive. Come già indicato in questo post, il flat file connection manager ci consente di selezionare il carattere di separazione di ogni colonna, sia dalla lista già fornita, sia indicando il carattere che più ci aggrada.. Può capitare però di incappare in particolari casi, in cui il carattere di separazione è, per scelta di chi ci fornisce i dati, non "indicabile" nella lista del controllo SSIS.. E' raro trovare questi separatori (ad esempio sugli NG di microsoft, alcuni hanno chiesto come poter splittare un file separato da un codice ASCII 1 e la cui riga termina con ASCII 2) però è possibile..
Vi è da considerare l'ipotesi di utilizzo della bcp utility che consente di specificare, tramite le opzioni -t e -r rispettivamente il delimitatore di colonna e quello di riga, però proveremo a vedere com'è possibile personalizzare la suddivisione del flat file anche tramite SSIS.
Partiamo dal seguente scenario: supponiamo di ricevere un file da una piattaforma diversa da quella che stiamo utilizzando, formattato in questo modo:
A1-separatore-B1-separatore-C1-fineriga-
A2-separatore-B2-separatore-C2-fine_riga-
A3-separatore-B3-separatore-C3-fine_riga-
A4-separatore-B4-separatore-C4-fine_riga-
A5-separatore-B5-separatore-C5-fine_riga-
Assumiamo che separatore sia un ASCII 1 e fine_riga sia un ASCII 2 e supponiamo di voler ottenere una tabella a tre campi che possa contenere il file correttamente suddiviso. Un flat file connection manager non è sufficiente ai nostri scopi, perciò dobbiamo per forza di cose ricorrere all'utilizzo di script.
Infatti, una delle possibili soluzioni, è proprio quella di utilizzare Script Component in Data Flow in modalità Sorgente Dati. Ecco come procedere:
1) Control Flow - Creazione delle variabili ROW_DELIMITER e COLUMN_DELIMITER, intere, che conterranno il codice ASCII personalizzato (nell'esempio 2 e 1) e della variabile FileName, che contiene il nome del file da creare per la simulazione.
2) Control Flow - Aggiungere uno Script Task per la creazione del file di testo "simulato", corredato di questo semplice script:
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.IO
Public Class ScriptMain
Public Sub Main()
If File.Exists("C:\flat.ale") Then File.Delete("C:\flat.ale")
Dim sw As New StreamWriter("C:\flat.ale")
sw.Write("A1" & Chr(1) & "B1" & Chr(1) & "C1" & Chr(2))
sw.Write("A2" & Chr(1) & "B2" & Chr(1) & "C2" & Chr(2))
sw.Write("A3" & Chr(1) & "B3" & Chr(1) & "C3" & Chr(2))
sw.Write("A4" & Chr(1) & "B4" & Chr(1) & "C4" & Chr(2))
sw.Write("A5" & Chr(1) & "B5" & Chr(1) & "C5" & Chr(2))
sw.Close()
Dts.TaskResult = Dts.Results.Success
End Sub
End Class
3) Control Flow - Aggiunta di un DataFlow Task
4) Spostarsi sul Data Flow - Aggiunta di uno Script Transformation Task, configurato (quando richiesto) come sorgente dati.
5) Data Flow - Configurazione Script Source:
Creo tre colonne nel buffer di output del file (chiamato SourceOutput), tipizzandole come stringhe lunghe due caratteri.
Passo le variabili utili alla gestione dinamica dei delimiter.
Progetto lo script per la gestione dello split custom:
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Imports System.IO
Public Class ScriptMain
Inherits UserComponent
Public Overrides Sub CreateNewOutputRows()
Dim sr As New StreamReader(Variables.FileName)
Dim curstring As String
Dim strs As New System.Collections.Hashtable
Dim i As Int32 = 1
Dim charRead As Int32
Do While Not sr.EndOfStream
'leggo il carattere
charRead = sr.Read()
If charRead <> Variables.ROWDELIMITER And charRead <> Variables.COLDELIMITER Then
curstring &= Convert.ToChar(charRead)
Else
' aggiungo la stringa nella hashtable
strs.Add(i, curstring)
curstring = ""
i += 1
' controllo il fine riga
If charRead = Variables.ROWDELIMITER Then
SourceOutputBuffer.AddRow()
SourceOutputBuffer.A = strs(1).ToString
SourceOutputBuffer.B = strs(2).ToString
SourceOutputBuffer.C = strs(3).ToString
strs.Clear()
i = 1
End If
End If
Loop
End Sub
End Class
6) Data Flow - Imposto la destinazione in base alle mie esigenze, ad esempio un OLEDB Destination.
la scelta sulla destinazione la lascio a voi
Il gioco è fatto.
In questo modo, abbiamo la possibilità di splittare il file come meglio crediamo. Il metodo, pur essendo customizzabile, ci lega comunque alla definizione delle colonne nel buffer di output dello script source, quindi ad ogni alterazione della struttura del flat file siamo costretti a ridefinire sia il buffer sia lo script. Del resto, il Flat File Connection Manager non ci consente di gestire i flussi in questo modo. Inoltre, il file sorgente, nella maggior parte dei casi, può cambiare all'inizio, ma poi la struttura rimane la medesima. Inoltre dobbiamo anche valutare i vantaggi che possiamo trarre da una soluzione di questo tipo. Una volta caricato il file, possiamo utilizzare tutti gli strumenti SSIS per gestire le trasformazioni anche più complesse. E' un modo per sostituire le sorgenti predefinite di SSIS.
Stay Tuned! 