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
ADVERTISEMENT
Apr 25, 2007
In using SSIS to migrate data from mainframe to SQL 2005, I had a situation where only group level data was exposed through the ODBC to SSIS, so I pulled this information as varchar on the SQL destination side. Now I would like to break that group into the individual numeric columns I need on SQL Server. However, the positive and negative sign did not convert because it came of character. I can write something to convert the positive signs to positive numbers; however I cannot do the negative because I would need get rid of the leading zeros in order to place the negative sign before the number. Is there anything I could have done to get SSIS to do the conversion like it did for every one-to-one mapping?
View 9 Replies
View Related
Jul 2, 2014
We're getting
Msg 1013, Level 16, State 1, Line 1
The objects "MYTEST2.TEST" and "mytest.TEST" in the FROM clause have the same exposed names. Use correlation names to distinguish them.use of fully qualified names is allowed without having to alias them.One workaround is changing compatibility mode to 80.Is there another (startup flag?).Reason for no alias: MS Reportbuilder doesn't provide them when building queries
CREATE SCHEMA MYTEST;
go
CREATE SCHEMA MYTEST2;
go
CREATE TABLE MYTEST.TEST
[code]....
View 2 Replies
View Related
Jul 20, 2005
Im trying to find the error in this statement:CREATE PROCEDURE STP_selectmainASselect a.inventoryid, b.firstname, b.lastname, art.title, art.medium,a.cost, a.inventoryid, a.receivedate, a.dimensions,a.reference, art.provenance, sum(c.restorationcost),sum(d.framingcost), sum(e.cost)from art as a left outer join artist as b on a.artistid =b.artistid,a left outer join restoration as c on a.inventoryid =c.inventoryid,a left outer join outframing as d on a.inventoryid =d.inventoryid,a left outer join basiccosts as e on a.inventoryid =e.inventoryidgroup by a.inventoryid, b.firstname, b.lastname, a.title, a.medium,a.cost, a.inventoryid, a.receivedate, a.dimensions, a.reference,a.provenanceorder by a.inventoryid descGOeveytime I do a syntax check on it I get this error.error 1012: the correlation name 'a' has the same exposed name astable 'a'.Whats the syntax to fix this?thanks-Jim
View 1 Replies
View Related
May 2, 2014
I've been trying to access the file table shares exposed on a server (I can select, delete, update, etc via SQL) via untransacted access with no luck (the share just sits there doing something when I try to access it, no error message of any kind).
So I thought I'd look to see if there were any open file handles via
SELECT * FROM sys.dm_filestream_non_transacted_handles;
I found there were 10 non transacted handles open, and they've been open for 3 days for automated processes and the files are about 1-2Kb in size.
so I tried to kill them via
exec sp_kill_filestream_non_transacted_handles
that's normally not something that takes very long (in fact when I cleared two for a different database it took just a second or two).
So leaving the stored proc running, I figured I'd connect and fire up Adam Mechanic's sp_whoisactive.
I see the processes to kill the open file handles, with a wait state of FFT_NSO_FILEOBJECT
View 2 Replies
View Related
Jun 17, 2015
I'm working in VS 2010 for SSIS 2012. I have a project whose deployment is done via project deployment to SSISDB. I've got a local flatfile connection manager within one of its packages. When I deploy I'm unable to override the file name property in either package configuration or job step configuration on the SQL Server. If the flatfile connection were at the project level, I could. Not sure of the rationale for this or maybe I'm doing something wrong.
View 2 Replies
View Related
Jul 23, 2005
Hi all!In a insert-trigger I have two joins on the table named inserted.Obviously this construction gives a name collition beetween the twojoins (since both joins starts from the same table)Ofcourse I thougt the usingbla JOIN bla ON bla bla bla AS a_different_name would work, but itdoes not. Is there a nice solution to this problem?Any help appriciated
View 1 Replies
View Related
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
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
View Related
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
Aug 18, 2005
Does anyone know if the SSIS can integrate with Notification Services? In which ways? Thanks!
View 6 Replies
View Related
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
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
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
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
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
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
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
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
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
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
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 "Set ? = (select max(URC_ID) AS URC_ID FROM
VIEW_A1021_URC
WHERE SerialNumber = ?)
" failed with the following error: "Syntax error, permission violation, or
other nonspecific error". Possible failure reasons: Problems with the query,
"ResultSet" 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
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
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
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
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
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
May 25, 2006
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
View 5 Replies
View Related
Aug 17, 2007
I am seeking a walkthrough for executing packages via a web service. All I have found so far are fragmented bits of information. This article is a good start but it leaves out some critical security setting information. http://msdn2.microsoft.com/en-us/library/ms403355.aspx#service.
Does anyone know of a good walkthrough that includes the security setup: Impersonation, Proxies, etc.?
View 6 Replies
View Related
Apr 12, 2008
what is considered best practice for privileges etc on the sql agent service account and long term need for that account to run ssis packages? I tried to understand and appreciate the article at http://www.microsoft.com/technet/prodtechnol/sql/2005/newsqlagent.mspx but felt like either it was overkill or I wasnt getting it.
View 11 Replies
View Related
Sep 17, 2007
Where do I find about running an SSIS package (.dtsx) from a ASP.Net web service, particularly issues to do with permissions
cheers
Rob
View 1 Replies
View Related
Nov 13, 2007
I've lost access to SSIS and BIDS.
I'm a SQL 2005 developer DBA with a 1.5 years experience with SQL Server 2005. It has been a while since I needed to use SSIS. I used it with no problems earlier this year when I was managing the team conversion to SQL Server 2005. Now it no longer appears on my START > Programs > SQL Server 2005 menus. I had to reinstall SQL Server 2005 and Visual Studio 2005 months ago and must have lost it then some how.
How do I get access to BIDS and SSIS again?
SQL Server Integrated Services shows up in my Services list as running.
I've already tried Add/Remove Programs to drop that component from SQL Server 2005, reboot, Add it back, reboot. Still does not show up in my menus and the Service is running. The Setup Bootstraplog summary for this effort shows no failures and shows this:
Product : Microsoft SQL Server 2005 Integration Services
Product Version : 9.00.1399.06
Install : Successful
Log File : C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGFilesSQLSetup0021_xxxx-xxxx_DTS.log
My SQL Server level is 9.0.3054. The About Info is:
Microsoft SQL Server Management Studio 9.00.3042.00
Microsoft Analysis Services Client Tools 2005.090.3042.00
Microsoft Data Access Components (MDAC) 2000.085.1117.00 (xpsp_sp2_rtm.040803-2158)
Microsoft MSXML 2.6 3.0 4.0 5.0 6.0
Microsoft Internet Explorer 7.0.5730.11
Microsoft .NET Framework 2.0.50727.832
Operating System 5.1.2600
My Visual Studio About Info is:
Microsoft Visual Studio 2005
Version 8.0.50727.762 (SP.050727-7600)
Microsoft .NET Framework
Version 2.0.50727
Installed Edition: Professional
Microsoft Visual Basic 2005 77626-009-0096213-41845
Microsoft Visual Basic 2005
Microsoft Visual C# 2005 77626-009-0096213-41845
Microsoft Visual C# 2005
Microsoft Visual C++ 2005 77626-009-0096213-41845
Microsoft Visual C++ 2005
Microsoft Visual J# 2005 77626-009-0096213-41845
Microsoft Visual J# 2005
Microsoft Visual Web Developer 2005 77626-009-0096213-41845
Microsoft Visual Web Developer 2005
Microsoft Web Application Projects 2005 77626-009-0096213-41845
Microsoft Web Application Projects 2005
Version 8.0.50727.762
Crystal Reports AAC60-G0CSA4B-V7000AY
Crystal Reports for Visual Studio 2005
Microsoft Visual Studio 2005 Professional Edition - ENU Service Pack 1 (KB926601)
Security Update for Microsoft Visual Studio 2005 Professional Edition - ENU (KB937061)
Also, in case it is relevant, I've NEVER installed SQL Server Express or had it installed.
When I reinstalled, I installed SQL Server 2005 before VS 2005 since I know that has caused problems with some people.
Please help me get SSIS and BIDS access again.
Thanks,
Paul
View 6 Replies
View Related
Apr 30, 2007
Hey,
I have an SSIS package that invokes a web service and then updates a table. It runs fine as long as I am running it on the local machine. However, as soon as I save this package to the sql server, and try to schedule this as a job, it starts to fail. Now, the web service writes to an xml file and also uses an xsd and and an xsl file. When I save a dts package to the sql server, whats the proper way of referencing these files? I think this probably is what is making the package to fail, ut I am not sure.
Any help is greatly appreciated!!
Thanks!
View 11 Replies
View Related
