Basics of XML and SQL Server, Part 5: Generate/Transmit XML with SSIS By Stan Kulp

Basics of XML and SQL Server, Part 5: Generate/Transmit XML with SSIS

By Stan Kulp

http://www.sqlservercentral.com/articles/Integration+Services+%28SSIS%29/77306/

The attached EmailFtpXmlInvoice.dtsx demo SSIS package queries the Clients table for client ids, then loops through the client ids to:

1. Generate invoice XML code for each client using a set of FOR XML queries.
2. Save the invoice XML code to an XML file.
3. Email the XML invoice file to the client if there is an email address for the client in the lookup table.
4. FTP the XML invoice file to the client's server if there is FTP server information for the client in the lookup table.

If you don't have access to SMTP and FTP servers, the 'Install and Configure Local FTP and SMTP Servers' section of this article explains how to download, install and configure free SMTP and FTP servers for testing purposes.