Mi sono ritrovato a dover caricare un flat file con tracciato a posizioni fisse in SQL 2005. Il file ha come caratteristica il fatto di essere composto da 2 tipi di record differenti, cioé la prima e l'ultima riga (header e footer) hanno un tracciato che riporta dati di riepilogo e i record restanti, contenenti i record di dettaglio (quelli che mi interessano!) con un altro tracciato (più lungo e con più campi). Mi sono fiondato immediatamente sull'utilizzo dei parametri FIRSTROW e LASTROW propri della BULK INSERT: leggendo dal record di testa il numero di record di dettaglio presenti nel file avevo ottenuto il LASTROW, mentre impostando a 2 il FIRSTROW mi sentivo di aver ottenuto ciò che mi serviva.
Il problema è che, nonostante la BULK INSERT avesse funzionato correttamente, mi ritrovavo una riga in meno nella mia tabella... e per la precisione la prima riga di dettaglio! Dopo due giorni di brainstorming con un collega siamo giunti ad una conclusione plausibile. Per mappare i campi del file con quelli della tabella target avevo predisposto un Format File costruito in base ai campi della riga di dettaglio e il cui terminatore di record era il terminatore \n (il file ha origine UNIX e quindi non ha anche il \r); parrebbe che SQL utilizzi le regular expressions per determinare i campi e tale carattere di fine riga, ma lo fa prima di aver letto il FIRSTROW, quindi, la prima cosa che fa, è crearsi in memoria l'immagine della tabella target, ma... la prima riga (header) non è abbastanza lunga per contenere tutti i caratteri necessari a comporre la riga di dettaglio e come si comporta? Essendo il terminatore di riga un carattere come un altro, accoda alla riga header la prima riga di dettaglio per completare il tracciato e POI cerca il \n per determinare la fine della riga (e quindi dell'ultimo campo): il primo che trova è il terminatore della prima riga di dettaglio che, a questo punto è diventata una parte della riga header a tutti gli effetti. E' adesso che salta la prima riga (come indicato dal FIRSTROW!) e carica a partire dalla seconda... Purtroppo non sono riuscito a trovare nessuno che avesse avuto lo stesso problema, né della documentazione in merito...
Soluzione adottata? Prima di effettuare la BULK INSERT apro il file, elimino le righe header e footer e carico senza FIRSTROW o LASTROW... Poco pulito, ma rapido ed efficace...
BULK INSERT side effect: se lo conosci lo eviti