Microsoft SQL Server Integration Services (SSIS)

1. Create a New SSIS Project

Open SQL Server Data Tools and create a new Integration Services Project.

2. Add a Data Flow Task

Drag a 'Data Flow Task' from the SSIS Toolbox onto the Control Flow tab of your package.

3. Configure the Data Flow

a. Add a Web Service Data Source

Double-click the 'Data Flow Task' to open the Data Flow tab.

From the SSIS Toolbox, drag a 'Web Service Task' onto the Data Flow canvas. You might need to configure this in the Control Flow first to define connection managers.

Alternatively, and often more flexible for direct data extraction into a flow:

Drag an 'HTTP Connection Manager' and a 'SOAP Web Service Task' (if applicable) or a custom script component that can handle HTTP requests.

For a simpler XML extraction, consider using a 'Web Service Task' in the Control Flow to call the service and save the response to a file, then use a 'File Source' in the Data Flow.

Let's assume for this example we use a Data Flow with a component that directly fetches data.

Add a 'Script Component' and configure it as a Source. In the script:

This script would make an HTTP request to the web service URL, receive the response (e.g., XML), parse it, and output rows.

4. Script Component Example (C#)

This is a simplified example. You would need to add references to System.Net and System.Xml.

            
using System.Net;
using System.Xml;

public override void CreateNewOutputRows()
{
    string webServiceUrl = "http://api.example.com/orders?apiKey=YOUR_API_KEY"; // Replace with actual URL

    try
    {
        using (WebClient client = new WebClient())
        {
            string xmlData = client.DownloadString(webServiceUrl);
            XmlDocument doc = new XmlDocument();
            doc.LoadXml(xmlData);

            // Assuming your XML has a structure like:
            // <Orders>
            //   <Order>
            //     <OrderID>123</OrderID>
            //     <CustomerID>C001</CustomerID>
            //     <OrderDate>2023-10-27</OrderDate>
            //     <TotalAmount>150.75</TotalAmount>
            //   </Order>
            // </Orders>

            XmlNodeList orderNodes = doc.SelectNodes("//Order"); // Adjust XPath as needed

            foreach (XmlNode node in orderNodes)
            {
                // Define your output columns here
                // Example: Output0Buffer.AddRow();
                // Example: Output0Buffer.OrderID = int.Parse(node.SelectSingleNode("OrderID").InnerText);
                // Example: Output0Buffer.CustomerID = node.SelectSingleNode("CustomerID").InnerText;
                // Example: Output0Buffer.OrderDate = DateTime.Parse(node.SelectSingleNode("OrderDate").InnerText);
                // Example: Output0Buffer.TotalAmount = decimal.Parse(node.SelectSingleNode("TotalAmount").InnerText);

                // Add row to output buffer
                Output0Buffer.AddRow();
                Output0Buffer.OrderID = int.Parse(node.SelectSingleNode("OrderID").InnerText);
                Output0Buffer.CustomerID = node.SelectSingleNode("CustomerID").InnerText;
                Output0Buffer.OrderDate = DateTime.Parse(node.SelectSingleNode("OrderDate").InnerText);
                Output0Buffer.TotalAmount = decimal.Parse(node.SelectSingleNode("TotalAmount").InnerText);
            }
        }
    }
    catch (Exception ex)
    {
        // Handle exceptions (e.g., log error, redirect to error output)
        // Dts.Events.FireError(0, "Web Service Extraction", ex.Message, "", 0);
        // Dts.TaskResult = (int)ScriptResults.Failure;
    }
}
            
            

Note: You will need to define the output columns in the Script Component Editor (e.g., OrderID, CustomerID, OrderDate, TotalAmount) to match your data structure.

5. Add a Destination Component

From the SSIS Toolbox, drag an 'OLE DB Destination' (or SQL Server Destination) onto the Data Flow canvas.

Connect the output of your Script Component (or other source) to the 'OLE DB Destination'.

Configure the 'OLE DB Destination' with your connection manager and the target table.

Map the columns from your output to the columns in your destination table.

6. Error Handling

Implement error handling for network issues, invalid data, or web service errors. Use 'precedence constraints' with failure paths or configure outputs for error rows within the Script Component.

7. Deployment and Execution

Deploy your SSIS package to an SSIS Catalog or as a file.

Schedule the package to run at desired intervals using SQL Server Agent or execute it manually.