We are happy to announce that xlsgen can now be used in SQL Server Integration Services (SSIS) as a key component for loading data sources, extract/transforming data sources, or exporting data sources in different ways.

For this to happen, xlsgen must be made available to the SQL Server Integration Services at design time, in the SQL Server Business Intelligence Development Studio, and at run-time in SQL Agent jobs.

What is needed to make this happen is to make a copy of the xlsgen .NET assembly in a location known as the global assembly cache (a folder where the .NET runtime knows it can load assemblies).

This can be done by hand on a computer where the .NET SDK is installed (the version of the .NET SDK does not matter) and we will provide in next build an automated tool for doing this by double-clicking on a Setup file.

This works for all SQL Server Integration Services versions out there (2005, 2008, 2008 R2, 2012, 2014). In version 2005, a copy of the xlsgen .NET assembly must also be made in the following folder (c:\windows\Microsoft.NET\Framework\v2.0.xxxxx).

Once this is done, any Script Task (a concept that is much like VBA macros in spreadsheets) can reference the xlsgen object model, hence take advantage of the entire xlsgen API.

This becomes powerful as Script Tasks can use parameters such as Variables that can be configured in SSIS packages as well as SQL Agent jobs.