Connect to an SSIS package and consume its data from .Net application

In this article, I will show how to connect to a Data reader destination component within a given SQL Server Integration Services package. The client application in this context is a simple console application that I have built using C# 3.5.

Requirements

First of all, there are conditions and requirements to be fulfilled in order to proceed and follow the subsequent walkthrough steps.

1. First, you have to have SQL server 2005/2008 at least the standard edition as it supports BI tools, namely SQL server integration services, SQL server analysis services and SQL server reporting services. But for our case we will be interested only on SQL server integration services.
   
   
2. You have to install those BI tools if they aren't already installed. To install them you have to lunch the SQL Installation Center
   
   
   
   
3. And then switch to the installation tab, then lunch the installation wizard through the first link
   
   
   
   
4. Of Corse, you have to have the SQL Server installation CD in the drive. Then follow the installation steps until you rich the bellow level
   
   
   
   
5. Of Corse, if you want to install a new named instance select the first alternative, and if you want to enhance an already existed instance then select the second choice
   
   Remarque: The installation step details are beyond the scope of this article, to know how to install SQL Server in detail please refer to this guide for 2005 version:
   
   http://www.impromed.com/documents/SQL_Server_2005_09252006.pdf
   
   And for 2008 version :
   
   http://blog.sqlauthority.com/2008/06/12/sql-server-2008-step-by-step-installation-guide-with-images/
   
   
6. Now, we are concerned by the bellow installation step, the Integration services must be checked
   
   
   
   
7. After the installation verify that the BI IDE which is Business Intelligence Development Studio BIDS is installed
   
   
   

The Data Reader destination case

The data reader destination component is a particular one among the rest of the other SSIS components as it is designed not to load data within a given data base or within a given file but it loads data in memory in order to be accessed by client applications like the .Net assemblies.

1. To starts let's first create a SSIS project
   
   
   
   
2. The create a new package by right clicking the packages folder within the solution explorer and then select create new package, then rename it by right clicking it and giving it a new name
   
   
3. Drag a data flow component within the scene
   
   
   
   
4. Double click that data flow in the scene then drag a OLEDB Source and a Data Reader destination
   
   
5. First begin by configuring the OLE DB source by double clicking it, then set the connection manager by configuring its provider, data base and data source which could be a table, a view or a command. The last one is better for performance issues. In my case, I connect to the Adventure works data base and retrieve full name which is a derived column and the city one and the state one. To choose an SQL command, you can select it from the SQL command text drop down list; you will remark that the page look will change. Then you can use the Build Query option to compose you query if you're not strong enough in SQL ; )
   
   
   
   
6. Always with the OLE DB source configuration wizard, ensure that all the desired columns within the view are selected by switching to the columns tab
   
   
   
   
7. Now, we step through to the data reader destination configuration, to do that, double click on that component, and first, let's link it with the OLE DB source component. 
   
   
   
   Second, let's verify that all columns are correctly mapped by switching to the Input Columns tab.
   
   
   
   
   
8. We can verify the correctness of the package content by debugging it by hitting F5, all the packages must turn to green color
   
   
   
   
9. Now, let's create a client application that consumes data from that package
   
   For this case we can create a simple console application to consume that data flow. To do that, add a console application project to the solution.
   
   
10. Add a reference to the Microsoft.SqlServer.Dts.DtsClient.dll which is a managed assembly located in %ProgramFiles%\Microsoft SQL Server\100\DTS\Binn
    
    
11. Then add this code to the class Program of the client, Of Corse, in my case I'm supposing to retrieve data from MyPackage.dtsx which is located in
    
    C:\Users\Administrator\Documents\VisualStudio 2008\Projects\SSISProject001\SSISProject001\ folder. The name of the data reader destination component is DataReaderDest that could be found within the properties grid of that component, in deed, you can change that name to suit your needs. In my case, I suppose to retrieve the full name, the city and the state fields from that data reader destination.
    
    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Data;
    using Microsoft.SqlServer.Dts.DtsClient;
    using System.Windows.Forms;
    namespace DataClient
    {
        class Program
        {
            static DtsConnection connection;
            static DtsCommand command;
            static IDataReader reader;
            static void Main(string[] args)
            {
                using (connection = new DtsConnection())
                {
                    connection.ConnectionString = @"/File "" C:\Users\Administrator\Documents\Visual Studio 2008\Projects\SSISProject001\SSISProject001\MyPackage.dtsx""";
                    try
                    {
                        connection.Open();
                        command = new DtsCommand(connection);
                        command.CommandText = "DataReaderDest";
                        reader = command.ExecuteReader(CommandBehavior.Default);
                        while (reader.Read())
                        {
                            Console.WriteLine(string.Format("Full name: " +
                                reader[0]+
                                " City" + reader[1] ));
                        }
                    }
                    catch (ApplicationException caught)
                    {
                        Console.WriteLine(caught.Message + " ,something is wrong");
                    }
                    finally
                    {
                        Console.Read();
                    }
                }
            }
        }
    }
    
    Very important issue: SSIS packages could be stored in the file system like our case, and then connection string is in that case as follow:
    
    @"/File "" <The path>\<The package name>.dtsx"""
    
    They could also be stored within the SQL server instance and exactly within the msdb system data base. In this case, the connection should be modified to be:
    
    @"/SQL ""<The package name>"""
    
    Then, if the package is stored within SSIS storage, the SQL keyword must be replaced by DTS one.
    
    You can play around by importing the given package within SQL Server Management Studio and exactly the SQL Server Integration Services instance if you have right permissions to do that and then try those connection strings to retrieve data from the package.
    
    
12. Finally, to lunch and test the client let start by setting the integration service project as a startup project and then lunch it by hitting F5, then you should wait until every component become green. Afterward, Start a new instance of the client
    
    
    

And the result will be as expected

It retrieves in memory data successfully.