Exporteren SSIS Data naar Excel 2007

Hieronder volgt een korte beschrijving hoe je vanuit SSIS een koppeling kunt maken met Excel 2007.

Integration Services ondersteunt standaard Excel-files maar geen Excelfiles die gemaakt zijn in Office 2007. Deze bestanden hebben namelijk een andere extensie (.xlsx). Om vanuit SSIS een connectie te kunnen maken dien je eerst een OLEDB-driver te installeren. Deze kun je hier downloaden.

Na de installatie kun je een OLEDB-Source aanmaken met als Provider de Microsoft Office 12 Access Database Engine OLE DB Provider. Vul bij de servernaam de locatie van de excelsheet in. Vul in het tabblad ALL bij het veld ‘Extented Properties’ de waarde ‘Excel 12.0 Xml’ in. De XML is belangrijk. Voer je enkel Excel 12.0 in dan zal de output file een binary file worden, iets wat je liever niet wilt. Zie hier voor meer informatie.

Maak in de Excelsheet alvast kolommen aan, anders krijg je problemen met je mappings in je destination! Cast al je velden in je OLEDB-Source naar NVARCHAR, Excel 2007 houdt van Unicode-strings . Doe je dit niet krijg je dit soort foutmeldingen: cannot convert between unicode and non-unicode string data types.

 

Het volgende probleem is het legen van de Excelfile. Je wilt iedere dag een versie datadump hebben. Als je de Excelfile niet leegt zal SSIS iedere keer dat de package draait de data toevoegen. Je krijgt dan dubbele data.

Door gebruik te maken van het OPENROWSET commando in SQL Server kun je data uit een Excelfile raadplegen. Wanneer je dit de eerste keer uitvoert zul je onderstaande foutmelding krijgen:
“SQL Server blocked access to STATEMENT ‘OpenRowset/OpenDatasource’ of component ‘Ad Hoc Distributed Queries’ because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of ‘Ad Hoc Distributed Queries’ by using sp_configure..”

Dit los je op door onderstaande statement uit te voeren (zie hier voor meer informatie):

{geshi xml:lang=”tsql” lines=”false”}
sp_configure ‘show advanced options’, 1;
GO
RECONFIGURE;
GO
sp_configure ‘Ad Hoc Distributed Queries’, 1;
GO
RECONFIGURE;
GO{/geshi}

Helaas kun je met T-SQL geen data uit een Excelsheet verwijderen, je zult dan deze foutmelding krijgen:
‘Deleting data in a linked table is not supported by this ISAM’.

Opvragen kan wel met onderstaand statement:
{geshi xml:lang=”tsql” lines=”false”}
SELECT *
FROM OPENROWSET(‘Microsoft.ACE.OLEDB.12.0′,’Excel 12.0 Xml;Database=C:Debiteuren.xlsx’, ‘SELECT * FROM [Sheet1$B1:F3]’);{/geshi}

Let op dat je leesrechten hebt op de file die wilt raadplegen, anders krijg je onderstaande foutmelding:
Error Message: The OLE DB provider “Microsoft.ACE.OLEDB.12.0” for linked server “(null)” reported an error. Access denied.

Het niet kunnen legen van het Excelbestand  is een issue. Ik krijg het niet voor elkaar om de Excelfile te legen en te vullen. Een oplossing moet dus gezocht worden in het opnieuw aanmaken van het bestand.

Oplossing
De oplossing is redelijk eenvoudig. Hieronder de stappen die doorlopen dienen te worden om een Excelfile succesvol te kunnen vullen.

Stap 1) Maak een sjabloon aan zoals je het Excelbestand wilt vullen. Maak enkel de headerrij aan dus de kopjes. Zet geen data in dit bestand. Plaats dit sjabloon in een folder, bijvoorbeeld c:reportssjablonen . Maak ook een folder aan waar je het bestand in gaat vullen, bijvoorbeeld: c:reportsgevuld

Stap 2) Maak een SSIS Package met 2 File System Task. In Task 1 verwijder je het bestand in gevuld . In Task 2 kopieeer je het sjabloon vanuit c:reportssjablonen -> c:reportsgevuld

Stap 3) Maak een Dataflow-Task aan die de data overpompt.

Je hebt 3 connection managers nodig voor deze solution. Eentje die naar het sjabloon verwijst, eentje die naar het gevulde bestand verwijst en eentje voor het vullen van het Excel 2007 bestand (OLEDB Connection).