SSIS Excel Connection Manager datatyper

At læse data ind fra et Excel-ark vha. SSIS er et mareridt, hvis man bruger de almindelige komponenter der følger med SSIS (Excel Connection Manager samt Excel Source). Årsagen er, at Excel Connection Manager’en anvender Microsoft JET OLE DB 4.0 provideren – en 16 år gammel teknologi (JET 4.0 udkom i 1997!). Selv i den seneste udgave af SSIS fra 2012, er den forældede provider den eneste måde at forbinde til et Excel-ark i SSIS, hvis ikke man ønsker at anvende 3. parts software eller skrive en Script Component fra bunden. Det er ubegribeligt hvorfor Microsoft ikke for længst har tilbudt SSIS-udvikler en bedre måde at forbinde til Excel-filer fra SSIS, særligt eftersom Excel-filer fra 2007 og fremefter (xlsx) anvender OpenOfficeXML-formatet.

Begrænsningerne ved JET OLE DB 4.0-provideren er opsummeret i dette blog-indlæg.

Det mest problematiske er den automatiske (“intelligente”) bestemmelse af datatyperne af de enkelte kolonner i Excel-filen, baseret på de første 8 rækker data. Som udvikler i SSIS har man ingen mulighed for at specificere hvilken datatype en kolonne indeholder. Særligt problematisk er en kolonne hvor der blandt de første 8 rækker er et flertal af numeriske data, mens der i efterfølgende rækker dukker tekstdata op. Formatering og foranstillede apostroffer i Excel-filen hjælper ikke ret meget. Kolonnen vil blive opfattet som numerisk (DT_R8) i SSIS, og alle ikke-numeriske værdier vil blive castet til NULL. Øv!

Et lille trick som kan hjælpe i de situationer hvor man har mulighed for at redigere indholdet af Excel-filen, er at tilføje ;IMEX=1 til Connection Manageren’s forbindelsesstreng:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\data.xls;Extended Properties="EXCEL 8.0;HDR=YES;IMEX=1

Denne lille feature sikrer, at datatypen tilpasses indholdet af de første 8 rækker, ikke kun flertallet blandt de første 8 rækker. Så ved at indsætte endnu en række øverst i Excel-arket med dummy tekst-data, kan man gennemtvinge at kolonner ikke castes til numeriske datatyper, men beholder tekst-datatypen (DT_WSTR)[255].

Daniel Otykier
Member since November 20, 2016

Comments

    Your email address will not be published. Required fields are marked *