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.

Disclaimer

This SSIS package is a prototype, intended for proof-of-concept purposes only.

Create the Demo Data

Execute the following SQL code in SQL Server Management Studio...

USE master
GO
BEGIN TRY
DROP TABLE [InvoiceDemo].[dbo].[Vendor]
END TRY
BEGIN CATCH
END CATCH
GO
BEGIN TRY
DROP TABLE [InvoiceDemo].[dbo].[Clients]
END TRY
BEGIN CATCH
END CATCH
GO
BEGIN TRY
DROP TABLE [InvoiceDemo].[dbo].[Sales]
END TRY
BEGIN CATCH
END CATCH
GO
BEGIN TRY
DROP TABLE [InvoiceDemo].[dbo].[EmailFtpInfo]
END TRY
BEGIN CATCH
END CATCH
GO
BEGIN TRY
DROP DATABASE InvoiceDemo
END TRY
BEGIN CATCH
END CATCH
GO
CREATE DATABASE InvoiceDemo
GO
USE InvoiceDemo
GO
CREATE TABLE [InvoiceDemo].[dbo].[Vendor]
(
    [VendorName] VARCHAR(50),
    [Address] VARCHAR(50),
    [City] VARCHAR(50),
    [State] VARCHAR(2),
    [ZipCode] VARCHAR(10),
    [Phone] VARCHAR(12)
)
GO
INSERT INTO [InvoiceDemo].[dbo].[Vendor]
([VendorName],[Address],[City],[State],[ZipCode],[Phone])
VALUES
('Gasoline and Diesel Suppliers, LLC','1234 Petroleum Blvd','Oil Town','XX','9999-9999','999-999-9999')
GO
CREATE TABLE [InvoiceDemo].[dbo].[Clients]
(
    [ClientID] VARCHAR(50),
    [ClientName] VARCHAR(50),
    [Address] VARCHAR(50),
    [City] VARCHAR(50),
    [State] VARCHAR(50),
    [ZipCode] VARCHAR(50),
    [Phone] VARCHAR(50)
)
GO
INSERT INTO [InvoiceDemo].[dbo].[Clients] ([ClientID],[ClientName],[Address],[City],[State],[ZipCode],[Phone]) VALUES ('1','Jiffee Stop Gas','1234 Monroe Street','Washington','XX','9999-9999','999-999-9999')
INSERT INTO [InvoiceDemo].[dbo].[Clients] ([ClientID],[ClientName],[Address],[City],[State],[ZipCode],[Phone]) VALUES ('2','Fast Gas and Sundries','5678 Jefferson Boulevard','Adams','XX','9999-9999','999-999-9999')
INSERT INTO [InvoiceDemo].[dbo].[Clients] ([ClientID],[ClientName],[Address],[City],[State],[ZipCode],[Phone]) VALUES ('3','I-90 Truck Stop','9876 Lincoln Avenue','Jackson','XX','9999-9999','999-999-9999')
INSERT INTO [InvoiceDemo].[dbo].[Clients] ([ClientID],[ClientName],[Address],[City],[State],[ZipCode],[Phone]) VALUES ('4','Gas and Go','1029 Truman Road','Hamilton','XX','9999-9999','999-999-9999')
INSERT INTO [InvoiceDemo].[dbo].[Clients] ([ClientID],[ClientName],[Address],[City],[State],[ZipCode],[Phone]) VALUES ('5','Racetrack Gas','3847 US Grant Freeway','Franklin','XX','9999-9999','999-999-9999')
GO
CREATE TABLE [InvoiceDemo].[dbo].[Sales]
(
    [ClientID] VARCHAR(50),
    [InvoiceNumber] VARCHAR(50),
    [InvoiceDate] DATETIME,
    [ItemID] VARCHAR(50),
    [ItemDescription] VARCHAR(50),
    [Gallons] VARCHAR(50),
    [PricePerGallon] VARCHAR(50),
    [Amount] AS (-1) * CAST(CAST([Gallons] AS FLOAT) * CAST([PricePerGallon] AS FLOAT) AS MONEY)
)
GO
INSERT INTO [InvoiceDemo].[dbo].[Sales] ([ClientID],[InvoiceNumber],[InvoiceDate],[ItemID],[ItemDescription],[Gallons],[PricePerGallon]) VALUES ('1','1001','01/01/2012','1','Regular Unleaded','5000','3.45')
INSERT INTO [InvoiceDemo].[dbo].[Sales] ([ClientID],[InvoiceNumber],[InvoiceDate],[ItemID],[ItemDescription],[Gallons],[PricePerGallon]) VALUES ('1','1001','01/01/2012','2','Premium Unleaded','3000','3.95')
INSERT INTO [InvoiceDemo].[dbo].[Sales] ([ClientID],[InvoiceNumber],[InvoiceDate],[ItemID],[ItemDescription],[Gallons],[PricePerGallon]) VALUES ('1','1001','01/01/2012','3','No. 2 Diesel','2000','4.50')
INSERT INTO [InvoiceDemo].[dbo].[Sales] ([ClientID],[InvoiceNumber],[InvoiceDate],[ItemID],[ItemDescription],[Gallons],[PricePerGallon]) VALUES ('2','1002','01/12/2012','1','Regular Unleaded','2000','3.45')
INSERT INTO [InvoiceDemo].[dbo].[Sales] ([ClientID],[InvoiceNumber],[InvoiceDate],[ItemID],[ItemDescription],[Gallons],[PricePerGallon]) VALUES ('2','1002','01/12/2012','2','Premium Unleaded','1000','3.95')
INSERT INTO [InvoiceDemo].[dbo].[Sales] ([ClientID],[InvoiceNumber],[InvoiceDate],[ItemID],[ItemDescription],[Gallons],[PricePerGallon]) VALUES ('2','1002','01/12/2012','3','No. 2 Diesel','500','4.50')
INSERT INTO [InvoiceDemo].[dbo].[Sales] ([ClientID],[InvoiceNumber],[InvoiceDate],[ItemID],[ItemDescription],[Gallons],[PricePerGallon]) VALUES ('3','1003','01/09/2012','1','Regular Unleaded','10000','3.45')
INSERT INTO [InvoiceDemo].[dbo].[Sales] ([ClientID],[InvoiceNumber],[InvoiceDate],[ItemID],[ItemDescription],[Gallons],[PricePerGallon]) VALUES ('3','1003','01/09/2012','2','Premium Unleaded','5000','3.95')
INSERT INTO [InvoiceDemo].[dbo].[Sales] ([ClientID],[InvoiceNumber],[InvoiceDate],[ItemID],[ItemDescription],[Gallons],[PricePerGallon]) VALUES ('3','1003','01/09/2012','3','No. 2 Diesel','2500','4.50')
INSERT INTO [InvoiceDemo].[dbo].[Sales] ([ClientID],[InvoiceNumber],[InvoiceDate],[ItemID],[ItemDescription],[Gallons],[PricePerGallon]) VALUES ('4','1004','01/22/2012','1','Regular Unleaded','6000','3.45')
INSERT INTO [InvoiceDemo].[dbo].[Sales] ([ClientID],[InvoiceNumber],[InvoiceDate],[ItemID],[ItemDescription],[Gallons],[PricePerGallon]) VALUES ('4','1004','01/22/2012','2','Premium Unleaded','6000','3.95')
INSERT INTO [InvoiceDemo].[dbo].[Sales] ([ClientID],[InvoiceNumber],[InvoiceDate],[ItemID],[ItemDescription],[Gallons],[PricePerGallon]) VALUES ('4','1004','01/22/2012','3','No. 2 Diesel','3000','4.50')
INSERT INTO [InvoiceDemo].[dbo].[Sales] ([ClientID],[InvoiceNumber],[InvoiceDate],[ItemID],[ItemDescription],[Gallons],[PricePerGallon]) VALUES ('5','1005','01/17/2012','1','Regular Unleaded','15000','3.45')
INSERT INTO [InvoiceDemo].[dbo].[Sales] ([ClientID],[InvoiceNumber],[InvoiceDate],[ItemID],[ItemDescription],[Gallons],[PricePerGallon]) VALUES ('5','1005','01/17/2012','2','Premium Unleaded','7000','3.95')
INSERT INTO [InvoiceDemo].[dbo].[Sales] ([ClientID],[InvoiceNumber],[InvoiceDate],[ItemID],[ItemDescription],[Gallons],[PricePerGallon]) VALUES ('5','1005','01/17/2012','3','No. 2 Diesel','5500','4.50')
GO
CREATE TABLE [InvoiceDemo].[dbo].[EmailFtpInfo]
(
    [ClientID] VARCHAR(50),
    [EmailAddress] VARCHAR(50),
    [FtpDomain] VARCHAR(50),
    [FtpUsername] VARCHAR(50),
    [FtpPassword] VARCHAR(50),
    [FtpSubdirectory] VARCHAR(50)
)
GO
INSERT INTO [InvoiceDemo].[dbo].[EmailFtpInfo] ([ClientID],[EmailAddress],[FtpDomain],[FtpUsername],[FtpPassword],[FtpSubdirectory]) VALUES ('1','address@domain.com',NULL,NULL,NULL,NULL)
INSERT INTO [InvoiceDemo].[dbo].[EmailFtpInfo] ([ClientID],[EmailAddress],[FtpDomain],[FtpUsername],[FtpPassword],[FtpSubdirectory]) VALUES ('2',NULL,'127.0.0.1','anonymous',NULL,'/')
INSERT INTO [InvoiceDemo].[dbo].[EmailFtpInfo] ([ClientID],[EmailAddress],[FtpDomain],[FtpUsername],[FtpPassword],[FtpSubdirectory]) VALUES ('3','address@domain.com','127.0.0.1','anonymous',NULL,'/accounting/')
INSERT INTO [InvoiceDemo].[dbo].[EmailFtpInfo] ([ClientID],[EmailAddress],[FtpDomain],[FtpUsername],[FtpPassword],[FtpSubdirectory]) VALUES ('4','address@domain.com',NULL,NULL,NULL,NULL)
INSERT INTO [InvoiceDemo].[dbo].[EmailFtpInfo] ([ClientID],[EmailAddress],[FtpDomain],[FtpUsername],[FtpPassword],[FtpSubdirectory]) VALUES ('5',NULL,'127.0.0.1','anonymous',NULL,'/')
GO
SELECT * FROM [InvoiceDemo].[dbo].[Vendor]
GO
SELECT * FROM [InvoiceDemo].[dbo].[Clients]
GO
SELECT * FROM [InvoiceDemo].[dbo].[Sales]
GO
SELECT * FROM [InvoiceDemo].[dbo].[EmailFtpInfo]
GO

...to create and populate the Vendor, Clients, Sales and EmailFtpInfo tables of the InvoiceDemo database.

Load the Demo Package

Load the attached EmailFtpXmlInvoice.dtsx SSIS package into an SSIS project in Visual Studio.

Configure the Demo Environment

Create a directory named C:\xml5\ where the Write XML invoice file script task creates the XML invoice files.

Create a directory named C:\ftp\ and...

...a directory named C:\ftp\accounting\ where some of the XML documents will be transferred.

Configure the InvoiceDemoOleDb connection manager to point to the server where the demo data was created by double-clicking the InvoiceDemoOleDb connection manager icon and entering the name of the server.

Configure the InvoiceDemoAdoNet connection manager to point to the server where the demo data was created by double-clicking the InvoiceDemoAdoNet connection manager icon and entering the name of the server.

Change the EmailAddress fields of the EmailFtpInfo table from address@domain.com to your own email address so that the emails will be sent to you when you test the SSIS package.

If you are not going to use a local FTP server, change the FTP domain, username, password and subdirectory information to that of the external server to which you wish to transfer the test files.

Install and Configure Local FTP and SMTP Servers

Download the FileZilla FTP server from http://filezilla-project.org/download.php?type=server, install it, and start the FileZilla Server Interface.

Accept the default server address (127.0.0.1) and port (14147) and click the OK button.

Select the Edit-Users menu selection from the FileZilla Server Interface.

Click the Add button below the Users window of the General page of the User configuraiton editor, enter the user account 'anonymous' into the top text box, then click the OK button.

Confirm that the user 'anonymous' has been added, then click the Shared folders page node.

Click the Add button under the Shared folders window, browse to the C:\ftp\ folder, then click the OK button.

Confirm that the C:\ftp\ shared folder has been added, then click the OK button to finish configuration of the FTP server.

Leave the FileZilla Server Interface up.

Download Free SMTP Server 2.5 from http://www.softpedia.com/get/Internet/Servers/E-mail-Servers/Free-SMTP-Server.shtml, install it, then start it up.

No further configuration of the SMTP server is required because it defaults to localhost.

The free version of Free SMTP Server only allows you to send ten emails per day, but that is enough to confirm that the SSIS package is working properly.

Execute the SSIS Package

Click on the 'Start Debugging" button  ...

...to execute the SSIS package.

Activity in the FileZilla Server Interface shows that it has been busy.

Check the C:\ftp\...

...and C:\ftp\accounting\ folders to confirm that the file transfers were successful.

Check your email to confirm that the emails and their attachments arrived successfully.

Anatomy of the SSIS Package

The EmailFtpXmlInvoice.dtsx SSIS package is based on the CreateXmlInvoiceFile.dtsx from the previous article in this series 'Basics of XML and SQL Server, Part 4: Create an XML invoice with SSIS .' The 'Create XML invoice content' and 'Write XML invoice file' script tasks are identical.

The previous demo package also contained the client_id, output_path_template and xml_doc package variables, as well as the InvoiceDemoOleDb OLE DB connection manager.

The EmailFtpXmlInvoice.dtsx package contains:

1. Seven global package variables: client_id, client_ids, from_email_address, output_path, output_path_template, smtp_ip and xml_doc
2. A Script Task component (Get Client IDs) that queries the Clients table for all the client ids and stores them in a list array
3. A ForEach Loop container (For Each Client ID) that reads client ids from the list array and loops through the four task components contained within it
4. An Execute SQL Task component (Create XML invoice content) that joins the Vendor, Clients and Sales tables in a FOR XML query to generate invoice XML code
5. A Script Task component (Write XML invoice file) that writes the XML code to an XML file
6. A Script Task component (Email XML invoice file) that checks the EmaiFtpInfo table to determine if the table contains an email address for the current client id. If it does, it emails the invoice to that email address.
7. A Script Task component (FTP XML invoice file) that checks the EmaiFtpInfo table to determine if the table contains an FTP domain for the current client id. If it does, it FTPs the invoice to that domain.
8. An OLE DB connection manager for the SQL query in the Execute SQL Task
9. An ADO.NET connection manager for SQL queries in the Script Tasks.

The package variables hold the following:

1. The client_id variable holds the current client_id being processed.
2. The client_ids variable holds the list of client ids to be processed.
3. The from_email_address variable contains the email address you wish to appear in the emails as the sender. Enter the desired sender email address in the Value field of this variable.
4. The output_path variable contains the path to the XML file for the client_id currently being processed.
5. The output_path_template variable contains the masked file path used to generate a valid file path for the XML files for each client. Enter the desired output_path_template in the Value field of this variable. Use an asterisk to indicate where the replacement text generated in the Write XML invoice content script task should appear. In this demo, the replacement text is the client id.
6. The smtp_ip variable holds the IP address of the SMTP server you wish to use to send email. If you use the local SMTP server as described in the Test section of this article, you would enter local_host into the Value field of this variable. If you wish to use an external SMTP server, enter its IP address instead.
7. The xml_doc variable holds the XML code generated by the Create XML invoice content Execute SQL Task.

Double-click on the Get Client IDs script task.

The ReadWriteVariable User::client_ids is a System.Object that will be used to store the list of client ids. Double-click the Edit Script button.

The script task code queries the InvoiceDemo.dbo.Clients table for the ClientIDs and stores them in the ClientIdList ArrayList.

Close the Edit Script window, then close the Script Task Editor dialog box, then double-click on the For Each Client ID loop container...

...to bring up the Foreach Loop Editor dialog box.

Click on the Collection node of the Foreach Loop Editor. The Foreach From Variable Enumerator has been selected from the Enumerator dropdown menu, and the System.Object variable User::client_ids has been selected in the Variable dropdown menu.

Click on the Variable Mappings node...

...to see that the variable User::client has been mapped to index zero.

Click the OK button to close the Foreach Loop Editor window,...

...then double-click the Create XML invoice content Execute SQL Task component...

...to bring up the Execute SQL Task Editor dialog box. Not that the ResultSet of the General page has been set to XML, and that the Connection Type has been set to OLE DB, and that the InvoiceDemoOleDb connection manager has been selected.

  Click on the SQLStatement line to bring up the button, then click on then button...

...to bring up the Enter SQL Query editor window.

The previous article in this series explains in detail what the SQL code is doing. It basically creates the XML code in pieces through a series of FOR XML queries, then stitches them together into a single XML document.

Click the OK button to close the SQL editor window...

...then click on the Parameter Mapping page node. Note that the variable name has been set to the package variable client_id. Note that the Directions is set to input, meaning that the client_id is a selection criteria of the query.

Click on the Result Set page node.

Note the the xml_doc package variable has been mapped to the result. This will make the XML code generated by the query available to Create XML invoice file script task.

Click the OK button to close the Execute SQL Task Editor.

Double-click  on the Write XML Invoice file script task...

...and not the three ReadOnly variables and the ReadWrite variable. Click on the Edit Script button...

...to bring up the VB.net code. Note that the client_id and output_path_template variables are used to create the value for the output_path variable, and that the content of the xml_doc variable is used to generate the contents of the output file.

Click the OK button to close the script editor, then click the next OK button to return to the main screen.

Double-click the Email XML invoice file script task...

...to not the read-only package variables being passed to it. Click the Edit Script button...

...to bring up the script editor window. Note the the client_id is used in the query to get the email address of the client. The MailMessage class is used to send an email to the address with the attachment referenced by the output_path.

Click the OK button to close the script window, then click the OK button of the Script Task Editor dialog box...

...to return to the main screen of the package.

Double-click the FTP XML invoice file script task...

...to bring up its Script Task Editor. Note that once again the client_id and output_path of the XML file for that client are being passed to the script task component.

Click on the Edit Script button...

...to bring up the script editor window. Note that the client_id is used to query for the client's FTP server info in the first part of the script task,...

...and that the ConnectionManager class is used to create the FTP connection manager 'cm' using this info in the second part of the script task.

The FtpClientConnection class is then used to create the FTP connection 'ftp' using the AcquireConnection method of the 'cm' connection manager. The 'ftp' connection is then used to connect to the client's server and send the XML file.

Transforming and Shredding Vendor Invoices

The next article in this series will show how clients can transform and shred XML invoices received from multiple vendors.