ASP.NET Application

The first example in this chapter demonstrated the DataReader Destination in SSIS, which adds flexibility to SSIS by exposing package output. In the first example, the output was consumed by a Reporting Services Report. This example demonstrates the capability of SSIS to interact with custom external applications by interfacing with; a simple ASP.NET application.

Note

This example application is written in VB.NET 2005 and displays the output from an SSIS package in an ASP.NET GridView control. Thanks to Ashvini Sharma and Ranjeeta Nanda for technical support!

In Visual Studio 2005, create a new Integration Services project. Drag a Data Flow task onto the Control Flow and double-click it to open the Data Flow tab. Drag an OLE DB Source onto the Data Flow and double-click to edit. Configure the OLE DB Source as follows (shown in Figures 17-19 and 17-20):

• OLE DB Connection Manager: Click New to open the Configure OLE DB Connection Manager dialog box, and then click New to open the Connection Manager dialog box. Configure the connection as follows:

  • Server Name: [Your server name]

  • Log on to the server: Use Windows Authentication

  • Select or enter a database name: AdventureWorks

  Click the Test Connection button to confirm connectivity, as shown in Figure 17-19, and then click OK to proceed.

• Data Access Mode: SQL Command

• SQL Command Text:

   SELECT Title, FileName FROM Production.[Document] 

Figure 17-19

Figure 17-20

Select the fields you wish to return by clicking Columns in the listbox and checking the Title and Filename checkboxes. Click OK to close the editor.

Drag a DataReader Destination onto the Data Flow. Connect the output of the OLE DB Source to the DataReader Destination and double-click the DataReader Destination to begin editing. Click the Input Columns tab on the Advanced Editor for the DataReaderDest dialog. Select the Title and Filename fields (selected earlier in the OLE DB Source) for the DataReader and click OK to close the editor. Test the SSIS functionality before proceeding.

Add a new Web site to the solution by clicking File Add New Web Site. Name the Web project ASP_Feed_Web. Set the Location to File System and select Visual Basic as the Language as shown in Figure 17-21.

Figure 17-21

In Solution Explorer, right-click the Web site project and click Add Reference. If Microsoft.SqlServer .Dts.DtsClient appears in the list of References on the .Net tab, double-click it to add a reference to the project. If not, click the Browse tab and navigate to %Program Files%\Microsoft SQL Server\90\DTS\Binn\Microsoft.SqlServer.Dts.DtsClient.dll and click OK.

Note

The DTSClient DLL contains interfaces to SSIS connection and command objects. See Books Online and MSDN for more information about this library.

Right-click the Default.aspx object and click View Designer. Drag a GridView control onto the Web page. Double-click the page to open the code viewer. Add the following code at the top of the page:

 Imports Microsoft.SqlServer.Dts.DtsClient Imports System.Data.SqlClient 

In the Page_Load subroutine, add the following line of code, replacing [your package directory] with the actual name of the directory containing your SSIS package:

 connectToSSISPackage("[your package directory]\Package.dtsx") 

Add the following function to the _Default partial class:

 Private Function connectToSSISPackage(ByVal path As String) As Integer     ' create new SSIS connection...     Dim ssisCN As DtsConnection = New DtsConnection     '  set new SSIS connection's connectionstring property to path     '    passed into function as an argument...     ssisCN.ConnectionString = String.Format("-f ""{0}""", path)     '  open the new SSIS connection...     ssisCN.Open()     '  create new SSIS command and assign it's connection...     Dim ssisCmd As DtsCommand = New DtsCommand(ssisCN)     '  assign new SSIS command's cpmmandtext property...     ssisCmd.CommandText = "DataReaderDest"     '  create a datareader to receive the SSIS command's output...     Dim ssisReader As Data.IDataReader = _         ssisCmd.ExecuteReader(Data.CommandBehavior.Default)     '  create and populate a new dataset from the datareader...     Dim ssisDs As Data.DataSet = New Data.DataSet _     ssisDs.Load(ssisReader, Data.LoadOption.OverwriteChanges, _         ssisReader.GetSchemaTable().TableName)     '  populate the gridview from the dataset...     GridView1.DataSource = ssisDs     '  bind the gridview (refresh data bindings)...     GridView1.DataBind()     '  close the SSIS connection...     ssisCN.Close() End Function 

The connectToSSISPackage function receives a path to an SSIS package through the "path" argument. A new SSIS connection (of DTSConnection type) called ssisCN is created.

Note

If your environment does not recognize the DTSConnection data type, make sure you have a reference properly defined and have included the Imports Microsoft.SqlServer.Dts.DtsClient statement at the beginning of your code.

The path argument is the connection string for the SIS connection. After the SSIS connection is opened, a new SSIS command (of DTSCommand type) is created and assigned to the SSIS connection. The CommandText property of the SSIS command object is set to the name of the DataReader Destination in the SSIS package.

Next, a DataReader object is defined and populated with the results of the SSIS command's execution. A data set is created and filled with the datareader's data. The gridview's datasource property is assigned to the data set and the gridview is refreshed with a call to DataBind. Finally, the SSIS connection is closed.

Click the Play button to test. A list of document names and file paths should populate the grid as shown in Figure 17-22.

Figure 17-22

This example demonstrates a simple yet powerful feature of SSIS — the ability to expose output directly to ASP.NET applications. The DataReader Destination provides a flexible interface for SSIS package output.