Pulling Data from WebService into Sql Server DB in SSIS

浏览: 3353

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. 

Clipboard Image.png

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: 

  • Variable
  • File Connections

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

Clipboard Image.png

Step 2: Add a web service task into the design pane.

Clipboard Image.png

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. 

Clipboard Image.png

Step 4: Create an empty wsld file.  For this example I created a file called mywsld in the "C:\Vault\Project\BI\WebService" folder. 

Clipboard Image.png

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. 

Clipboard Image.png

then go to the wsdl folder, you can see the date has already been modified

Clipboard Image.png

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. 

Clipboard Image.png

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.

Clipboard Image.png

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.

Clipboard Image.png

Step 2: Double click on the XML Task

Clipboard Image.png

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.

Clipboard Image.png

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:

Clipboard Image.png


Step 5: Double click this XML Source, then 'Generate XSD...'.

Clipboard Image.png


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.

Clipboard Image.png

the destination is Sql Server DB, which looks like the below connection Manager.

Clipboard Image.png

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!

推荐 2
本文由 Flash 创作,采用 知识共享署名-相同方式共享 3.0 中国大陆许可协议 进行许可。
转载、引用前需联系作者,并署名作者且注明文章出处。
本站文章版权归原作者及原出处所有 。内容为作者个人观点, 并不代表本站赞同其观点和对其真实性负责。本站是一个个人学习交流的平台,并不用于任何商业目的,如果有任何问题,请及时联系我们,我们将根据著作权人的要求,立即更正或者删除有关内容。本站拥有对此声明的最终解释权。

2 个评论

It's better to show the data of destination table.
Flash

Flash 回复 choc

Sorry, buddy! One company case, data privacy policy.

要回复文章请先登录注册