Web Service Consumption In SSIS

Sep 12, 2007

Hi,

I have a webservice, which I am using in the Web service Task. The XML response, is fed to the XML source and I want to write the data from this XML to flat file. I am using the below attached schema definition for XML (.XSD). When I use this as XSD in the XML source task, in the column definition I see only three columns and they are

ZoneCode
ZoneName
AggregatedCZ_Id

My question is what is AggregatedCZ_Id and why I do not see the PostCode in the columns? Any help is appreciated.

<?xml version="1.0" encoding="utf-8"?>
<xs: schema elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:import namespace="http://microsoft.com/wsdl/types/" />
<xs:element name="ResponseZoneDetailsReport" nillable="true" type="ResponseZoneDetailsReport" />
<xs:complexType name="ResponseZoneDetailsReport">
<xs: sequence>
<xs:element minOccurs="0" maxOccurs="1" name="PostCode" type="xs: string" />
<xs:element minOccurs="0" maxOccurs="1" name="AggregatedCZ" type="ArrayOfComplianceZoneDetails" />
</xs: sequence>
</xs:complexType>
<xs:complexType name="ArrayOfComplianceZoneDetails">
<xs: sequence>
<xs:element minOccurs="0" maxOccurs="unbounded" name="ComplianceZoneDetails" nillable="true" type="ComplianceZoneDetails" />
</xs: sequence>
</xs:complexType>
<xs:complexType name="ComplianceZoneDetails">
<xs: sequence>

<xs:element minOccurs="0" maxOccurs="1" name="ZoneCode" type="xs: string" />
<xs:element minOccurs="0" maxOccurs="1" name="ZoneName" type="xs: string" />
</xs: sequence>
</xs:complexType>
</xs: schema>

Regards,
Virendra

View 7 Replies


ADVERTISEMENT

SQL Server Memory Consumption With SSIS?

Aug 7, 2006

I've been working on serveral packages for the past hour and after finishing for the night I quickly wanted to check to see how much memory SQL Server was consuming on my laptop. It was using almost 500MB of memory. It typically hovers around 50-100MB when I'm not doing anything with it. Is this normal?

View 3 Replies View Related

SQL-Server Memory Consumption

Mar 9, 2005

Hi,

I've thre problem that our MSDE SQL-Server 2000 (without SP3) memory consumption is constantly increasing. Even though, that I limitet the 'max server memory' with sp_configure and RECONFIGURE it's growing above that limit.

When I shut down the application, SQL server does not free the memory. I was already checking the database file size (transaction logs) and made shure that autoshrinking is enabled.

Does anybody have an idea, why SQL-Server does not release the memory?

Thanks for support, rene

View 1 Replies View Related

How To View Memory Consumption In 64-bit O/S ?

Feb 6, 2008

I know I can run DBCC MEMORYSTATUS to view how much memory SQL Server is using on a 64-bit server, but how do I view how much memory the other processes are consuming?

I do not believe the Perfmon counters are accurate when using AWE or 64-bit memory, at least in terms of memory consumption per process so how do we see this information. I have a feeling it is not possible.

Here is the problem. We have a 64-bit server with 32GB of memory. SQL Server is configured to use no more the 20GB. It currently is using only 15GB. The O/S is reporting 28.3GB used out of 32GB, which means some other processes are consuming 28.3GB - 15GB = 13.3GB. How can I determine what is using the 13.3GB of memory?

Thanks, Dave

View 12 Replies View Related

Ad-hoc Reporting Memory Consumption

Jul 29, 2006

Hi to all, on our project we are facing this problem: our users using report builder are making reports containing several Mb of data (sometimes also 1Gb!). Now it happens that when 3-4 users do a report like that on the server the process w3wp.exe reaches also 10 Gb of memory allocated and then the whole reporting services istance stops working.
We want to limit the number of rows that can be extracted from the sql server db using report builder. In the report model reference seems that there is not this kind of option (something like "max rowcount"). Is it possible to set this limit to the user that connects to sql server (something like: whatever is the query this user does, set @@rowcount to 10000)?
Thanks.
Alberto.

View 2 Replies View Related

How To View Memory Consumption With 64-bit OS ?

Feb 6, 2008

I know I can run DBCC MEMORYSTATUS to view how much memory SQL Server is using on a 64-bit server, but how do I view how much memory the other processes are consuming?


I do not believe the Perfmon counters are accurate when using AWE or 64-bit memory, at least in terms of memory consumption per process so how do we see this information. I have a feeling it is not possible.


Here is the problem. We have a 64-bit server with 32GB of memory. SQL Server is configured to use no more the 20GB. It currently is using only 15GB. The O/S is reporting 28.3GB used out of 32GB, which means some other processes are consuming 28.3GB - 15GB = 13.3GB. How can I determine what is using the 13.3GB of memory?


Thanks, Dave

View 1 Replies View Related

DB Engine :: How To Get Resource Consumption

Sep 29, 2015

I got a task to get resource consumption (CPU, storage etc.) for stored procedures in certain database. I found nice script ([dbo].[usp_Worst_TSQL]) but looks like script written for SQL 2005 and we use SQL 2012.how to get resource consumption details.

View 9 Replies View Related

SQL Server 2000 And Memory Consumption

Sep 14, 2005

Hello!


We have a SQL Server 2000 that has been working nice
without any issues. Lately we noticed the fact that the amount of memory that
it is using has increased and once it took down the web server as the total
amount of memory used was 2G. Due to this fact I have set Memory Max to 500MB.
Now as I look in Task Manager the Memory usage is at 530396k which is 518MB.
Any reason why would it exceed the 500MB?

What we did before was to stop the SQL Server and restart it, and it takes about
2 days until it gets back to +500MB.

What can I do to stop this behavior?

Thanks.

View 3 Replies View Related

Sorting An XML Source &&amp; Memory Consumption.

Jan 23, 2006

Hi there,

I have a large XML document (100Mg) that I want to process through a data
flow process.

Part of the process is to Sort the various relational tables created by the XML Source - then merge join them.

The Sorts consume all the available memory and the process grinds to a halt.

However, if I source the same data from SQL tables, the sorts don't consume anywhere near as much memory.

So a couple of questions:
1. Is there a difference between the way SSIS sorts XML Source Data and the way it sorts OLE DB Source Data?

2. Are there any techniques/best practices when dealing with large XML Files?

Thanks,
Dave.

View 4 Replies View Related

Linked Server Memory Consumption

Oct 31, 2007

Hi all,

I have two database server, named it DB_A and DB_B. I want to establish a linked server of DB_A in my DB_B. There's a script in DB_B that queries data from DB_A but only execute periodically. Does linked server consume large memory or CPU when it didn't use ?

Thanks in advance,

Hery

View 4 Replies View Related

SQL 2005 Memory Consumption Report

Aug 21, 2006

When Running the standard Memory Consumption Report in the section Buffer Pages Distribution (# Pages) i get the following error

Unable to retrieve data for this section of the report. Following error occurred. Msg 8114, Level 16, State 1 Error converting data type bigint to int.

Any Ideas

View 1 Replies View Related

Limiting User Resource Consumption At The Instance Level

Oct 9, 2007

We are planning a shared SQL Server 2005 enviroment where users can create databases/applications for themselves and/or departments. With the consideration that there can be multiple SQL Servers on a box, can each instance limit a user's disk space? Thanks for any enlightenment.

View 1 Replies View Related

Passing A Parameter For Consumption Of IN Keyword In A Stored Procedure

Nov 14, 2007



Hi all, what's the recommended way of passing a parameter than can be consumed properly by a SQL statement using the IN keyword.

For Example:

proc Test @Param varchar(200)
as
begin

SELECT FieldA, FieldB
FROM TABLE
WHERE FieldA IN (@Param)

end



An option is making the query into a string
strSQL = "SELECT FieldA, FieldB FROM TABLE WHERE FieldA IN (" + @Param ")"

then executing the string, but I was wondering if there is a more effective way?

Thanks

View 5 Replies View Related

Can't Find SSIS Service

Feb 26, 2007

SQL 2005 running on 2003 Server:

When trying to connect to Integration Services from Management Studio, I retrieve the following error:

***
TITLE: Connect to Server
------------------------------

Cannot connect to DAVINCI.

------------------------------
ADDITIONAL INFORMATION:

Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476

------------------------------

Connect to SSIS Service on machine "DAVINCI" failed:
The specified service does not exist as an installed service.
***

I see a ton of people having the same problem, but as for now I still haven't found a solution.

We have SP1 installed and have installed cumulative fix from http://support.microsoft.com/kb/918644, and been by the DCOM settings regarding local/remote access.

The SSIS service is not running. It is not shown in the list of services.

Any ideas before a total re-install?

Regards /Snedker

View 3 Replies View Related

SSIS Service Won't Start

Jan 3, 2006

I've been completely unsuccessful so far in getting the SSIS service to start under normal circumstances.



I've got the SQL 2005 RTM DVD and a clean, brand-new installation of
Windows 2000 Server (Service Pack 4). All system requirements have been
met according to the SQL installer. I choose to install just the
Database Engine, SSIS and the Workstation components, all other
installation options are defaults except Services, which are set to use
a Domain User account.



The install completes without problems and the SQL Server & SS
Agent services start suceessfully, but the SSIS service refuses to
start (timeout). If I change the service to use LocalSystem, the same
happens. If I make the Domain User (DOMAINMisterSQL) account a member
of Local Admins on the server, the service still won't start.



If, however, I use a Domain Admin account, the service does start, so it appears to be a security issue, but how can I configure the service to start with fewer privileges?



This problem has appeared on each machine I've tried this on, four to
date, so it's not related to the particular setup of this server. I've
searched the web for weeks for a solution with no success; any help
would be greatly appreciated.

View 10 Replies View Related

SSIS And Notification Service

Aug 18, 2005

Does anyone know if the SSIS can integrate with Notification Services? In which ways? Thanks!

View 6 Replies View Related

SSIS Service Account

May 12, 2006

Hi All,

I understand Sql Server Integration Services by default uses"NT AuthorityNetwork Service" account as service account. Is running SSIS using "NT AuthorityNetwork Service" account is good or should we create a domain account to run the SSIS service.

Regards, Balaji Thiruvenkataraju.

View 3 Replies View Related

SSIS And Service Broker

Feb 12, 2007

I am trying to use SSI Sto receive messages from a Service Broker Queue and continue to have difficulty doing so.

I have come to the point where I need to get the messages that share a conversation with this BatchEnd message type before I get any other messages, as this message type tells me information about how many others there are.

I built this script and ran it in management studio with success and felt it should work for me running as the sql for an OLE DB source.

declare @ch uniqueidentifier;

select @ch = conversation_handle

from dm.[consultant queue]

where message_type_name = 'BatchEnd';

Receive conversation_handle, message_type_name, message_body

from dm.[consultant queue]

where conversation_handle = @ch;

But SSIS constantly complains about it. I finally profiled SSIS to catch the sql that it is executing. It is below and this fails with an error about a missing conversation_handle.


declare @p1 int

exec sp_prepare @p1 output,NULL,N'declare @ch uniqueidentifier;

select @ch = conversation_handle

from dm.[consultant queue]

where message_type_name = ''BatchEnd'';

Receive conversation_handle, message_type_name, message_body

from dm.[consultant queue]

where conversation_handle = @ch;',1

select @p1

go



Now I'm not sure which team may own this, but shouldn't this sp_prepare work. If I change this to use a table variable then the prepare seems to work just fine, but then I never actually get any data back into SSIS. It is almost like it calls it twice; although, I could not confirm that with profiler.

I'll cross post this in the Service Broker forum too.

Thanks!

View 1 Replies View Related

Service Broker And SSIS Together ?

Aug 15, 2007

Hello,

I want to use Service Broker and SSIS in a integration project where the queue contains Xml data and the SSIS package processes and sends it to a destination. What is a good way to do this? Is it possible for the queue to execute the package with the data as a parameter, or what is good practise when combining this techniques? I am not familiar with Service Broker so i don´t know what you can do with it when data are loaded in it.

Would be grateful for some hints to put me in a good direction..

/Erik B

View 1 Replies View Related

Service Broker From SSIS

Jun 5, 2007

Hello,

I want to use Service Broker to drop some SQL from a loop in SSIS task into a Service Broker queue. Can anyone point me in the right direction, examples, samples?


I have an ExecuteSQL task which returns a set of tablenames, which will be iterated to, and I want to send an EXEC mySP 'myTable' to Service Broker, to asynchronously execute. I have created a queue and a service, but I'm not sure what the next step is to join the two together.

any help, or links will be gratefully received!

Many thanks

View 1 Replies View Related

SSIS Exposed As A Web Service

Mar 22, 2007

I've seen examples of creating a Web Service that executes an SSIS package through code. But that involves developing the web service, configuring IIS on the same server as SSIS to expose the web service. I was wondering if it is easy to use the HTTP endpoints within SQL Server 2005 to create a web service interface that calls an SSIS package, supplying any parameters the package needs to run. Also, this would be called from within Excel VBA that uses a web-reference via the Web Reference Toolkit.

Thanks

Kory

View 3 Replies View Related

SSIS Web Service Task

Apr 6, 2007

I have been struggling with a problem with the Web Service Task. I have a package that uses this and has to authenticate to the web service. This works fine on my development server but if I export the package to file and run from another computer / user, the package fails as it doesn't log in to the web service. I have found the problem to be the package was set to default of EncryptSensitiveWithUserKey and I understand why this is. If I change to use EncryptSensitiveWithPassword, it prompts for creds and works fine. My question is how do I use a package configuration file to use different creds (there is not password option for the http connection) so I can standardize on use of config files for this operation. Thanks.



Cale

View 5 Replies View Related

SSIS Service Not Available On A/P Failover Server

Dec 17, 2007

Greetings,
Ever since our sql2005 A/P failover cluster failed over to the 'B-passive node', I have a failing SSIS scheduled job with a 'missing component' error. While verifying services in computer management and sql surface area config, It appears that the SSIS service is not available because its not listed therefore cant be started. I have examined both nodes and the primary "A-active node' shows the SSIS service but the passive node unlisted. The server has failed over before during normal scheduled updates and sevice pack installs however this is the first occation we have not failed back to the primary node for normal operation. Any ideas or clues would be appreciated.

View 6 Replies View Related

Validation Of SSIS Service Upon Startup

Mar 7, 2007

In the SP1 update of SSIS there was a call from the SSIS service that would attempt to validate itself against the http://crl.microsoft.com web site. If the SSIS service didn't have access to the internet then it would timeout and not start the service. In our company we have our development systems behind a firewall which only allows for limited internet access due to it being a development system.

Does the installation of SP2 remove this authentication restriction?

...cordell...



Problem Description Reference Documents:

http://support.microsoft.com/default.aspx/kb/918644

http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=124887.

View 1 Replies View Related

Thoughts On Whether The SSIS (service/agent) Is Really Necessary?

Jul 12, 2007

I just learned I can deploy and schedule jobs to run SSIS packages (via job/sqlagent) without the Integration Service (agent) itself actually running alongside (or on) the server. (Double-click on manifest, deploy IS package to server, create job/job step to run IS package, watch it run even when integration service is completely disabled)



Other than convenient viewing, configuring, and RMC running w/in SQL Server Mgmt Studio 2005, why then do I need the integration service running on a production box at all? When do I really need the IS service itself?



In our (finance) world only (a) an act of God or (b) a DBA can touch production databases/servers. Allowing anyone to connect to yet another service - in this case, an integration service - to meddle w/ a package would be a no no, so...



1) Could I trouble someone for a concrete, critical reason why the DBA should enable it on a production server. Speed? Caching? Peace of mind knowing everything is piled onto and neatly running on the server?



2) On a more minor note, if I'm deploying a package to be housed solely w/in MSDB, is there anyway to prevent the prompt of a file location during deployment, i.e. the creation of an empty directory that would otherwise hold package dependencies if I were running it as a file?



We'd like to deploy only to MSDB (I know all the pros/cons w/r/t saving dtsxs to files v. msdb) and keep deployments clean (read: all in one place). DR is via SAN-to-SAN replication with, among other things, msdb cleanly getting replicated. We would very much like to avoid having to worry about (more) file/directories sitting out on a server share to be replicated to DR (it seems the default is to allow deployments to directories on the SQL server instance itself..ugh) Any architectural insights on this would be appreciated.



Kind Regards,

Jim





View 3 Replies View Related

Using Ssis Task To Call A Web Service

Jan 11, 2007

Hi,

I m trying to use the web services task, but when i try to run it i got the following error

[Web Service Task] Error: An error occurred with the following error message: "Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebserviceTaskException: Could not execute the Web method. The error is: Object reference not set to an instance of an object..    at Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebMethodInvokerProxy.InvokeMethod(DTSWebMethodInfo methodInfo, String serviceName, Object connection)    at Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebServiceTaskUtil.Invoke(DTSWebMethodInfo methodInfo, String serviceName, Object connection, VariableDispenser taskVariableDispenser)    at Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebServiceTask.executeThread()".

This is probably because i never worked with this task, even so, does anyone knows what might be wrong?

I got a connection a wsdl, i can configure the service, method and variables(i use fixed values), and i configure the output to a variable with the type object(i have tryed string and int)

I even can download the wsdl file, but the error seems to be in the connection

Thanks

 

View 7 Replies View Related

SSIS And SQL Server Service Pack 2 X64

Jun 26, 2007

I have recently installed SQL Server SP2 on a Windows 2003 x64 server SP 1 and now I cannot open existing packages on that machine. The packages work fine on other machines. Also, when I create a new package and then attempt to add a data flow component, I get an error that the designer could not be initialized. It does add the component, but when I try to delete it, I get an error that the collection cannot be null (parameter: c). I have read some posts that point to registry permssions, and have ran the sample c# code, but I do not see any problems. Anyone have a suggestion?

View 5 Replies View Related

Web Service Will Not Completely Run SSIS Package

Oct 20, 2006

Hi,

After many days of searching i have eventually been able to set up a web service to call and execute my SSIS package. The SSIS package is set up completely with windows authentication,the web service is run from an application using the same credentials with all necessary access for the SSIS package. I can run the package from the SQL management studio and it works fine, however when i attempt to run it from the web service it appears to work once round the for each loop and then fails for every other time. The error i am getting is as follows:


Executing the query &quot;Set ? = (select max(URC_ID) AS URC_ID FROM


VIEW_A1021_URC


WHERE SerialNumber = ?)


&quot; failed with the following error: &quot;Syntax error, permission violation, or


other nonspecific error&quot;. Possible failure reasons: Problems with the query,


&quot;ResultSet&quot; property not set correctly, parameters not set correctly, or


connection not established correctly.


This error doesn't happen on the first iteration, as the package does exactly what it is supposed to do.

Has anyone encounter anything like this before? any help on this matter would be great.

Many thanks in advance,

Grant

View 1 Replies View Related

Executing A SSIS-Package From Web Service.

Oct 10, 2007

I have a SSIS Package that I am executing from a Web service. The SSIS Package is stored as a file. That works fine. In the SSIS package I have one ADO.NET, .Net ProvidersSqlClient Connection. For the Protection Level I have EncryptSensitiveWithUserKey.

My problem comes when I change the Protection Level to EncryptSensitiveWithPassword. Now the SSIS package is throwing an error when trying to connect to the sql database: Login failed for user _ _

I believe my problem is that I don€™t know how to pass the password of the SSIS package from the Web service into the SSIS package.

Below is part of the code for the Web service:


<WebMethod()> _
Public Function LaunchSSISPackage( _
ByVal variableName As String, _
ByVal variableValue As Object) As Integer 'DTSExecResult
Dim packagePath As String
Dim myPackage As Package
Dim integrationServices As New Application
Dim SSISvar As Variable

myPackage = integrationServices.LoadPackage(packagePath, Nothing)
SSISvar = myPackage.Variables.Add(variableName, False, "User", variableValue)
LaunchSSISPackage = myPackage.Execute()


I appreciate your help with this issue I am having. Thank You.

View 4 Replies View Related

Reporting Service Report And SSIS

May 1, 2008




How can I run a RS report in SSIS ? I have deployed a report to a server.
and I would like to run this report after a certain control flow task in SSIS ..and put the report to a network folder.. and schedule this SSIS package as a sql job.

I don;t see any tasks that can run a RS report in SSIS... how can i do this?

View 6 Replies View Related

SSIS Script For Windows Service Monitoring

May 24, 2006

Hi

I would like to use SSIS Script for Monitoring Windows Service...

If Windows Service Up / Down. I should be able to Notified by

Using SSIS Script(SQL SERVER 2005).

Please Help me

Thanks & Regards
Deepu M.I

View 1 Replies View Related

Named Instance: SSIS Service Won't Start

Jan 15, 2008

Dear Experts,

I am running a named instance of SQL-Server 2005 SP2 on Win2003 with two local partitions C: and F:
The TCP settings for this instance have ecplicitly been set to use the port 1433.

The config file for SSIS (F:ProgrammeMicrosoft SQL Server90DTSBinnMsDtsSrvr.ini.xml) has been changed to:

<?xml version="1.0" encoding="utf-8"?>
<DtsServiceConfiguration xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<StopExecutingPackagesOnShutdown>true</StopExecutingPackagesOnShutdown>
<TopLevelFolders>
<Folder xsi:type="SqlServerFolder">
<Name>MSDB</Name>
<ServerName>.INSTNAME,1433</ServerName>
</Folder>
<Folder xsi:type="FileSystemFolder">
<Name>File System</Name>
<StorePath>..Packages</StorePath>
</Folder>
</TopLevelFolders>
</DtsServiceConfiguration>

I have tried different logons (NetworkService, LocalSystem, DomainAdmin).

The service still refuses to start. There is no detailed message in the event log just that the service didn't respond in a reasonable time, doh!

Any help is welcome
Fridtjof

View 1 Replies View Related

Execute SSIS Package Through ASP.NET Web Service - Different Problem

Aug 22, 2007

Hi all,

There was already several posts about executing packages programmatically from the context of Web Services. I have decided to follow Web Service approach because we were already calling packages programmatically through .NET. So far I have been able to clear all security configuration obstacles as I am able to load package and call Execute but mysteriously package doesn€™t execute. I am reading StopTime, StartTime and ExecutionDuration properties after call to Execute and evidently package doesn€™t execute because StartTime and EndTime are identical and ExecutionDuration is 0 but no error is raised or logged anywhere but package return status is failure)!

We are using SQL Server 2005 with Service Pack 2 on Windows 2003 R2 Enterprise, .NET Framework 2.0. The Domain Account that has Admin privileges on both servers is calling Web Service. DefaultAppPool uses Network Service and Anonymous access is enabled. Network Service is also member of the sysadmin role.

I am sure root of the problem is still in the Security but I am not getting any clue because of lack of any error. Does anyone has any suggestion? Thanks in advance.

Sinisa Catic

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved