Professional SQL Server™ 2005 Integration Services

17.2. InfoPath Document

This example demonstrates the ability of SSIS to interact with an external Microsoft Office application, namely InfoPath 2003.

Microsoft InfoPath 2003 is a desktop forms client that provides a rich interface to XML-based documents. For more information about Microsoft InfoPath 2003, see Professional InfoPath 2003 by Ian Williams and Pierre Greborio (Wrox, 2004).

Using a document created from the Timecard template supplied with InfoPath 2003, you will import portions of data stored in an InfoPath document and output the results to a comma-delimited flat file.

Portions of imported data in this demonstration appear disconnected in the Data Flow task. This example demonstrates an SSIS method to join disconnected data. This example also covers some troubleshooting.

Many thanks to Wenyang Hu for permission to reuse some elegant XSL!

Create a new Integration Services project in SSIS. Drag an XML task onto the Control Flow. Double-click the XML task to open the editor. Configure the XML task as follows:

• Operation Type: XSLT.

• Source Type: File Connection.

• Source: New File Connection. Configure the New File Connection as follows:

  • Usage Type: Existing File.

  • File: Click Browse to locate and select an InfoPath timecard directory and file. The timecard file may be generated using the InfoPath 2003 Timecard template, or you can get the Timecard_ARay.xml file from the Resources.

• Save Operation Result: True.

• Overwrite Destination: True.

• Destination Type: File Connection.

• Destination: ...