none
Call Webservice Method in integration service project and GetData in SqlServer for join to my tables' data

    Question

  • hi

    I called webservice method in integration service project with Web Service Task control.

    then I want get data from xml output file and send them to sql server to View(object) so can select them or join to my table's data 

    can I do that?

    any help would be greatly appreciated

    (sorry for my English)


    Best Regards Narges
    Thursday, January 20, 2011 12:29 PM

Answers

  • one of ways to schedule an SSIS package is with sql server agent job, and don't worry about it, you can change the schedule anytime you want.

     

    yes , I meant to stored result into database tables.

    Note that you can not store result of webservice from ssis package into memory and then use this data in memory in your application. because there is no connection between these.

    you should stored these results somewhere, for example you can write them to sql server tables, or excel file or flat file. As .NET are very simple to work with sql server tables I think sql server database can be good option.

    and don't worry about this tables, these are just reporting tables, you can delete all data from these tables at first step of your ssis package, because everytime you fill this data again.

     

     


    http://www.rad.pasfu.com
    • Marked as answer by Narges_Raha Monday, January 24, 2011 11:53 PM
    Monday, January 24, 2011 10:58 PM
    Moderator

All replies

  • hi

    I called webservice method in integration service project with Web Service Task control.

    then I want get data from xml output file and send them to sql server to View(object) so can select them or join to my table's data 

    can I do that?

    any help would be greatly appreciated

    (sorry for my English)


    Best Regards Narges


    Hi Narges,

    Can you provide more details what kind of web service you are trying to use? For simple scenarios, it may work. However you should know that the Web Service Task is limited in scope and more complex features are not supported.


    SSIS Tasks Components Scripts Services | http://www.cozyroc.com/
    Thursday, January 20, 2011 8:28 PM
  • Hi CozyRoc,

    thanks for your reply

    I have an asp.net web service

    I create a simple webservice and integration project and uploaded it here: http://www.4shared.com/file/-EhzT0ee/EmployeeintegrationTest.html

    please look at it.

    my webservice method returns a buisness object.

    in integration service project, the webservice task create a xml file with name Employee.xml

    i don't know how get data from Employee.xml and send it to sql. 

    in my View for example i need Employee.ID  for select from my tables.

    when webservice method returns a list of Employee (business object), how can I send them to sql?



    Best Regards Narges
    Saturday, January 22, 2011 9:34 AM
  • Narges,

    Have you tried using the Data Flow task and the standard XML Source component?


    SSIS Tasks Components Scripts Services | http://www.cozyroc.com/
    Saturday, January 22, 2011 2:10 PM
  • thanks for your soon reply

    yes, in Xml Source Editor when Generating XSD, I saw this error:

    Unable to infer the XSD from .The XML Contains multiple namespaces.

     

     


    Best Regards Narges
    Saturday, January 22, 2011 2:38 PM
  • thanks for your soon reply

    yes, in Xml Source Editor when Generating XSD, I saw this error:

    Unable to infer the XSD from .The XML Contains multiple namespaces.

     

     


    Best Regards Narges

    This is a known issue. The XML Source supports only one namespace. You can transform your XML with XSL before processing it with the XML Source component. Check this article by Matt Masson.

    SSIS Tasks Components Scripts Services | http://www.cozyroc.com/
    Saturday, January 22, 2011 3:07 PM
  • For some reason I was able to create an XML Schema (using Visual Studio 2010) to work with the file even with multiple schema (see below).

    You have a second problem with your output.  The record is at the root of the document and the XML Source won't process the root as a record. What you need to do is to convert your output to:

    <?xml version="1.0" encoding="utf-16"?>
    <root>
    <BOEmployee>
     <Email >name@gmail.com</Email>
     <FName >Ali</FName>
     <LName >Rezaee</LName>
     <FatherName >Saman</FatherName>
     <ID >111111</ID>
     <Address >address</Address>
     <SSN >1232323</SSN>
    </BOEmployee>
    </root>
    

    You use Matt's method as suggested by CozyRoc (http://blogs.msdn.com/b/mattm/archive/2007/12/15/xml-source-making-things-easier-with-xslt.aspx) and make sure that you add some kind of root.

    I found that I could read the original document with all of the extra namespaces by creating my own schema:

    <?xml version="1.0" encoding="utf-16"?>
    <xs:schema xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
          xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xs="http://www.w3.org/2001/XMLSchema" 
          attributeFormDefault="unqualified" elementFormDefault="qualified"
          targetNamespace="http://tempuri.org/"
          >
     <xsd:element name ="root">
      <xsd:complexType>
       <xsd:sequence>
    
        <xsd:element name="BOEmployee">
      <xsd:complexType>
       <xsd:sequence>
        <xsd:element name="Email" type="xs:string"/>
        <xsd:element name="FName" type="xs:string"/>
        <xsd:element name="LName" type="xs:string"/>
        <xsd:element name="FatherName" type="xs:string" />
        <xsd:element name="ID" type="xs:string" />
        <xsd:element name="Address" type="xs:string" />
        <xsd:element name="SSN" type="xs:string"/>
       </xsd:sequence>
      </xsd:complexType>
     </xsd:element>
       </xsd:sequence>
      </xsd:complexType>
     </xsd:element>
    </xs:schema>

    Russel Loski, MCT
    Saturday, January 22, 2011 3:23 PM
  • Hi

    thanks for your time

    how can I convert my output to

    <?xml version="1.0" encoding="utf-16"?>
    <root>
    <BOEmployee>
     <Email >name@gmail.com</Email>
     <FName >Ali</FName>
     <LName >Rezaee</LName>
     <FatherName >Saman</FatherName>
     <ID >111111</ID>
     <Address >address</Address>
     <SSN >1232323</SSN>
    </BOEmployee>
    </root>
    
    

    how add <root> element?

    In XML Task Editor for Destination and SecondOperand what files should I set?

    I create empty file, but I saw this error:[XML Task] Error: Property "Second Operand (XSL document)" has no source Xml text; Xml Text is either invalid, null or empty string.

    I am beginner to ssis. please say to me how many steps or what controls should I use to send my data to sql.

     

     


    Best Regards Narges
    Sunday, January 23, 2011 6:46 AM
  • I am agree with others, you can change style of your xml file with an XSLT Task, this is a sample of using xml task with XSLT option:

    http://www.rad.pasfu.com/index.php?/archives/21-XML-Task-Changing-Style-of-Data-XSLT.html

    the only thing you need is a bit understanding of XSLT, this quick tutorial can be good starting point:

    http://www.w3schools.com/xsl/


    http://www.rad.pasfu.com
    Sunday, January 23, 2011 7:13 AM
    Moderator
  • Hi, thanks for your reply

    suppose I have xml or xsd file. how send it's data to view(object) in sql. I don't want store it's data in table. only I want select from it's data in view.

    please help me

    my output xml file is like this: (is it xsd file. right?)

     <?xml version="1.0" encoding="utf-8" ?> 
    - <Student xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://tempuri.org/">
     <Contacts /> 
     <IT_Code>1</IT_Code> 
     <Code>1111</Code> 
     <PersonID>1</PersonID> 
     <Fa_FirstName>محرم</Fa_FirstName> 
     <Fa_LastName>كاظم </Fa_LastName> 
     <Fa_Aliyas_FirstName>کامران  </Fa_Aliyas_FirstName> 
     <En_FirstName>MAGERRAM</En_FirstName> 
     <En_LastName>KAZEM OV</En_LastName> 
    - <StudentGender>
     <GenderID>2</GenderID> 
     <GenderTitle>مرد</GenderTitle> 
     </StudentGender>
    - <Religion>
    - <xs:schema id="NewDataSet" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
    + <xs:element name="NewDataSet" msdata:IsDataSet="true" msdata:MainDataTable="Table" msdata:UseCurrentLocale="true">
    + <xs:complexType>
    + <xs:choice minOccurs="0" maxOccurs="unbounded">
    + <xs:element name="Table">
    + <xs:complexType>
    + <xs:sequence>
     <xs:element name="StudentReligionId" type="xs:int" minOccurs="0" /> 
     <xs:element name="BeginDate" type="xs:dateTime" minOccurs="0" /> 
     <xs:element name="ReligionID" type="xs:int" minOccurs="0" /> 
     <xs:element name="StudentId" type="xs:long" minOccurs="0" /> 
     <xs:element name="EndDate" type="xs:dateTime" minOccurs="0" /> 
     <xs:element name="ArReligion" type="xs:string" minOccurs="0" /> 
     <xs:element name="EnReligion" type="xs:string" minOccurs="0" /> 
     <xs:element name="FaReligion" type="xs:string" minOccurs="0" /> 
     <xs:element name="ReligionId1" type="xs:int" minOccurs="0" /> 
     </xs:sequence>
     </xs:complexType>
     </xs:element>
     </xs:choice>
     </xs:complexType>
     </xs:element>
     </xs:schema>
    - <diffgr:diffgram xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" xmlns:diffgr="urn:schemas-microsoft-com:xml-diffgram-v1">
    - <NewDataSet xmlns="">
    - <Table diffgr:id="Table1" msdata:rowOrder="0">
     <StudentReligionId>4776</StudentReligionId> 
     <ReligionID>6</ReligionID> 
     <StudentId>1</StudentId> 
     <FaReligion>شیعه</FaReligion> 
     <ReligionId1>6</ReligionId1> 
     </Table>
     </NewDataSet>
     </diffgr:diffgram>
     </Religion>
    - <Nationality>
    - <xs:schema id="NewDataSet" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
    - <xs:element name="NewDataSet" msdata:IsDataSet="true" msdata:MainDataTable="Table" msdata:UseCurrentLocale="true">
    - <xs:complexType>
    - <xs:choice minOccurs="0" maxOccurs="unbounded">
    - <xs:element name="Table">
    - <xs:complexType>
    - <xs:sequence>
     <xs:element name="StudentAlligianceId" type="xs:int" minOccurs="0" /> 
     <xs:element name="BeginDate" type="xs:dateTime" minOccurs="0" /> 
     <xs:element name="NationID" type="xs:int" minOccurs="0" /> 
     <xs:element name="StudentId" type="xs:long" minOccurs="0" /> 
     <xs:element name="EndDate" type="xs:dateTime" minOccurs="0" /> 
     <xs:element name="ArNation" type="xs:string" minOccurs="0" /> 
     <xs:element name="EnNation" type="xs:string" minOccurs="0" /> 
     <xs:element name="FaNation" type="xs:string" minOccurs="0" /> 
     <xs:element name="NationId1" type="xs:int" minOccurs="0" /> 
     </xs:sequence>
     </xs:complexType>
     </xs:element>
     </xs:choice>
     </xs:complexType>
     </xs:element>
     </xs:schema>
    - <diffgr:diffgram xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" xmlns:diffgr="urn:schemas-microsoft-com:xml-diffgram-v1">
    - <NewDataSet xmlns="">
    - <Table diffgr:id="Table1" msdata:rowOrder="0">
     <StudentAlligianceId>26935</StudentAlligianceId> 
     <BeginDate>1976-03-21T00:00:00-07:00</BeginDate> 
     <NationID>111</NationID> 
     <StudentId>1</StudentId> 
     <FaNation>آذربايجان</FaNation> 
     <NationId1>111</NationId1> 
     </Table>
     </NewDataSet>
     </diffgr:diffgram>
     </Nationality>
     </Student>
    

    Best Regards Narges
    Monday, January 24, 2011 8:47 AM
  • In the sample xml data you put here, you can use this xslt :

    <?xml version='1.0' ?>
    <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
    <xsl:output method="xml" indent="yes" version="2.0" encoding="UTF-8" />
       <xsl:template match="/">
          <xsl:element name="Students">
        <xsl:for-each select="Student" >
             <xsl:element name="Student">
                <xsl:element name="IT_Code">
                   <xsl:value-of select="./IT_Code" />
                </xsl:element>
    
                <xsl:element name="Code">
                   <xsl:value-of select="./Code"/>
                </xsl:element>
    
                <xsl:element name="Fa_LastName">
                   <xsl:value-of select="./Fa_LastName"/>
                </xsl:element>
    
                <xsl:element name="En_FirstName">
                   <xsl:value-of select="./En_FirstName"/>
                </xsl:element>
    
         
             </xsl:element>
    
          </xsl:for-each>
          </xsl:element>
       </xsl:template>
    </xsl:stylesheet>
    

    Note that in this sample xslt , only few fields fetched, but you can change it and add any other fields you want.

    then the input xml will convert into flatten xml ,

    then you can use it in a data flow task with xml source and any destination you like.


    http://www.rad.pasfu.com
    Monday, January 24, 2011 9:02 AM
    Moderator
  • thanks, I know it

    but for ReligionID I wrote

    <xsl:element name="Religion">
     <xsl:value-of select="Student/Religion/NewDataSet/Table/ReligionID" />
     </xsl:element>
    

    and it doesn't work.

    for xml file like this

     <?xml version="1.0" encoding="utf-8" ?> 
     <boolean xmlns="http://tempuri.org/">true</boolean> 

    what xslt code can I write?

    is there a tutorial for different xml convert to flatten xml?

    please guide me



    Best Regards Narges
    Monday, January 24, 2011 9:29 AM
  • I found it for ReligionID

    I wrote:

    <xsl:element name="StudentReligionId" >
     <xsl:value-of select="Student/Religion" />
     </xsl:element>
    
    

    and it returns Religion Properties.(but I don't know how get ReligionID)

    and for

    <?xml version="1.0" encoding="utf-8" ?> 
     <boolean xmlns="http://tempuri.org/">true</boolean> 
    
    

    I have problem.

    then I used xmlsource and SqlServerDestination. right?

    in SQL Destination Editor in Mapping tab for Available Input Columns I see only GenderID. I expect IT_Code,Code,GenderID and Relegion properies.

    I maped GenderID with one of the Column of my view. but I need send IT_Code,Code,GenderID and Relegion properies to view and select them like columns of a table.

    please help me.


    Best Regards Narges
    Monday, January 24, 2011 11:41 AM
  • what do you get as "Religion Properties" ? I want to know what is result of your xslt? and then we can find out a way to fetch ReligionID

     

    and about your second problem:

    I didn't get what you mean exactly, when you use the xml in xml source then you have all columns there, simply you can map these columns to columns in the sql server destination, What is your problem? you can't see xml source columns? or what? explain it more...

    Note that you have to use the xml which generated as result of xml task.

     

    Sorry for my late replies, I'm a bit busy today.

     


    http://www.rad.pasfu.com
    Monday, January 24, 2011 12:08 PM
    Moderator
  • thanks for your reply. your welcome.

    for Religion properties in w3schools.com I get: 4776 6 1 شیعه  6

    in view I should select columns of my database tables. but I want select data(columns) of this xml file.

    and then I write query that select or join data of my tables with data in my view.


    Best Regards Narges
    Monday, January 24, 2011 1:02 PM
  • sorry for my frequent questions!

    I have another xml file that it's structure is similar to Religion in above code. I uploaded it here:

    http://www.4shared.com/document/AAehyn-o/GetStudentByCode.html

    is there any tools that automatic convert xml to flatten xml? or it's better that I write it?


    Best Regards Narges
    Monday, January 24, 2011 1:54 PM
  • for your last question,

    I wrote this code for flatten your xml file:

          System.Xml.XmlDocument xdoc = new System.Xml.XmlDocument();
          System.Xml.XmlDocument xdocResult = new System.Xml.XmlDocument();
          xdoc.Load(@"D:\SSIS\New folder\GetStudentByCode.xml");
          System.Xml.XmlNode ParentNew = xdocResult.CreateNode(System.Xml.XmlNodeType.Element, "Data", string.Empty);
    
          foreach (System.Xml.XmlNode xnode in xdoc.GetElementsByTagName("DocumentElement"))
          {
            
    
            foreach (System.Xml.XmlNode xchild in xnode.ChildNodes)
            {
              xchild.Attributes.RemoveAll();
              System.Xml.XmlNode importNode = ParentNew.OwnerDocument.ImportNode(xchild, true);
    
              ParentNew.AppendChild(importNode);
    
    
            }
          }
          xdocResult.AppendChild(ParentNew);
          xdocResult.Save(@"D:\SSIS\New folder\GetStudentByCode_Converted.xml");
    
    


    http://www.rad.pasfu.com
    Monday, January 24, 2011 5:43 PM
    Moderator
  • thank you very much.

    your code works fine.

    but in XML Task I can't use it. what is xslt code for convert "<xs:schema ...." to xml?

    another question is:

    in view I should select columns of my database tables. but I want select data(columns) of this xml file.

    and then I write query that select or join data of my tables with data in my view.

    (I promise that was my last question! :D)


    Best Regards Narges
    Monday, January 24, 2011 6:37 PM
  • "but in XML Task I can't use it. what is xslt code for convert "<xs:schema ...." to xml?"

    why? did you used converted xml file as source? did you clicked on Generated xsd? did you received any errors? if yes what errors?

     

    "

    in view I should select columns of my database tables. but I want select data(columns) of this xml file.

    and then I write query that select or join data of my tables with data in my view."

     

    could you upload an screenshot somewhere and give us the link? I think you did something wrong. for example did you connect xml source to the destination with data path ( green arrow ) ?


    http://www.rad.pasfu.com
    Monday, January 24, 2011 6:41 PM
    Moderator
  • thanks for your soon reply.

    I see. I used converted xml file without error. but I need when package runs, webmethod of webservice calls and xml output automatically convert to flatten xml and then XML Source converts it to .xsd and then with Sql Server Destination or OLE DB Destination I send data to sqlserver.

    so I need xslt code. right?

     

    (I mean I need all of Students returns by webservice method and I don't want insert them into my table. I want I have them in view and for special operation for example GetLoan I insert some of them into my table)I don't know this is possible or not!

     

    but for simple example this is my view and table:

    http://www.4shared.com/file/zYz40n6v/Table_View.html

    with your Code and GetStudentByCode_Converted.xml file I used a OLE DB Destination(I mapped Code from available column with StudentId), but in "Result Execution" tab I received these errors:

    (as you see the error shows conflict for foreign key)

     

    [OLE DB Destination [351]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
    An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: 
    "The statement has been terminated.". An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description:
    "The INSERT statement conflicted with the FOREIGN KEY constraint "FK_AccountNumber_Bank". The conflict occurred in database "Refahi", table "dbo.Bank", column 'Id'.". [OLE DB Destination [351]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR.
    The "input "OLE DB Destination Input" (364)" failed because error code 0xC020907B occurred,
    and the error row disposition on "input "OLE DB Destination Input" (364)" specifies failure on error.
    An error occurred on the specified object of the specified component.
    There may be error messages posted before this with more information about the failure. [SSIS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED.
    The ProcessInput method on component "OLE DB Destination" (351) failed with error code 0xC0209029 while processing
     input "OLE DB Destination Input" (364). The identified component returned an error from the ProcessInput method.
    The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.
    There may be error messages posted before this with more information about the failure.

     

     

    and with Sql Server Destination I received this error (convert datatype):

     

    Error at Data Flow Task[SQL Server Destination [329]]: The column "Code" can't be inserted because the conversion between types DT_UI2 and DT_I4 is not supporte.
    
    Error at Data Flow Task[SSIS.Pipeline]: "component" SQL Server Destination "(329)" failed validation and returned validation status "VS_ISBROKEN".
    
    Error at Data Flow Task: There were errors during task validation.
    

     


    Best Regards Narges
    Monday, January 24, 2011 8:35 PM
  • for first part of your question:

    you can automate it in this way:

    set your web service task to write result into an xml file

    then use a script task and write my code there to change the input xml file to new converted flatten file

    then use this new flatten file as source in xml source, ( you need to generate xsd for first time, this xsd will work next times because data structure wouldn't change)

     

    for your second question about having this data in a view :

    I think you want to have this data in memory actually.

    let me ask you that where you want to use it? I'm asking this because you can simply store result of this xml data into an object type variable and this will store in memory, but there are other cases which raise performance if you don't use another variable. SSIS Data flow will help you to use in-memory structure, but first you should say your scenario exactly to find best approach.

     

    I couldn't see that file, seems removed.

     

    and about error lines :

    there are lots of errors here, I see errors for conflicting means that you are inserting redundant data. errors for datatype which you can supress them by data conversion transform.

     

    and last line is:

    I think this will be better if you explain overall scenario and then we can help you better.


    http://www.rad.pasfu.com
    Monday, January 24, 2011 8:52 PM
    Moderator
  • thank you

    about uploaded file: I uploaded it and submit the reply with link, then tested it.sorry while I was editing the reply and changing the link you clicked on it. it works now.

    (sorry for my English. may I send to your email scenario in Persian?)

    I say scenario:

    students' educational information like average, marks,... are in another database and I can access to these information with web service methods.

    in my application I want create groups of students with these information, for example: students that have average greater than 18 and their educational level is master of science  now. these groups dynamically creates by user of application. It means user selects the features(arbitrary) and select a title for group.

    (I don't want repeat these information in my tables) now I want have students with features in view and for showing Student List for each group, I select from this view.

    I hope could explain clearly!


    Best Regards Narges
    Monday, January 24, 2011 9:35 PM
  • OK, That make sense enough.

    my question is : why you want to do it with SSIS? SSIS doesn't designed for this purpose.

    you have user interaction in your scenario, and your result may differ based on user inputs. So I recommend to write .NET application to do these. you can get user inputs simply and call webservice and return result as user needs.

    SSIS can be good if your scenario is not as online as this case, I mean that for example you can write a package to fetch data from webservice and save results in staging database tables, and then just use this staging database when you work with user, you can suppress calling webservices in this scenario. But note that this is an offline scenario because this ssis package will run by an schedule and it's data will be updated based on schedule. using this way is based on your scenario. If you need online data don't use this way. but if you can provide data till yesterday and this data will be enough for queries and user reports so you can use it.

     

    Note that in both ways you need to implement a .net application to work with user interaction. You can not work with ssis as an application which get user input and make desired reports.

     

    Hope it make sense.let me know if you need more explanation.

    I prefer to continue discussion here because msdn forum threads are traceable by google and other SEOs , maybe someone face same issue in feature and this thread helps that time.


    http://www.rad.pasfu.com
    Monday, January 24, 2011 10:11 PM
    Moderator
  • thanks alot for your help

    yes, I'm writing a .net application for user. but I won't call web service method in it because these methods change in future and I won't change my code and publish again my application.

    can I change schedule of running packages? I can use yesterday data because I know data won't change everyday. but changing the schedule seems necessary.

    and you said: "save results in staging database tables"

    does it mean I should save data in table? this is only way to execute query from data?

     


    Best Regards Narges
    Monday, January 24, 2011 10:42 PM
  • one of ways to schedule an SSIS package is with sql server agent job, and don't worry about it, you can change the schedule anytime you want.

     

    yes , I meant to stored result into database tables.

    Note that you can not store result of webservice from ssis package into memory and then use this data in memory in your application. because there is no connection between these.

    you should stored these results somewhere, for example you can write them to sql server tables, or excel file or flat file. As .NET are very simple to work with sql server tables I think sql server database can be good option.

    and don't worry about this tables, these are just reporting tables, you can delete all data from these tables at first step of your ssis package, because everytime you fill this data again.

     

     


    http://www.rad.pasfu.com
    • Marked as answer by Narges_Raha Monday, January 24, 2011 11:53 PM
    Monday, January 24, 2011 10:58 PM
    Moderator
  • thanks a lot for your reply.

    I insert into one of sql server tables my data a few minutes ago.


    Best Regards Narges
    Tuesday, January 25, 2011 12:06 AM