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

Execute Multiple Scripts from one directory

I got tired of having to execute multiple scripts one at a time from
my testing server to my production server. So I decided to create a
batch routine that utilizes the command line routine OSQL. All you
have to do to use it is place the batch file in a directory containing
your SQL scripts and execute the batch file. I have placed in the
command the argument to print a result file for each sql script. If
you need your SQL scripts run in a particular order simply rename them
accordingly. i.e. 001_updatecustomer.sql, 002_update_sp.sql
enjoy
REM ########################################
#####
REM Author: Joe Ocampo
REM Date: 04/17/2004
REM Version: 1.0.1
REM ########################################
#####
REM You must execute the script from the SQL server itself
REM and you must use the SA login or the DBO login of the
REM target database.
REM ########################################
#####
SET login=sa
SET password=password
SET server=local
SET database=pubs
for %%a in (*.sql) do osql -d %database% -U %login% -P %password% -i
%%a -o %%a_result.txtJoe wrote:
> I got tired of having to execute multiple scripts one at a time from
> my testing server to my production server. So I decided to create a
> batch routine that utilizes the command line routine OSQL. All you
> have to do to use it is place the batch file in a directory containing
> your SQL scripts and execute the batch file. I have placed in the
> command the argument to print a result file for each sql script. If
> you need your SQL scripts run in a particular order simply rename them
> accordingly. i.e. 001_updatecustomer.sql, 002_update_sp.sql
Exceedingly simple, but incredibly useful, and something that I was going to
have to write myself pretty soon - thanks.
John.|||I knew there had to be an easier way. I was scripting 35 scripts at a
time. Between opening and closing files and the occasional, "Didn't I
run that already?" I knew it was time to find a better way.
Glad to help,
Joe
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

Execute Multiple Scripts from one directory

I got tired of having to execute multiple scripts one at a time from
my testing server to my production server. So I decided to create a
batch routine that utilizes the command line routine OSQL. All you
have to do to use it is place the batch file in a directory containing
your SQL scripts and execute the batch file. I have placed in the
command the argument to print a result file for each sql script. If
you need your SQL scripts run in a particular order simply rename them
accordingly. i.e. 001_updatecustomer.sql, 002_update_sp.sql
enjoy
REM #############################################
REM Author: Joe Ocampo
REM Date: 04/17/2004
REM Version: 1.0.1
REM #############################################
REM You must execute the script from the SQL server itself
REM and you must use the SA login or the DBO login of the
REM target database.
REM #############################################
SET login=sa
SET password=password
SET server=local
SET database=pubs
for %%a in (*.sql) do osql -d %database% -U %login% -P %password% -i
%%a -o %%a_result.txt
Joe wrote:
> I got tired of having to execute multiple scripts one at a time from
> my testing server to my production server. So I decided to create a
> batch routine that utilizes the command line routine OSQL. All you
> have to do to use it is place the batch file in a directory containing
> your SQL scripts and execute the batch file. I have placed in the
> command the argument to print a result file for each sql script. If
> you need your SQL scripts run in a particular order simply rename them
> accordingly. i.e. 001_updatecustomer.sql, 002_update_sp.sql
Exceedingly simple, but incredibly useful, and something that I was going to
have to write myself pretty soon - thanks.
John.
|||I knew there had to be an easier way. I was scripting 35 scripts at a
time. Between opening and closing files and the occasional, "Didn't I
run that already?" I knew it was time to find a better way.
Glad to help,
Joe
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

Execute Multiple Scripts from one directory

I got tired of having to execute multiple scripts one at a time from
my testing server to my production server. So I decided to create a
batch routine that utilizes the command line routine OSQL. All you
have to do to use it is place the batch file in a directory containing
your SQL scripts and execute the batch file. I have placed in the
command the argument to print a result file for each sql script. If
you need your SQL scripts run in a particular order simply rename them
accordingly. i.e. 001_updatecustomer.sql, 002_update_sp.sql
enjoy
REM #############################################
REM Author: Joe Ocampo
REM Date: 04/17/2004
REM Version: 1.0.1
REM #############################################
REM You must execute the script from the SQL server itself
REM and you must use the SA login or the DBO login of the
REM target database.
REM #############################################
SET login=sa
SET password=password
SET server=local
SET database=pubs
for %%a in (*.sql) do osql -d %database% -U %login% -P %password% -i
%%a -o %%a_result.txtJoe wrote:
> I got tired of having to execute multiple scripts one at a time from
> my testing server to my production server. So I decided to create a
> batch routine that utilizes the command line routine OSQL. All you
> have to do to use it is place the batch file in a directory containing
> your SQL scripts and execute the batch file. I have placed in the
> command the argument to print a result file for each sql script. If
> you need your SQL scripts run in a particular order simply rename them
> accordingly. i.e. 001_updatecustomer.sql, 002_update_sp.sql
Exceedingly simple, but incredibly useful, and something that I was going to
have to write myself pretty soon - thanks.
John.