Typically, making the output of an SSIS Package available to a client application for further processing is achieved by outputting the results to a Table in a Database or, maybe, to a file.

The application would then interrogate any of these sources and retrieve the data it needs.

However, what if you’d rather not persist the output to disk?

What if you wanted to read the output of the Package straight from memory?

In other words, what if you wanted the client application to be the SSIS Package output destination?

One way you can do this is with the SSIS DataReader Destination.

The DataReader Destination allows you to consume the output of an SSIS package from within a client application.

You would write the Package as you would any other, however, you would use the DataReader Destination as the final destination for the rows the SSIS Package outputs.

You can then reference and execute the Package directly from within your application and retrieve its output.

Let’s see how this might work.

I’ll be using Visual Studio 2013 with SQL Server 2014.

The Test SSIS Package

First, we’ll go ahead and create a simple SSIS package that sends a few rows from a flat file to a DataReader Destination.

It should consist of a Data Flow Task that contains a Flat File Source connected to a DataReader Destination.

There should also be a Flat File Connection in the Connection Managers Tab pointing to a Test file with a few rows of data.

Configure the DataReader Destination by double clicking it, opening up it’s Advanced Editor and clicking on the Input Columns Tab.

Check the boxes to include all Input Columns.

The Package looks something like this:

The Consuming Application

Next, we’ll need an application that can consume the output of the SSIS Package.

For this purpose, we’ll create a simple Console application within Visual Studio.

In order to capture the output from the SSIS Package within the Client Application, we need to reference an Assembly that is installed alongside SQL Server.

Right click on the References node on Solution Explorer, clicking “Add Reference”.

Browse to the %ProgramFiles%\Microsoft SQL Server\120\DTS\Binn folder and select the Assembly called “Microsoft.SqlServer.Dts.DtsClient”.

The following code should be entered in the Program.cs file (I have dotted the code with any relevant comments):

static void Main(string[] args)
            {
                string dtexecArgs;
                string dataReaderName;
                DtsConnection dtsConnection;
                DtsCommand dtsCommand;
    
                IDataReader dtsDataReader;
                Console.Title = "SSIS Package Output Consumer";
                try
                {
                    //  This amounts to the connection string or path to your package.
                    //  If it's in anyway familiar, its because it's the same string you might typically use to execute the package with the dtexec utility
                    //  You can even create the connection with the dtexecui and then just plug it in here
                    string package = @"/FILE ""C:\Users\chimkalunta\ImportCustomers\Package.dtsx"" /CHECKPOINTING OFF  /REPORTING EW ";
                    dtexecArgs = package;
                    // The rows that are output from the SSIS package is provided to the client application via a datareader object.
                    // You will note that the name of the datareader is the same as the name of the Data Reader Destination in the package.
                    // This is required.
                    dataReaderName = "DataReaderDest";
                    dtsConnection = new DtsConnection();
                    dtsConnection.ConnectionString = dtexecArgs;
                    dtsConnection.Open();
                    dtsCommand = new DtsCommand(dtsConnection);
                    dtsCommand.CommandText = dataReaderName;
                    // The package is executed at this point and you have your output in the DataReader Object without having to persist it to disk first
                    dtsDataReader = dtsCommand.ExecuteReader(CommandBehavior.Default);
                    
                    Console.ForegroundColor = ConsoleColor.Green;
                    Console.WriteLine("");
                    Console.WriteLine("/***** SSIS Package Output *****/");
                    Console.WriteLine("");
                    Console.ForegroundColor = ConsoleColor.White;
                    // You loop through the rows in the data reader object and carry out any further processing, in this case, simply sending it to the Console
                    while (dtsDataReader.Read())
                    {
                        Console.WriteLine(dtsDataReader.GetString(0) + " " + dtsDataReader.GetString(1));
                    }
                    // Clean up
                    dtsCommand.Dispose();
                    dtsDataReader.Close();
                    dtsConnection.Close();
                    Console.ReadLine();
                }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message + "\n" + ex.InnerException.Message);
            }
        }

The Test

I fire up the Console Application which in turn executes the SSIS Package, writing the output to the Console screen.