Calling A Custom Web Service From My Package To Perform Heavy-duty Tasks
Mar 20, 2007
As part of my SSIS package, a list of sites is created that need to be created on a remote machine. let's say 1000 sites. I need to pass this list to a web service so web service sitting on that machine creates these sites for me. MY SSIS package does not run frequently so I can sacrifice time a little bit to get better functionality.
I need to move the sites that are not created (for any reasons) by web service to another table and successfully created sites to another table, so I need to get confirmation for each site from the web service.
Which option is better?
1) Calling web service for every single record (site) and get the confirmation and then based on the confirmation I move the records accordingly. I know this might be very time consuming, but as I said my SSIS package might only run every six months
OR
2) Sending records to a web service in a batch and get the result. I don€™t know how to do this though.
Any help or better solution is appreciated.
View 3 Replies
ADVERTISEMENT
Feb 26, 2007
Posted the same message to the Service Broker forum. But the board is neither responsive nor active. So I am trying this forum. Hope someone can help.
I would like to call an SSIS package from a Service Broker Queue.
There is one way that I am aware of -
Using xp_cmdshell from within an activation stored procedure and using DTEXEC.
Is there a more elegant way of executing an SSIS package from within SSB?
Also,
I am not interested in writing a .NET external activator to process my
messages in the queue. I would like this operation to be strictly
database oriented. Having said this, I am also trying to avoid triggers
processing the messages in the queue.
Thank you!
View 5 Replies
View Related
Nov 15, 2006
I am trying to call a SSIS package from a web service hosted on the same machine as the package file is sitting. All that the package does is a simple Execute SQL task with one datasource connection.
I have set impersonation as true. When I run the package from the web service on the virual port (through Visual studio IDE) it runs fine.
http://localhost:4609/WebServiceRunSSIS/LaunchSSISPackageServiceCS.asmx
In the above case I don't think impersonation means anything coz it runs successfully even when there is no impersonation set!
I then run the same service (asmx page) from the browser after making it a web service on my machine
http://localhost/WebServiceRunSSIS/LaunchSSISPackageServiceCS.asmx
I get an error indicating package failure.
I have enabled logging in SSIS. The package log indicates the following:
Failed to acquire connection "APNetDS". Connection may not be configured correctly or you may not have the right permissions on this connection.
Effectively, my web service impersonated account (which is my admin account) is not being authenticated for any of the db connection (uses windows integrated authentication) and I find that odd. To impersonate, I have set the Impersonation to true in the web.config file. The authentication mode is windows.
Please tell me what bit of security I am missing? In case if it helps, the database connection is to my local database and hence should not deny access to my own account. And yes, I think my impoersonation is working because when I debug, the user.identity shows my user id.
Thank you,
Sumeet
View 9 Replies
View Related
Sep 12, 2007
When running an integration services package from a windows service I get the "Object ... has been disconnected or does not exist at the server." exception after aproximately six minutes of execution.
This is *not* my windows service failing. I can loop indefinately while tracing to a log file within the service and it will run forever. While calling the mypackage.execute(...) method however, after six minutes (give or take) the exception is thrown...
my code looks something like this:
<code>
dim foo as Microsoft.SqlServer.Dts.Runtime.Application
mypackage = foo..LoadPackage(strimportPkgFilename, pkgevents)
results = myPackage.Execute(Nothing, Nothing, pkgevents, Nothing, Nothing)
</code>
<error>
A first chance exception of type 'System.Runtime.Remoting.RemotingException' occurred in mscorlib.dll
Exception in: frmMyForm.DoImports
Message: Object '/b76f98a0_5bd9_49d8_a524_eeb49d55b303/bqbhkjnaofq_ifr_cwz+srid_1.rem' has been disconnected or does not exist at the server.
</error>
oddly, this same code works perfectly if I run it within a windows form application no matter how long it takes.
It also runs fine if the package can complete in under six minutes.
Any suggestions?
Mark
View 2 Replies
View Related
Oct 5, 2000
Would anyone be aware of anyplace I could find some good information on creating DTS custom tasks? I've come across a couple of articles from SQL Server Magazine, but nothing too substantive...
Better yet, if anyone has any success (great or small), I'd like to hear from you and hear some of the things you did, what your custom task does, difficulty.
I'm just trying to get an idea of how much work I have ahead of me....
Thank You...
View 2 Replies
View Related
Dec 4, 2007
I have developed a custom task where I have a lot of properties on. Then I realized that I was missing some properties and added them to my task.
The problem is that my previous version of the task does not "upgrade" properly resulting in me needing to type in all the property values once again. I have severel task that need to upgrade but I can't figure out how to use the functions: Update, CanUpdate and Version
Any suggestion - maybe a working example?
Help much appreciated!
View 8 Replies
View Related
Dec 6, 2007
Hi everyone!
How do I create my own descriptions as shown in the buttom of the Properties pane in Visual Studio 2005 when I create a Custom Task?
Thanks in advance!
View 6 Replies
View Related
Mar 13, 2006
Hello,
I had an idea that it would be nice to be able to extend the functionality of an existing SSIS component or task by inheriting from it. Perhaps in a similar way to how it is possible to extend user controls in .Net.
e.g. The rowcount is a useful component but it would be good to create a new component that inherits from it and then override the PostExecute() method to fire an Information event containing the number of rows. That's a very simple example but I think you get the gist.
Does anyone think that would be useful? Or even made possible?
I was also wondering whether it would be possible for the SSI team to make all the icons used within SSIS available as .ico files so that we could modify them for tasks/components that might do similar things.
Thanks
-Jamie
[Microsoft follow-up]
View 1 Replies
View Related
Jul 31, 2006
All aspects of my custom task work well except for the icon!
I have included the .ico in the assembly as an "Embedded Resource" and the icon has both 16X16 and 32X32 images. I have edited the IconResource property of the DtsTask attribute as follows:
IconResource="MyNamespace.MyDLLName.MyIconName.ico",
Where the output assembly is called "MyNamespace.MyDLLName.dll".
Has anybody got this working before? The Icon just never shows (I get the default blue square task icon).
Thanks in advance.
View 7 Replies
View Related
Apr 3, 2008
I can't find information on how you are suppose to handle the TransactionOption setting inside of a custom task.
I have a custom task that I have developed, and it basically calls a COM+ object that writes data to a database. When I have the task inside of a container that has the transactionoption set to required, and my custom task is set to supported, if one of the X items fail to execute in my custom task I am telling my task to fail the parent, which I thought would rollback everything. But it does not.
Is there someplace that I need to write rollback code in custom SSIS tasks? If there is I can't find any mention of it anywhere. Any examples out there on how to build custom SSIS tasks that support the TransactionOption parameter?
Thanks!
Matt
View 4 Replies
View Related
Oct 22, 2007
Ive been using SSIS for a month or two and now find I need to create some custom tasks to perform some performance logging. in the the overloaded ProviderComponentProperties section I am trying to create a property which has the same look as the Expressions properties you find elsewhere (Little + on the left and a group of sub properties when expanded).
Ive have played with creating a IDTSCustomPropertyCollection90 collection then adding my sub properties to it but I cant seem to then add my new collection to the ComponentMetaData.CustomPropertiesCollection.
Im assuming the Expressions parameter is a collection added to the properties collection but I cant figure out how. Any help would be much appreciated.
View 4 Replies
View Related
May 28, 2008
Whenever I make a breaking change to a custom SSIS component/tasks and update the Assembly Version, it seems to break my packages beyond repair, telling me it can't load the task:
Error loading Package1.dtsx: Error loading a task. The contact information for the task is "". This happens when loading a task fails.
All of the properties of said task now show:
Could not get value for property 'c-155-designer-name'. Specified cast is not valid.
Typically, a "breaking" change when it comes to code just means that you need to update your components to adhere to the new contract of the updated signatures. But with SSIS, it seems the only solution to this is to completely remove the component, and re-add the new version, and re-enter all of the property values/expressions. If I have a package containing 10 instances of a task that only had one property removed, for example, this results in a very time-consuming process of fixing my package.
So my questions:
1) Am I doing something wrong in my versioning/deployment that is causing my packages to unnecessarily break?
2) If this is just "by design" and the way it's meant to behave, what is the best practice for making breaking changes to custom tasks/components used by many packages? Should I just never change the assembly version, even when it is a breaking change (this seems to be less disastrous)?
3) As a last resort, if I'm stuck with having to fix the broken tasks, is there a better way to fix them rather than having to completely remove them, re-add them, and re-set all of their properties/expressions?
Thanks in advance.
Jerad
View 3 Replies
View Related
Apr 13, 2000
has anyone built a t-sql script that performs the same as a dts import package. I have done every thing for creating the table for the data to go into and just need to know of any procedures to create the connection to the access db to import from.
Dana
View 3 Replies
View Related
May 16, 2008
Hi,
How do I retrieve the connections (connection managers) collections from Custom Data Flow destination? ComponentMetadata.RuntimeConnectionCollection is empty. I would like to be able to access all the connections defined in the package from the custom data flow task.
I came across code in which it was possible to access the Connections collection using the IDtsConnectionService for custom task (destination). The custom task has access to serviceProvider, whcih can be used to get access to the IDtsConnectionService interface but not the custom data flow task.
Any help appreciated.
Thanks
Naveen
View 5 Replies
View Related
Jul 20, 2005
Hi,Did anyone successfully set up a local package to first ftp a db.bakand second perform an automated db restore?I need to perform an automated task, which ftp nightly backup file toanother server and then restore onto a database and leave the databasein read-only mode for additional transaction logs restore during theday.Can someone help and provide the procedures on how to do that?Thanks in advance.
View 1 Replies
View Related
Jun 29, 2006
HiWe have need for an SSIS package that would perform routine purging of the growing data in some of the tables used to support notification services. While running Sql in a regular job would seem to suffice, an SSIS package would be more in line with the other processes for the alerts in terms of manageability. The SSIS package should follow the following guidelines:Deletes records from a given number of tables, based on a specified date column for each table and a specified number of days for each table, or other conditions. SystemAlertQueue: 30 days old based on the SubmitTimestamp column. SystemAlertChron: 30 days old based on the EventTimestamp column. SystemAlertNotifChron: 30 days old based on the NotifTimestamp column. WMICheck, related tables (based on WMICheckID, see WMIAlerts database diagram in SqlServer) 15 days old based on BeginCheckDate column. Each table€™s deletion routine should be distinguishable in the package. Does any body know how to do this.. Please help meRegardsDeepu M.I
View 1 Replies
View Related
Mar 6, 2008
I have a SSIS job, one of the last steps it performs is to execute a SQL 2000 DTS package. This has to be done as a SQL 2000 DTS package as it is performing rebuilds of SQL 2000 Analysis Services dimensions and cubes. We've found that when the DTS fails the SSIS job is happily completing showing as a success, we would prefer to know it went wrong.
As far as I'm aware SSIS merely starts the DTS off and doesn't care about it's result. I've taken a look in to turning on the logging for the execute DTS package and thought that the ExecuteDTS80PackageTaskTaskResult would give me the answer I need...but is merely written to the log not available as an event-handler. It also looks like it is not safe to put a SQL task in as the next item to go look at the SQL 2000 system tables to look at the log for the DTS package as the SSIS documentation warns that the DTS package can continue to run after the execute DTS package task has ended.
Ideally I want any error raised within the DTS package to cascade up to be an error in the SSIS job, I can then handle it appropriately. I cannot find a way to do this. Is there a way?
If not, can anyone suggest how in the remainder of the SSIS tasks I can be sure that the DTS has completed before I start any other tasks that will check for the SQL 2000 log of its execution?
View 5 Replies
View Related
Nov 13, 2006
Hello,
When I run my report from within visual studio 2005 it generates just fine.
However, when I run the report from the reporting services local web site I get the following error. What do I need to do to fix this (temporarily turning off .net security uusing caspol didn't work).
An error occurred while executing OnInit: Request for the permission of type 'System.Security.Permissions.EnvironmentPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed
View 9 Replies
View Related
Jan 9, 2007
I've run into a problem with SSIS packages wherein tasks that write or copy files, or create or delete directories, quit execution without any hint of an error nor a failure message, when called from an ASP.NET 2.0 application running on any other machine than the one where the package was created from. By all indications it appeared to be an identity/permissions problem.
Our application involves a separate web server and database server. Both have SQL Server 2005 installed, but the application server originally only had Integration services. The packages are file system-deployed on the application server, and are called using Microsoft.SqlServer.Dts.Runtime methods. For all packages that involve file system tasks, the above problem occurs.
When the above packages are run using the command prompt (either DTEXEC or DTEXECUI) the packages execute just fine. This is expected since we are using an administrative account. However when a ShellExecute of the same command is called from ASP.NET, the same problem occurs.
I've tried giving administrative permissions to the ASPNET worker process user to no avail.
I have likewise attempted to use the SQL Server Agent job approach but that approach might not be acceptable for our clients since it means installing SQL Server 2005 Database services on the application server.
I have read the relevant threads in this forum, namely http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1044739&SiteID=1 and http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=927084&SiteID=1 but failed to find any solution appropriate for our set up.
Anybody got any idea on how to go about this?
View 33 Replies
View Related
Oct 25, 2015
I have an SSIS package, that move file from one folder (Download) to another folder (Working), where it will be processed and passed to (Processed) folder. The folder (Working) is created at run time and deleted after finishing process. I ran this package using SQL Server Agent (I created a sql job). My problem is that the package fails to move the file from Download to Working, Although it can move it to other folders (say I skipped Working and move it directly to the already-created folder "Processed").
I traced the problem and found the error "Access is denied", when run the package without Agent (double click). I provided the necessary permissions to all levels of folders to the user XX, which I made it the (SQL Server Agent Service Account) as well as the Job Owner. By this, the package executes successfully (again by double clicking it), but with Agent it FAILS.
Why Agent cannot move the file to the run-time-created folder (Working) ?
View 3 Replies
View Related
Jul 14, 2006
I need to pass variables from the parent package to the child package. I got this to work in a small test case. But in the actual project, I get an error when I try to do that. The parent package's log has this error :
Error: Error 0xC0012050 whike loading package file "c:.......". Package failed validation from the ExecutePackage task. The package cannot run..
There's nothing more to go on here in the log.
Any ideas ?
-chiraj
View 4 Replies
View Related
Apr 2, 2007
I'm trying to return Annotations from a BSM scorecard by calling the PMService.asmx web service. I can successfully call operations like "GetScorecards" that require no parameters...but I keep getting the same error when trying to call "GetAllAnnotationsbyScorecardID"
Here is the Query:
<Query>
<SoapAction>http://www.microsoft.com/performancemanagement/scorecards/GetAllAnnotationsByScorecardID</SoapAction>
<Method Namespace="http://www.microsoft.com/performancemanagement/scorecards/" Name="GetAllAnnotationsByScorecardID">
<Parameters>
<Parameter Name="scorecardID">
<DefaultValue>7e3b541b-51db-4ded-b8f3-f7e8f6657c42</DefaultValue>
</Parameter>
</Parameters>
</Method>
</Query>
Here is the XML error returned:
<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlnsoap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<soap:Body>
<soap:Fault>
<faultcode>soaperver</faultcode>
<faultstring>Server was unable to process request. --> scorecardID</faultstring>
<detail />
</soap:Fault>
</soap:Body>
</soap:Envelope>
Am I structuring the Query the right way? Not sure if I'm a passing the guid correctly...I got the scorecard guid from the result set of calling "GetScorecards"
View 2 Replies
View Related
Mar 22, 2007
Is it possible to call a WCF service from a SQL CLR.
We have had success calling standard web services by including a Proxy in the Assembly but I wondered if it's possible to call out to a WCF service as the enpoint configuraion is done with an external XML file and I don't know if this will work in the same way. We may want to alter the wcf configuration depending on where the service is sitting, same box or remote.
Please do not respond with 'why do you want to do this' the reson we want to do this is because we use Service broker to recieve incoming messages and want to trigger a stored procedure to do some work on an external service when it recives some data.
Cheers,
Darren
View 2 Replies
View Related
Mar 29, 2008
Thanks in advance in reading this thread.
I have developed a big SSIS package to extract data from flat-files ( + 200 Dataflows ).
The situation is the following, inside de SSIS package, there are a lot of validations before extracting & loading the flat-files, i'm running this validations in paralell, so that when a file arrives, it enters the "validation process" and start extracting the file.
When i run the SSIS package from BIDS it works the way i have concepted it... but when i run the ssis in the server, the tables that are loaded through the process are only "available" when the SSIS PACKAGE ends, it is imperative that trough the process, when a table receives new data, it becomes ready, and don't just be available when the SSIS package finishes...
I have attached the an lousing .jpeg.
It is importart for the tables to be available, so the stored procedures(OUTSIDE SSIS PACKAGE) that are dependent of some tables, start working before the SSIS package Ends.
Thanks in Advance.
View 4 Replies
View Related
Jun 26, 2007
Hello,
I have done a search and have read some of the posts, but am left more confused than before. I am fairly new to SSIS. Here is my situation and what i am trying to accomplish.
I have a package that has a sequence container, in which there are multiple SQL tasks (about 20) running in parallel. I have checkpoints enabled, and FailPackageOnFailure enabled as well. If the package fails, when i re-run the package it will run the last task as well as all the other tasks. What I am looking to accomplish is when the package is re-run, have the SQL tasks that failed ran and not the previous successful tasks.
I think the best way would be via disabling tasks on successful completion of a task, where it writes the name of the SQL task to a temp table, but I am skeptical.
Can anyone point me in a direction to help me accomplish what I am looking for please.
Thanks in Advance.
View 4 Replies
View Related
Feb 6, 2007
Hi,
I have a Flat File Source and I want to retrieve few properties of it in an Script Component. How do I?
Also, How could I make the file path of Flat File Source or Connection manager dynamic or configurable through some file ?
any input is appreciated.
Fahad
View 1 Replies
View Related
Feb 7, 2007
Hi everyone,
Once I've accesed to package by means of LoadFromSqlServer method how to read its Sql Tasks, for example?
I'm trying with the Executables property but unsuccessfully results:
pkg.Executables.Item(0)
Thanks in advance,
View 14 Replies
View Related
Feb 12, 2007
Hi,
We want to develop an error handling process that will log the errors into multiple destinations (eventlog, text files or sql database) depending upon a variable set in the package. Also we want that this errror handling process should be initiated by all the tasks in the package on error.
Is this possible? Can the same event handler be called from multiple tasks in the package? Also in the event handler can we call another package which actually does the error handling. This way we have only one place to change our error handling process in case required.
Thanks in advance for your help.
$wapnil
View 4 Replies
View Related
Mar 31, 2008
This may sound a little anal-retentive, but I have a number of SSIS packages that, when I open them, the first thing I have to do is scroll to the left or up to get to where the tasks are displayed. Even if I move the tasks right or down, they still end up in that initial position. This happens even if I use auto arrange.
Is there a way for me to set the package so it has a consistent point or display at which it opens?
View 6 Replies
View Related
Feb 4, 2008
Hi,
Is it possible to call a Web Service using SSRS and bind the dataset to the the table in the Report? Please share your views.
Thanks and Warm Regards
Saanj
View 4 Replies
View Related
Jan 26, 2006
I have several sequence containers in one package that fire off execute package tasks. I would like each of the sequence containers to start at the same time when the job starts running. However when I set them up to do that, i get an error that the variable cannot be read because it is locked. I have the variables setup as readonly so not sure why they are being locked. When I run the package and have each sequence container fire off after the previous one ends it runs fine.
Any ideas on how to get around this?
Thanks!
View 1 Replies
View Related
Aug 16, 2007
I'm trying to do some custom SSIS logging using event handlers, similar to the ideas provided by Jamie Thomson in the past. My problem is that when I use System:ourceID as one of the items to be logged, I can't match up the SourceID to any of the GUIDs that are displayed in the property window for the various tasks in my package.
Where is this sourceID coming from and how can I track it down?
Thanks for any insight on this.
John Woods
View 14 Replies
View Related
Mar 30, 2008
Thanks in advance in reading this thread.
I have developed a big SSIS package to extract data from flat-files ( + 200 Dataflows ).
The situation is the following, inside de SSIS package, there are a lot of validations before extracting & loading the flat-files, i'm running this validations in paralell, so that when a file arrives, it enters the "validation process" and start extracting the file.
When i run the SSIS package from BIDS it works the way i have concepted it... but when i run the ssis in the server, the tables that are loaded through the process are only "available" when the SSIS PACKAGE ends, it is imperative that trough the process, when a table receives new data, it becomes ready, and don't just be available when the SSIS package finishes...
I have attached the an lousing .jpeg.
It is importart for the tables to be available, so the stored procedures(OUTSIDE SSIS PACKAGE) that are dependent of some tables, start working before the SSIS package Ends.
Thanks in Advance.
View 5 Replies
View Related
