Importing data using WebService

Hello,

i'm searching a way to update data using a web service.

For example, i want to store the temprature of cities in a database. I have a table with all the cities and an other table storing the temperature.

I have access to à web service that give temperature for the cities...

i hope you understand what i want to do... ;-)

thanks a lotThe web service task can access the web service and store the SOAP envelope as an XML file locally. You can then access the XML file in a data flow task using the XML Source Adapter and use the data in your data-flow as you would any other data.

-Jamie|||Ok !

Where can i find a tutorial for using webservice task ?|||BOL should always be your first port of call. Try ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/extran9/html/5c7206f1-7d6a-4923-8dff-3c4912da4157.htm and if there isn't enough there to help use the "Send Feedback" link.

-Jamie|||It's not clear from the question whether the web service gives you address for a specific city or for all cities. Likely it's the former in which case you'd likely want to call the web service through the script component that wraps the SOAP call and does a little of it's own caching to avoid redundant web service calls.

If the shoe fits, feel free to wear it.|||Hi all,

I am using the web service task with XML source to insert data from a web service into an SQL Server database. But I have a problem with the XML output from the Web Service Task. It looks li this:
<?xml version="1.0" encoding="utf-16"?>
<ArrayOfBooking xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" >
<Booking>
<BookingDate xmlns="http://tempuri.org/">2007-04-12T16:14:33.9210023+03:00</BookingDate>
<FlightDate xmlns="http://tempuri.org/">2007-04-12T16:14:33.9210023+03:00</FlightDate>
</Booking>
<Booking>
<BookingDate xmlns="http://tempuri.org/">2007-04-12T16:14:33.9210023+03:00</BookingDate>
<FlightDate xmlns="http://tempuri.org/">2007-04-12T16:14:33.9210023+03:00</FlightDate>
</Booking>
</ArrayOfBooking>

and because of this xmlns="http://tempuri.org/" that is met almost everywhere I cannot create a proper XSD Schema , that is needed for the XML source. If I remove this xmlns="http://tempuri.org/" , then everything works fine . The web service I am calling has this namespace xmlns="http://tempuri.org/" in the [WebService(Namespace = "http://tempuri.org/")] . I am writing the web service so I can change it as well if this is needed. Do you have any ideas how to solve this xmlns="http://tempuri.org/" problem?

Thanks in advance.

Regards,
pc

Importing data using WebService

Hello,

i'm searching a way to update data using a web service.

For example, i want to store the temprature of cities in a database. I have a table with all the cities and an other table storing the temperature.

I have access to à web service that give temperature for the cities...

i hope you understand what i want to do... ;-)

thanks a lotThe web service task can access the web service and store the SOAP envelope as an XML file locally. You can then access the XML file in a data flow task using the XML Source Adapter and use the data in your data-flow as you would any other data.

-Jamie|||Ok !

Where can i find a tutorial for using webservice task ?|||BOL should always be your first port of call. Try ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/extran9/html/5c7206f1-7d6a-4923-8dff-3c4912da4157.htm and if there isn't enough there to help use the "Send Feedback" link.

-Jamie|||It's not clear from the question whether the web service gives you address for a specific city or for all cities. Likely it's the former in which case you'd likely want to call the web service through the script component that wraps the SOAP call and does a little of it's own caching to avoid redundant web service calls.

If the shoe fits, feel free to wear it.|||Hi all,

I am using the web service task with XML source to insert data from a web service into an SQL Server database. But I have a problem with the XML output from the Web Service Task. It looks li this:
<?xml version="1.0" encoding="utf-16"?>
<ArrayOfBooking xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" >
<Booking>
<BookingDate xmlns="http://tempuri.org/">2007-04-12T16:14:33.9210023+03:00</BookingDate>
<FlightDate xmlns="http://tempuri.org/">2007-04-12T16:14:33.9210023+03:00</FlightDate>
</Booking>
<Booking>
<BookingDate xmlns="http://tempuri.org/">2007-04-12T16:14:33.9210023+03:00</BookingDate>
<FlightDate xmlns="http://tempuri.org/">2007-04-12T16:14:33.9210023+03:00</FlightDate>
</Booking>
</ArrayOfBooking>

and because of this xmlns="http://tempuri.org/" that is met almost everywhere I cannot create a proper XSD Schema , that is needed for the XML source. If I remove this xmlns="http://tempuri.org/" , then everything works fine . The web service I am calling has this namespace xmlns="http://tempuri.org/" in the [WebService(Namespace = "http://tempuri.org/")] . I am writing the web service so I can change it as well if this is needed. Do you have any ideas how to solve this xmlns="http://tempuri.org/" problem?

Thanks in advance.

Regards,
pc

Importing data using import service/DTS

Hi,

I'm using Sql server 2005 and I need to import about 15000 records from a txt file which is delimated using " | " character. Part of the problem is that the file has about 109 fields. The other part of the problem is that there aren't any headers defined. The field name and their respective data types are defined in a sepearte excel sheet. When I'm trying to import it using Sql Server 2005 import wizard, I would have to modify the names and the length manually and change the data types which is a time consuming job. My boss has claimed that he did it in Sql Server 2000 without using BCP utility and he got the table columns from the excel file (he didn't have to modify it manually somehow he import it) but he can't really remember !!!!!!!!

Now i'm really confused and a bit ashamed as well as he is around 62 years old.

Any idea of how it could be done using Sql Server 2005 or Sql Server 2000?

Thanks in advance.

Hi,

Can somebody please reply to this thread .......

Thanks

|||

Using the Bulk Insert task, you can specify a format file. This could be easily generated from the Execl sheet, and would be quicker than configuring the text file connection manager. Note this is not using teh Wizard, but the full designer.

The DTS Wizard had no way of using an Excel file or other format file either.

|||

DarrenSQLIS wrote:

Using the Bulk Insert task, you can specify a format file. This could be easily generated from the Execl sheet, and would be quicker than configuring the text file connection manager. Note this is not using teh Wizard, but the full designer.

The DTS Wizard had no way of using an Excel file or other format file either.

Thanks Darren. Can you please specify on how can i do it and which full designer. I'm sorry but i'm not use to Sql Server 2005.

|||

The designer, aka Business Intelligence Development Studio (BIDS) aka Visual Studio (VS).

Create a new Integration Services project type. This is the SSIS package designer.

Drag tasks from the toolbox, and I suggest you start with the Bulk Insert Task.

There is a tutorials section in the SQL help, Books Online, that may be worth a look as well.