Background:
The OLTP data source is from WebService, we want to pull this data into the specified tables of sql server. How can we do this? In this article, i will show you a simple example to call a Web Service from SSIS and store the results in a file, then insert into database.
Solution:
Part 1: Get data from WS
The SSIS framework provides a Web Service task which executes a Web Service method. You can save the Web Service returns as the following types:
Because i need to save the data into DB, and the data is too much. i prefer to use file connections to save the WebService returns.
Step 1: Create an SSIS Package
Step 2: Add a web service task into the design pane.
Step 3: Double click on the Web Service Task and create a new HTTP connection, then press the "Test Connection". It popups the message: Test connection succeeded.
Note: you can open your own url by browser. https://FlashBI.com/GetData.asmx is my sample WS url, you cannot use it.
Step 4: Create an empty wsld file. For this example I created a file called mywsld in the "C:\Vault\Project\BI\WebService" folder.
Step 5: In the WSDL File property specify the path of the file:C:\Vault\Project\BI\WebService. Make sure that the OverwriteWSDLFile option is set to true and press the Download WSDL button.
then go to the wsdl folder, you can see the date has already been modified
Step 6: In the Input tab, i specify which service and method i need to use. there are two parameters in this method: GetBIData. In SSIS, i define two variables:
[sCheckstring]: it is used for security, this value will be set in configuration file, i will pass the value by config file to this variable, then used in this method.
[dtFrom]: it is dynamic, which is get from one log table in database.
Step 7: In the Output tab, in the File properties, select "New Connection". It will popup a new windows, you can select the "Existing file" option in the Usage type, then find one xml file (i created one blank xml file: XMLData.xml). Press Okay. [XMLData.xml] is the file where the XML data will be written to when the package is run and the web service is called.
Step 8: Run the SSIS project. You can see the data will be saved in XMLData.xml.
Part 2: Save data into Database
We have already save the data into XML, then will
Step 1: Add a XML task into the design pane.
Step 2: Double click on the XML Task
Source: choose the "XMLData.xml" as the datasource,
SaveOperationResult: True
Destination: create another xml file : ValidatedXMLData.xml according to the step 7 in Part 1,
OverWriteDestination: True
SecondOperand: XSLT.xslt, i create it by Altova mapping and it looks like the below:
<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="2.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:msxsl="urn:schemas-microsoft-com:xslt" exclude-result-prefixes="msxsl"
xmlns:diffgr="urn:schemas-microsoft-com:xml-diffgram-v1">
<xsl:output method="xml" encoding="UTF-8" indent="yes"/>
<xsl:template match="DataSet/diffgr:diffgram/DsAllTables|DataSet/diffgr:diffgram/DsAllTables//*|text()">
<xsl:copy>
<xsl:apply-templates select='*|text()' />
</xsl:copy>
</xsl:template>
</xsl:stylesheet>
When you run the XML task, it will take your original file[XMLData.xml] and the apply the transformation rules defined in the XSLT file[XSLT.xslt]. The results will be saved in your new XML file[ValidatedXMLData.xml]. this processed xml[ValidatedXMLData.xml] can be used as the XML source.
Step 3: Add a Data Flow task into the design pane.
Step 4: Double click this Data Flow Task, then to navigate to the Data Flow TAB. Place an XML Source component and OLE DB Command as shown in the below:
Step 5: Double click this XML Source, then 'Generate XSD...'.
Step 6: Configure the OLE DB Command, and add One Data Conversion between the XML source and OLE DB Command, because i have the type mapping problem.
the destination is Sql Server DB, which looks like the below connection Manager.
Execute your package, you will find the data will be saved into destination table.
From the above steps, you can see, the XML result[ValidatedXMLData.xml] actually is one data set, which includes many tables. DsAllTables.xsd is the schema. Currently, my SSIS works well. If you have any good suggestion, pls let me know. Thanks very much!