Export output of SQL Server script to Excel (SSIS)

• Download SQLScriptToExcel.zip - 539 KB

Introduction

The issue I wanted to solve is that a user had over 20 Excel files that were generated each month. They came to me for a solution to automate it. All the Excel files were generated using different queries that were stored as .sql files.
A fellow DBA tried to solve it using the Import/Export wizard and storing all SSIS packages separately. But my issue with that is that it takes a big effort to maintain them. Also, over time, the number of queries is likely to grow and you would end up with a lot of maintenance as each SSIS package must be maintained separately.

The solution I wanted should require a minimum of maintenance and it should be easy to add new reports or change existing ones. It should be able to take "any" SQL file and generate an Excel file for it. I decided to use SSIS because of the flexibility and customisability.  The SSIS package roughly performs the following steps:

• Get the contents of the SQL script file.
• Create a temporary table from the script code (for determining the structure of the output).
• Generate and execute a script for creating the Excel worksheet.
• Dynamically generate a temporary package for executing the SQL script with an Excel file as output.
• Execute and drop the dynamic package.

Prerequisites for SQL file

The query in the SQL file must meet the following conditions:

• The selected columns must be uniquely named.
• The names of the columns can only contain alphanumeric characters and spaces.
• It must return a single result set.

Background

The problem in SSIS is that it isn't possible to simply use an OLEDB source for the query file and an Excel destination. It must also have a Data Transform component that specifies all the columns from the source that are non-unicode (only for strings) and convert them to unicode. As we don't know the structure of the output from the SQL script file, a solution must be found for this.

I searched the internet if there was a solution to solve this, but could not find a good, working solution.
The solutions I found had some usable parts, but none of them were a complete solution for my problem.

I used this article as a guideline for dynamically generating an SSIS package:  API Sample - Data Conversion.

Explanation of the SSIS package

This is the control flow of the SSIS package:

And below you'll find the explanation of what happens in each component of the Data Flow.

1  Get contents of SQL script file

Gets the contents of the SQL script file (via the Connection Manager SQLScript) and puts the contents in the variable SQLCommand.
Generates a script to create a temporary table with the columns the SQL script will generate and puts the script in the variable TempTableCommand.

2 Generate Excel create script

Script that generates a CREATE TABLE script for Excel, based on the query in the SQLScript file. To extract the columns and column types of the query, an in-memory package is created with an OLEDB source. 

3 Remove Excel output file

Removes the Excel output file, that is specified in the ConMgr DestinationExcel. This step will be executed when the package starts executing, together with step 1.

4 Create Excel output file

Executes the script, generated in step 2. 
The result is that a new Excel file is created with column names of the correct type in the first worksheet.

5 Dynamically generate package

Generates a new package with a DataFlow Task. Within the DataFlow Task, an OLEDB source, a Data Conversion and an Excel destination component are added. The Data Conversion component performs the conversion of non-unicode to unicode columns.
The package is saved in the location specified in the package parameter PackagePath.

6 Execute dynamic package

Executes the package, generated in step 5. The result is that the Excel sheet is filled with data, if the SQL script generates output.

7 Drop dynamic package

Drops the package, generated in step 5.

Connection managers:

Explanation of the code

In this chapter I will explain the most important code. It is more an overview of the structure than a complete description of all methods used.

1  Get contents of SQL script file

The Main method contains the logic of the script. It performs the following steps:

• Get the content of the script file. Returns an ApplicationException if the script file is not found or if the script file is empty.
• Execute method:

GetTempTableName()

This method generates a temporary tablename with a GUID in it (method ). This will prevent that when you execute the package multiple times in parallel that these executions generate the same temporary table.

• The SQLCommand parameter is filled with the original SQL statement from the SQL script file.

This is the complete code of the Main method:

public void Main()
{
    byte[] emptyBytes = new byte[0];

    try
    {
        // Get content of SQL file that is specified in the SQLFile connection manager.
        string SqlFileName = Dts.Connections["SQLScript"].ConnectionString;
        string sqlCommand = System.IO.File.ReadAllText(SqlFileName);
        if (sqlCommand == null
            || sqlCommand == String.Empty)
        {
            Dts.Log("The SQL script file cannot be read or is empty.", 0, emptyBytes);
            throw new ApplicationException("The SQL script file cannot be read or is empty.");
        }

        // Determine temporary package name.
        string tempPackageName = GetTempPackageName();

        Dts.Variables["SQLCommand"].Value = sqlCommand;

        // Everything OK, exit the script with success.
        Dts.TaskResult = (int)ScriptResults.Success;
    }
    // In case of an exception, exit the script with failure.
    catch (System.Exception ex)
    {
        Dts.Log(String.Format("Script ended in error:\r\n{0}", ex.Message), 0, emptyBytes);
        Dts.TaskResult = (int)ScriptResults.Failure;
    }
}

3 Generate Excel create script

This is a C# script that generates an Excel CREATE TABLE script. An in-memory package with an OLEDB source (that refers to the SQL script file) is used to determine the columns and column types.

public void Main()
{
    byte[] emptyBytes = new byte[0];
    try
    {
        // Create package and add a DataFlow task to it.
        Package package = new Package();
        MainPipe pipeline = AddDataFlowTask(package);

        // Add connection manager.
        ConnectionManager oleDbConnection = AddConnectionManagerOleDb(package);

        // Add package variables.
        AddPackageVariables(package);

        // Add the OLEDB source.
        IDTSComponentMetaData100 oleDbSource = AddSourceOleDb(pipeline, oleDbConnection);

        // Generate Excel CREATE TABLE script.
        GenerateExcelCreateScript(oleDbSource);
        Dts.TaskResult = (int)ScriptResults.Success;
    }
    // In case of an exception, exit the script with failure.
    catch (System.Exception ex)
    {
        Dts.Log(String.Format("Generate Excel create script ended in error:\r\n{0}", ex.Message), 0, emptyBytes);
        Dts.TaskResult = (int)ScriptResults.Failure;
    }
}

7 Dynamically generate package

The Main method contains the logic of the script for generating the dynamic package.
It performs the following steps:

• Create a new package and add a DataFlow task to it:

Package package = new Package();
MainPipe pipeline = AddDataFlowTask(package);

• Add the connection managers for the OLEDB source and the Excel destination.

ConnectionManager excelConnection = AddConnectionManagerExcel(package);
ConnectionManager oleDbConnection = AddConnectionManagerOleDb(package);

• Add the SQLCommand variable as a package variable to the dynamic package.

AddPackageVariables(package);

• Adds the OLEDB source, Excel destination and the transform between these components.

IDTSComponentMetaData100 oleDbSource = AddSourceOleDb(pipeline, oleDbConnection);
                IDTSComponentMetaData100 excelDestination = AddDestinationExcel(pipeline, excelConnection);
                IDTSComponentMetaData100 transform = AddTransform(pipeline, oleDbSource);

• Configures the transform (adds conversion for non-unicode columns to unicode columns).
• Configures the Excel destination (takes the unicode columns from the transform and the rest of the columns from the OLEDB source).

ConfigureTransform(pipeline, oleDbSource, excelDestination, transform);
ConfigureExcelDestination(pipeline, oleDbSource, excelDestination, transform);

• Saves the package and sets the task execution result to "success".

SavePackage(package);
Dts.TaskResult = (int)ScriptResults.Success;

• In case of an error, a message is written to the log (via Connection Manager SQLScriptToExcel.log) and the task execution result is set to "failure".

This is the full code of the Main method:

public void Main()
{
    byte[] emptyBytes = new byte[0];
    try
    {
        // Create package and add a DataFlow task to it.
        Package package = new Package();
        MainPipe pipeline = AddDataFlowTask(package);

        // Add connection managers.
        ConnectionManager excelConnection = AddConnectionManagerExcel(package);
        ConnectionManager oleDbConnection = AddConnectionManagerOleDb(package);

        // Add package variables.
        AddPackageVariables(package);

        // Add source, destination and transformation.
        IDTSComponentMetaData100 oleDbSource = AddSourceOleDb(pipeline, oleDbConnection);
        IDTSComponentMetaData100 excelDestination = AddDestinationExcel(pipeline, excelConnection);
        IDTSComponentMetaData100 transform = AddTransform(pipeline, oleDbSource);

        // Configure the transform and the Excel destination.
        ConfigureTransform(pipeline, oleDbSource, excelDestination, transform);
        ConfigureExcelDestination(pipeline, oleDbSource, excelDestination, transform);

        // Save the package and exit the script with success.
        SavePackage(package);
        Dts.TaskResult = (int)ScriptResults.Success;
    }
    // In case of an exception, exit the script with failure.
    catch (System.Exception ex)
    {
        Dts.Log(String.Format("Script ended in error:\r\n{0}", ex.Message), 0, emptyBytes);
        Dts.TaskResult = (int)ScriptResults.Failure;
    }
}

Deployment

To deploy the package, open the SQLScriptToExcel project in Visual Studio. Right-click the project and start the deployment wizard. I recommend to deploy the package to: /SSISDB/SQLScriptToExcel/SQLScriptToExcel.

For running a SQL script from your SSIS database server, you need an environment. Instructions for creating this environment is available as a Word document in the SQLScriptToExcel.zip download file.

History

Future changes

None planned right now.