Consuming from Web Service and Load data into SQL

Hi:

Can someone help me with a SSIS package that would consume from a Web Service (in fact two of them) and then load the data into SQL Server. I currently have Web Service task which connects to ForEachLoop task, and inside the loop task, I have a DFT. I am thinking, I would need to call the webservice utilizing the Web Services Task, and then store the output in a Full ResultSet variable. In my loop, I would like to loop thru the resultset, and store the data into SQL server. Inside the DFT, how would I construct this mechanism? Also, is this a good way to consume from a Web Service and then populate SQL Server? Are there any alternate ideas on this? Any documentation on this yet? Thanks.

MA,

Let me clarify. Do you want to consume data from a web service from within the data-flow?

-Jamie

|||

Well, the goal is to call a web service, and pump data into SQL server, although I thought it's less complex to hook up to a Web Services Task, and then utilize the output from the Web Services within a DFT somehow but not sure. Is there a better way to do this. Thanks.

|||

I think so, yes. It is possible to consume data from a web service from directly within the pipeline. What you are proposing would be an extra step.

To consume from a web service in the pipeline you will need a script component. Donald Farmer's book (http://www.amazon.com/Rational-Guide-Extending-Script-Guides/dp/1932577254/ref=pd_bbs_sr_1/104-7087211-5731917?ie=UTF8&s=books&qid=1181582618&sr=8-1) has a chapter explaining how to do it.

-Jamie

|||

It looks like utilizing the XML Adapter task in the DFT, would allow us to read data from a Variable, not sure how this feature works, but will provide comments, once its working for me. My goal is to avoid using the script component, and utilize existing tasks to accomplish this goal, lets see where I get with that :-)

|||

MA2005 wrote:

It looks like utilizing the XML Adapter task in the DFT, would allow us to read data from a Variable, not sure how this feature works, but will provide comments, once its working for me. My goal is to avoid using the script component, and utilize existing tasks to accomplish this goal, lets see where I get with that :-)

Fair enough. I think that's a worthy aim.

Out of interest, why do you not want to use the script component?

-Jamie

|||

No reason, just exploring an alternate solution. :-)

Constant WAITFOR(RECEIVE) in Profile Trace?

After seeing abnormally high load on the sql server this morning, i've
been investigating. After ruling out the usual sources, i ran a trace
on the DB, limited to showing only queries taking longer than 2000
miliseconds, only to find a constant stream of:
WAITFOR (RECEIVE message_body FROM WMIEventProviderNotificationQueue),
TIMEOUT 5000
it stops ~5000ms, then another one occurs!
Is this causing my load? I've had no luck googling for that message,
so not sure how common it seems to be .. Only thing I can think of is
last week we set up database mail, is this related? The field
"message_body" seems to indicate perhaps it is related to this?
Is it normal? or is this my CPU load problem?
How do I fix it? Thanks in advance!
<vidguide@.gmail.com> wrote in message
news:1170282805.989902.152780@.j27g2000cwj.googlegr oups.com...
> After seeing abnormally high load on the sql server this morning, i've
> been investigating. After ruling out the usual sources, i ran a trace
> on the DB, limited to showing only queries taking longer than 2000
> miliseconds, only to find a constant stream of:
> WAITFOR (RECEIVE message_body FROM WMIEventProviderNotificationQueue),
> TIMEOUT 5000
> it stops ~5000ms, then another one occurs!
> Is this causing my load? I've had no luck googling for that message,
> so not sure how common it seems to be .. Only thing I can think of is
> last week we set up database mail, is this related? The field
> "message_body" seems to indicate perhaps it is related to this?
> Is it normal? or is this my CPU load problem?
> How do I fix it? Thanks in advance!
>
I am not sure exactly what process is causing this, but shouldn't be a
performance problem. This is a normal usage pattern for a service broker
application reading a queue.
The WAITFOR RECEIVE is the command to perform a blocking read on a queue.
It consumes no resources while waiting, times out after 5000ms and
immediately starts another blocking read.
David
|||It is set up by the SQL Server 2005 WMI Provider for Server Events. You can
find more information here:
http://msdn2.microsoft.com/en-us/library/ms181893.aspx
As David noted in his reply, this should not be a performance hit. RECIEVE
just waits until it either gets a message in it's queue or it times out. The
WMI provider is just launching another RECIEVE when one times out.
Alan Brewer [MSFT]
SQL Server Documentation Team
Download the latest Books Online update:
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
This posting is provided "AS IS" with no warranties, and confers no rights.
|||On Feb 1, 12:53 pm, "Alan Brewer [MSFT]" <ala...@.microsoft.com> wrote:
> It is set up by the SQL Server 2005 WMI Provider for Server Events. You can
> find more information here:
> http://msdn2.microsoft.com/en-us/library/ms181893.aspx
> As David noted in his reply, this should not be a performance hit. RECIEVE
> just waits until it either gets a message in it's queue or it times out. The
> WMI provider is just launching another RECIEVE when one times out.
> --
> Alan Brewer [MSFT]
> SQL Server Documentation Team
> Download the latest Books Online update:http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
> This posting is provided "AS IS" with no warranties, and confers no rights.
Thanks very much for your information. Turns out it was one of the
clients causing the load, i'd just missed it on the first sweep, these
messages were just new/repeating, so looked suspicious
Thanks heaps!

Constant WAITFOR(RECEIVE) in Profile Trace?

After seeing abnormally high load on the sql server this morning, i've
been investigating. After ruling out the usual sources, i ran a trace
on the DB, limited to showing only queries taking longer than 2000
miliseconds, only to find a constant stream of:
WAITFOR (RECEIVE message_body FROM WMIEventProviderNotificationQueue),
TIMEOUT 5000
it stops ~5000ms, then another one occurs!
Is this causing my load? I've had no luck googling for that message,
so not sure how common it seems to be .. Only thing I can think of is
last week we set up database mail, is this related? The field
"message_body" seems to indicate perhaps it is related to this?
Is it normal? or is this my CPU load problem?
How do I fix it? Thanks in advance!<vidguide@.gmail.com> wrote in message
news:1170282805.989902.152780@.j27g2000cwj.googlegroups.com...
> After seeing abnormally high load on the sql server this morning, i've
> been investigating. After ruling out the usual sources, i ran a trace
> on the DB, limited to showing only queries taking longer than 2000
> miliseconds, only to find a constant stream of:
> WAITFOR (RECEIVE message_body FROM WMIEventProviderNotificationQueue),
> TIMEOUT 5000
> it stops ~5000ms, then another one occurs!
> Is this causing my load? I've had no luck googling for that message,
> so not sure how common it seems to be .. Only thing I can think of is
> last week we set up database mail, is this related? The field
> "message_body" seems to indicate perhaps it is related to this?
> Is it normal? or is this my CPU load problem?
> How do I fix it? Thanks in advance!
>
I am not sure exactly what process is causing this, but shouldn't be a
performance problem. This is a normal usage pattern for a service broker
application reading a queue.
The WAITFOR RECEIVE is the command to perform a blocking read on a queue.
It consumes no resources while waiting, times out after 5000ms and
immediately starts another blocking read.
David|||It is set up by the SQL Server 2005 WMI Provider for Server Events. You can
find more information here:
http://msdn2.microsoft.com/en-us/library/ms181893.aspx
As David noted in his reply, this should not be a performance hit. RECIEVE
just waits until it either gets a message in it's queue or it times out. The
WMI provider is just launching another RECIEVE when one times out.
Alan Brewer [MSFT]
SQL Server Documentation Team
Download the latest Books Online update:
http://www.microsoft.com/technet/pr...oads/books.mspx
This posting is provided "AS IS" with no warranties, and confers no rights.|||On Feb 1, 12:53 pm, "Alan Brewer [MSFT]" <ala...@.microsoft.com> wrote:[vbcol=seagreen]
> It is set up by the SQL Server 2005 WMI Provider for Server Events. You ca
n
> find more information here:
> http://msdn2.microsoft.com/en-us/library/ms181893.aspx
> As David noted in his reply, this should not be a performance hit. RECIEVE
> just waits until it either gets a message in it's queue or it times out. T
he
> WMI provider is just launching another RECIEVE when one times out.
> --
> Alan Brewer [MSFT]
> SQL Server Documentation Team
> Download the latest Books Online update:http://www.microsoft.com/technet/pr...nloads/books...
> This posting is provided "AS IS" with no warranties, and confers no rights.[/vbcol
]
Thanks very much for your information. Turns out it was one of the
clients causing the load, i'd just missed it on the first sweep, these
messages were just new/repeating, so looked suspicious
Thanks heaps!

Constant WAITFOR(RECEIVE) in Profile Trace?

After seeing abnormally high load on the sql server this morning, i've
been investigating. After ruling out the usual sources, i ran a trace
on the DB, limited to showing only queries taking longer than 2000
miliseconds, only to find a constant stream of:
WAITFOR (RECEIVE message_body FROM WMIEventProviderNotificationQueue),
TIMEOUT 5000
it stops ~5000ms, then another one occurs!
Is this causing my load? I've had no luck googling for that message,
so not sure how common it seems to be .. Only thing I can think of is
last week we set up database mail, is this related? The field
"message_body" seems to indicate perhaps it is related to this?
Is it normal? or is this my CPU load problem?
How do I fix it? Thanks in advance!<vidguide@.gmail.com> wrote in message
news:1170282805.989902.152780@.j27g2000cwj.googlegroups.com...
> After seeing abnormally high load on the sql server this morning, i've
> been investigating. After ruling out the usual sources, i ran a trace
> on the DB, limited to showing only queries taking longer than 2000
> miliseconds, only to find a constant stream of:
> WAITFOR (RECEIVE message_body FROM WMIEventProviderNotificationQueue),
> TIMEOUT 5000
> it stops ~5000ms, then another one occurs!
> Is this causing my load? I've had no luck googling for that message,
> so not sure how common it seems to be .. Only thing I can think of is
> last week we set up database mail, is this related? The field
> "message_body" seems to indicate perhaps it is related to this?
> Is it normal? or is this my CPU load problem?
> How do I fix it? Thanks in advance!
>
I am not sure exactly what process is causing this, but shouldn't be a
performance problem. This is a normal usage pattern for a service broker
application reading a queue.
The WAITFOR RECEIVE is the command to perform a blocking read on a queue.
It consumes no resources while waiting, times out after 5000ms and
immediately starts another blocking read.
David|||It is set up by the SQL Server 2005 WMI Provider for Server Events. You can
find more information here:
http://msdn2.microsoft.com/en-us/library/ms181893.aspx
As David noted in his reply, this should not be a performance hit. RECIEVE
just waits until it either gets a message in it's queue or it times out. The
WMI provider is just launching another RECIEVE when one times out.
--
Alan Brewer [MSFT]
SQL Server Documentation Team
Download the latest Books Online update:
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
This posting is provided "AS IS" with no warranties, and confers no rights.|||On Feb 1, 12:53 pm, "Alan Brewer [MSFT]" <ala...@.microsoft.com> wrote:
> It is set up by the SQL Server 2005 WMI Provider for Server Events. You can
> find more information here:
> http://msdn2.microsoft.com/en-us/library/ms181893.aspx
> As David noted in his reply, this should not be a performance hit. RECIEVE
> just waits until it either gets a message in it's queue or it times out. The
> WMI provider is just launching another RECIEVE when one times out.
> --
> Alan Brewer [MSFT]
> SQL Server Documentation Team
> Download the latest Books Online update:http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
> This posting is provided "AS IS" with no warranties, and confers no rights.
Thanks very much for your information. Turns out it was one of the
clients causing the load, i'd just missed it on the first sweep, these
messages were just new/repeating, so looked suspicious :)
Thanks heaps!