Showing posts with label service. Show all posts
Showing posts with label service. Show all posts

Tuesday, March 27, 2012

Executing Package From Web Service

Hi,

I'm having another go at attempting to call an SSIS package from a web service. The Web Service is set up on the same server as the sql server and SSIS package deployment. When i attempt to run the pacage from the web service it starts and then inmediately fails. I can see this in both the Integration Service Logs and the event viewer.

If i click on the package itself and run it using the DTExecUI, it runs without a problem. No logs are output by the package when called by the web service.

Can anyone suggest anything that i could to see if i can diagnose where the problem stems from.

Many thanks in advance,

Grant

Are any exceptions being thrown within the WS code?

Are you passing IDTSEvents or an implementation of (DefaultEvenst for example) into the load and execute methods?

Do you get any error events?

You really need to have decent error handling and make good use of the events from those methods to find out what is going on.

Without an error message we are in the dark out here.

|||hi, i had the same problem, The reason for this execution error is that you are using the NT AUTHORITY\NETWORK SERVICE user, this is one who executes the package.

I suggest you to verify the permissions given to this user into the DB tables and also read this articles:

http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=607891&SiteID=17
http://sqljunkies.com/WebLog/knight_reign/archive/2006/01/05/17769.aspx

regards|||Hi Darren,

You are indeed correct i do need to add error tracking of some description included in the WS, apologies for not having this information at first.

The WS does not trigger any exceptions, i have since added exception handling but nothing was highlighted. It returns the enumerator 1 for "Package Failed".

If possible, I'd like some further help on how to use the IDTSEvents, could you point me to any good URL's or help me yourself.

In the mean time , i have logged on to the PC as the account under which the WS is running. Upon trying to execute the package, it fails and looks as though it may be to do with access denied errors to the file system of a remote machine where files that require processing are stored and processed to. These directories have full control set for the account running the web service and as a result i believe they should be able to get access, but unfortunately don't.

I'll keep plugging away at it, but setting up the IDTSEvents may help if you can provide further info on this subject.

Thanks,

Grant|||Further to my last post. I've logged into the PC where the SSIS package resides with the same account that calls the web service. It seems to definitely be a permissions issue with the file system access. I have mapped a link to the UNC path of the folders to be processed and when i ran the DTExecUI for a second time it ran with no problems.

What i don't understand now is that despite the web service account having full control over the directories on the remote PC it still fails to see them. Any ideas why this is the case?

Cheers,

Grant|||

Handling Package Events Programmatically
(http://msdn2.microsoft.com/es-es/library/ms135967.aspx)


Loading and Running a Local Package Programmatically
(http://msdn2.microsoft.com/en-us/library/ms136090.aspx)

I woudl expect you to get an error event with a similar message to what you have seen running interactively. That is a good test method by the way.

Are you referencing UNC paths or trying to use a mapped network drive from the package normally? Mapped drives are generally a bad idea when used unattended, just use a UNC. Obviously permissions need to be correct, but I cannot suggest anything other than standard Windows permissions troubleshooting.

|||hi Darren,

Thanks for the links, i'll be sure to read through them. I think i have my problem resolved. It seems that i had to share the top level folder (where all sub folders relate to the SSIS files etc) so that it could be seen via the network. I was initially trying to use the C$ default admin share which wouldn't allow access via the web service account.

It seems to be working with the network share. I've also had to set up anonymous access on the web service to stop the constant prompting for user name and password. Is there a better way to secure the web service when it is called from another ASPNET page?

Many thanks for your help,

Grant

executing job with non-admin account

hello,
i administer w2k server with sql server and analysis
service installed on it.
some developers use it to tackle their projects.
one of them needs to test job execution and can manage to
create, but is not being able to execute it.
he has registered the server on his desktop enterprise
manager with sql login that is db owner of a given
database.
i wouldn't want to give him sysadmin server role - one
that has more power than he needs.
Would someone knows an alternative way to grant him
rights to run job not being sysadmin?
TIA
Mas"Mas" <anonymous@.discussions.microsoft.com> wrote in message
news:0cbd01c3adf9$b428f1b0$a501280a@.phx.gbl...
> Would someone knows an alternative way to grant him
> rights to run job not being sysadmin?
Oracle? DB2?
One work-around I've found for the permissions problem for the Sqlagent (so
you don't get that infernal database "guest" permissions error) is to set up
the job steps as OS commands that call dtsrun & osql. Just set the user up
as an operator, he'll then be able to run the xp_* sys. procs to run the OS
commands from the job manager, with no SA privileges to worry about. But
talk about convoluted! How 'bout a real job/task security model, eh
Microsoft?
You're not the first to complain about this on this forum, it's frequent
question. MS's security model is - well - unique in the industry in this
regard. The answer in these fora so far has been "It's supposed to work that
way." Maybe one of the MVPs who hang out here might write an FAQ that
explains how "It's supposed to work that way" (along with MS's idea of error
handling and load balanced clusters). Another Yukon feature to wait/pay for?
Tell you what... Max-DB/SAP-DB & PostgreSQL are coming up close behind and
are going to pass MS if MS doesn't watch out.
--j|||Hi, many thanks! I'll try to workout this way.
Mas
>--Original Message--
>"Mas" <anonymous@.discussions.microsoft.com> wrote in
message
>news:0cbd01c3adf9$b428f1b0$a501280a@.phx.gbl...
>> Would someone knows an alternative way to grant him
>> rights to run job not being sysadmin?
>Oracle? DB2?
>One work-around I've found for the permissions problem
for the Sqlagent (so
>you don't get that infernal database "guest" permissions
error) is to set up
>the job steps as OS commands that call dtsrun & osql.
Just set the user up
>as an operator, he'll then be able to run the xp_* sys.
procs to run the OS
>commands from the job manager, with no SA privileges to
worry about. But
>talk about convoluted! How 'bout a real job/task
security model, eh
>Microsoft?
>You're not the first to complain about this on this
forum, it's frequent
>question. MS's security model is - well - unique in the
industry in this
>regard. The answer in these fora so far has been "It's
supposed to work that
>way." Maybe one of the MVPs who hang out here might
write an FAQ that
>explains how "It's supposed to work that way" (along
with MS's idea of error
>handling and load balanced clusters). Another Yukon
feature to wait/pay for?
>Tell you what... Max-DB/SAP-DB & PostgreSQL are coming
up close behind and
>are going to pass MS if MS doesn't watch out.
>--j
>
>.
>

Monday, March 12, 2012

Execute SSIS package through ASP.NET Web Service - different problem

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

Is logging enabled on the package? If so, are you getting any error messages?

If not, enable a text file log for the package so you can see if any errors are getting logged.

|||

Hi, This is not a problem with package execution. All my packages have logging enabled but there is no possibility to log anything because package never started. The problem is strictly with the Microsoft.SqlServer.ManagedDTS and Execute() method and for me this is a bug. My understanding is that calling Execute method will cause SQL Server Integration Services to spawn another process. If this is not possible for one or another reason as is the case when call is made by the remote client that doesn't have SSIS installed or because of the issue of not propagating security context, Execute method reports package execution failure, which is misleading. I am hoping that someone from Microsoft Team can finally post some clarifications and guidelines because this is a thorny issue for many people.

Thanks.

Sinisa

Execute SSIS package through ASP.NET Web Service - different problem

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

Is logging enabled on the package? If so, are you getting any error messages?

If not, enable a text file log for the package so you can see if any errors are getting logged.

|||

Hi, This is not a problem with package execution. All my packages have logging enabled but there is no possibility to log anything because package never started. The problem is strictly with the Microsoft.SqlServer.ManagedDTS and Execute() method and for me this is a bug. My understanding is that calling Execute method will cause SQL Server Integration Services to spawn another process. If this is not possible for one or another reason as is the case when call is made by the remote client that doesn't have SSIS installed or because of the issue of not propagating security context, Execute method reports package execution failure, which is misleading. I am hoping that someone from Microsoft Team can finally post some clarifications and guidelines because this is a thorny issue for many people.

Thanks.

Sinisa

Friday, February 17, 2012

Execute Integration Package by SP

Hi,
I'm writing a ASP.NET application, and I would like to export data from SQL
Server 2005 to MS-Excel. I know that the Integration Service in SQL 2005
has replaced the DTS in SQL2K. I'm wondering how I can execute the
Integration package from either ASP.NET or by means of Stored Procedures.
Thank you.
Regards,
JanetWell, I have not pkayed with this issue in SQL Server 2005 ,however , can
you create a linked server to EXCEL or using OPENDATSOURCE
"Janet >" <<unknown> wrote in message
news:OoNzgaYfGHA.2032@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I'm writing a ASP.NET application, and I would like to export data from
> SQL Server 2005 to MS-Excel. I know that the Integration Service in SQL
> 2005 has replaced the DTS in SQL2K. I'm wondering how I can execute the
> Integration package from either ASP.NET or by means of Stored Procedures.
> Thank you.
> Regards,
> Janet
>

execute functions in sql reporter service

Hello
a. How can I calculate in a function in SQL reporter the date for tomorrow.
Now I've filled in a criteria {fn > now()} which shows me all the coming
projects from today.
I only want to show reports for tomorrow. I know how to do it in VB.NET but
can I used in SQL reporter service ?
b. When I export date values to excel, they are shown as numeric value. In
PDF exporter, it works fine? I've changed my dateformat several times,
nothing helps
c. I need a function like this. I a certain value is smaller than 10, the
word "LOW VALUE" is reporter, otherwise the word "HIGH VALUE" is shownReport expressions are essentially VB.NET
So you should be able to everything inside of the expressions.
With the only caveat is that when report is compiled, by default we import
only following namespaces:
System, System.Convert, System.Math and Microsoft.VisualBasic
If you want to get something from any other namespace you'll need to use
full name notation which include full namespace.
"DEME-Carlo" <DEME-Carlo@.discussions.microsoft.com> wrote in message
news:931C2DD3-84A2-4526-A761-55C5A465E12C@.microsoft.com...
> Hello
> a. How can I calculate in a function in SQL reporter the date for
tomorrow.
> Now I've filled in a criteria {fn > now()} which shows me all the coming
> projects from today.
> I only want to show reports for tomorrow. I know how to do it in VB.NET
but
> can I used in SQL reporter service ?
> b. When I export date values to excel, they are shown as numeric value.
In
> PDF exporter, it works fine? I've changed my dateformat several times,
> nothing helps
> c. I need a function like this. I a certain value is smaller than 10,
the
> word "LOW VALUE" is reporter, otherwise the word "HIGH VALUE" is shown
>
>
>
>