job running SSIS package keeps failing but the SSIS package by itself runs perfectly fine

Hey, I've a few jobs which call SSIS packages. If I run the SSIS package, it runs fine but if I try to run the job which calls this package, it fails. Can someone help me troubleshoot this issue? None of my jobs that call an SSIS package work. All of them fail.

Thank you

Tej

Need to add some mopre to the description of my problem. These packages were originally on a SQL 2000 server. I migrated them to SSIS. When I run a package it says

'SSIS package <package name> starting.
Error: 0x0 at <package name>: System.Runtime.InteropServices.COMException (0x80040427): Execution was canceled by user.
at DTS.PackageClass.Execute()
at Microsoft.SqlServer.Dts.Tasks.Exec80PackageTask.Exec80PackageTask.ExecuteThread() Task failed: <package name> Warning: 0x80019002
at <package name>: The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors. SSIS package <package name> finished: Failure.'

This is in Article 904796. But the thing is these packages were never MetaDataServices.. I had them on SQL Server and now on a file system. I imported them in a SSIS project and have it on a share. but it still gives me the above error.

Thank you

Tej

|||

I have the same problem. Except that I have had these packages working prior to this failure. I tried re-installing the SQL Server 200 DTS Designer components as suggested in the article, but to no avail. Same result. I will be very interested if you get any helpful responses.

|||

Hey Peter. First of all, when I put these in MSDB, they worked fine. Also, I didn't try this but try to look for the security part for every package in its properties. It's encrypted with User Key or something. Remove the encryption and it should be fine.

Let me know how it goes. Thanks

|||

so, sounds like the common issue of getting agent and package permissions in sync.

for others, the KB
An SSIS package does not run when you call the SSIS package from a SQL Server Agent job step

http://support.microsoft.com/?kbid=918760

|||

I have the same problem.

My solution is to click on the "Load DTS Package Internally" and it works perfectly. Hope this would help.

|||Hey. I think I've figured out what the problem is. SSIS by default stores the packes encrypted by user key. It's in th eproperties of the package. If you use, SQL Server encryption which I used, it removes that encryption. Also, if you want to use th efile system for this, you might want to go to the properties of the package and say no encryption instead of encrypting by user key. This should work just fine... Thank you

job running SSIS package keeps failing but the SSIS package by itself runs perfectly fine

Hey, I've a few jobs which call SSIS packages. If I run the SSIS package, it runs fine but if I try to run the job which calls this package, it fails. Can someone help me troubleshoot this issue? None of my jobs that call an SSIS package work. All of them fail.

Thank you

Tej

Need to add some mopre to the description of my problem. These packages were originally on a SQL 2000 server. I migrated them to SSIS. When I run a package it says

'SSIS package <package name> starting.
Error: 0x0 at <package name>: System.Runtime.InteropServices.COMException (0x80040427): Execution was canceled by user.
at DTS.PackageClass.Execute()
at Microsoft.SqlServer.Dts.Tasks.Exec80PackageTask.Exec80PackageTask.ExecuteThread() Task failed: <package name> Warning: 0x80019002
at <package name>: The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors. SSIS package <package name> finished: Failure.'

This is in Article 904796. But the thing is these packages were never MetaDataServices.. I had them on SQL Server and now on a file system. I imported them in a SSIS project and have it on a share. but it still gives me the above error.

Thank you

Tej

|||

I have the same problem. Except that I have had these packages working prior to this failure. I tried re-installing the SQL Server 200 DTS Designer components as suggested in the article, but to no avail. Same result. I will be very interested if you get any helpful responses.

|||

Hey Peter. First of all, when I put these in MSDB, they worked fine. Also, I didn't try this but try to look for the security part for every package in its properties. It's encrypted with User Key or something. Remove the encryption and it should be fine.

Let me know how it goes. Thanks

|||

so, sounds like the common issue of getting agent and package permissions in sync.

for others, the KB
An SSIS package does not run when you call the SSIS package from a SQL Server Agent job step

http://support.microsoft.com/?kbid=918760

|||

I have the same problem.

My solution is to click on the "Load DTS Package Internally" and it works perfectly. Hope this would help.

|||Hey. I think I've figured out what the problem is. SSIS by default stores the packes encrypted by user key. It's in th eproperties of the package. If you use, SQL Server encryption which I used, it removes that encryption. Also, if you want to use th efile system for this, you might want to go to the properties of the package and say no encryption instead of encrypting by user key. This should work just fine... Thank yousql

job running SSIS package keeps failing but the SSIS package by itself runs perfectly fine

Hey, I've a few jobs which call SSIS packages. If I run the SSIS package, it runs fine but if I try to run the job which calls this package, it fails. Can someone help me troubleshoot this issue? None of my jobs that call an SSIS package work. All of them fail.

Thank you

Tej

Need to add some mopre to the description of my problem. These packages were originally on a SQL 2000 server. I migrated them to SSIS. When I run a package it says

'SSIS package <package name> starting.
Error: 0x0 at <package name>: System.Runtime.InteropServices.COMException (0x80040427): Execution was canceled by user.
at DTS.PackageClass.Execute()
at Microsoft.SqlServer.Dts.Tasks.Exec80PackageTask.Exec80PackageTask.ExecuteThread() Task failed: <package name> Warning: 0x80019002
at <package name>: The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors. SSIS package <package name> finished: Failure.'

This is in Article 904796. But the thing is these packages were never MetaDataServices.. I had them on SQL Server and now on a file system. I imported them in a SSIS project and have it on a share. but it still gives me the above error.

Thank you

Tej

|||

I have the same problem. Except that I have had these packages working prior to this failure. I tried re-installing the SQL Server 200 DTS Designer components as suggested in the article, but to no avail. Same result. I will be very interested if you get any helpful responses.

|||

Hey Peter. First of all, when I put these in MSDB, they worked fine. Also, I didn't try this but try to look for the security part for every package in its properties. It's encrypted with User Key or something. Remove the encryption and it should be fine.

Let me know how it goes. Thanks

|||

so, sounds like the common issue of getting agent and package permissions in sync.

for others, the KB
An SSIS package does not run when you call the SSIS package from a SQL Server Agent job step

http://support.microsoft.com/?kbid=918760

|||

I have the same problem.

My solution is to click on the "Load DTS Package Internally" and it works perfectly. Hope this would help.

|||Hey. I think I've figured out what the problem is. SSIS by default stores the packes encrypted by user key. It's in th eproperties of the package. If you use, SQL Server encryption which I used, it removes that encryption. Also, if you want to use th efile system for this, you might want to go to the properties of the package and say no encryption instead of encrypting by user key. This should work just fine... Thank you

Job Outcome Constants (SQLDMO_JOBOUTCOME_TYPE)

How can i write a simple procedure as below, call it from a job, and because
@.m = 2 it will fail the job step and so show me the red x beside the job nam
e
in ent mgr?
create procedure marc1 as
declare @.m int
set @.m = 2
if @.m = 1
begin
print '2'
end
else
begin
print '2'
return -1
endmarcmc wrote:
> How can i write a simple procedure as below, call it from a job, and becau
se
> @.m = 2 it will fail the job step and so show me the red x beside the job n
ame
> in ent mgr?
> create procedure marc1 as
> declare @.m int
> set @.m = 2
> if @.m = 1
> begin
> print '2'
> end
> else
> begin
> print '2'
> return -1
> end
Use RAISERROR:
e.g. RAISERROR('My error message', 16, 1)

Job issue

Dear all,
I want to define a job which might be able to launch a .BAT where its
location is my local workstation. Is it possible? That .BAT call a .EXE and
we wish to keep out of our production server. That job will be scheduled fro
m
the server...
Thanks a lot,There are a couple of things to consider here. If you create the job on your
sql server, and have the first step be an OS Command (CmdExec) with a value
like
\\yourworkstation\c$\yourbatch.bat
-although the file resides on the machine yourworkstation, the program is
actually executed from the server (and since the exe that the bat file calls
won't be on the server, it will fail)
-if the sql server is running under the local system account, you won't be
able to connect over the network to your workstation anyway.
If you needed the program to physically reside and execute from your
workstation, you're best bet would be to create a scheduled task on your
workstation, and call the command from the sql server like
schtasks /run /tn YourTask /s yourworkstation
where YourTask is the name given to your task when you create it. If the sql
server is running under the local system account, you will need to specify a
user account that has local administrative priveledges on yourworkstation,
like so
schtasks /run /tn YourTask /s yourworkstation /u administrator /p password
The security implications here are that you have an administrator password
stored with the job on the sql server.
"Enric" wrote:

> Dear all,
> I want to define a job which might be able to launch a .BAT where its
> location is my local workstation. Is it possible? That .BAT call a .EXE an
d
> we wish to keep out of our production server. That job will be scheduled f
rom
> the server...
> Thanks a lot,

JDBC: calling a stored procedure with multiple return values.

Using JDBC, is there a way to call a stored procedure with multiple
return values? Thanks.
randy.p.ho@.gmail.com wrote:

> Using JDBC, is there a way to call a stored procedure with multiple
> return values? Thanks.

Absolutely. What do you mean by 'multiple return values'? Multiple output
parameters? Multiple result sets and/or update counts? Multiple mixes of
result sets and update counts?
If you will show the procedure signature and maybe even the text? Tell
us what the body of the procedure returns.

Joe Weinstein at BEA|||Thanks for the reply. I meant "multiple output parameters".

Here is how I execute the stored procedure:

declare @.ErrorID int
declare @.ErrorStr varchar(255)
exec procName
@.customerId = '1234567890',
@.customerName = 'some name',
@.error_code = @.ErrorID,
@.error_state = @.ErrorStr

Here is the procedure:

create procedure uxt1.procName
@.customerId char(15) output,
@.customerName char(64) output,
@.error_code int output,
@.error_state varchar(255) output
... ...
/* all the business logic */
... ...
return (@.error_state)
GO

Here's what SQL server gives me if I do a "Script object as Execute":
DECLARE @.RC int
DECLARE @.customerId char(15)
DECLARE @.customerName char(64)
DECLARE @.error_code int
DECLARE @.error_state varchar(255)
EXEC @.RC = [uxt1].[procName] @.customerId, @.customerName, @.error_code
OUTPUT , @.error_state OUTPUT

The following is what I've tried in a Java program:
...
CallableStatement cs = conn.prepareCall(" {? = call
uxt1.procName(?,?,?,?)}" );
cs.registerOutParameter(1,java.sql.Types.INTEGER);
cs.setString(2,"some ID");
cs.setString(3,"some Name");
cs.registerOutParameter(4,java.sql.Types.INTEGER);
cs.registerOutParameter(5,java.sql.Types.VARCHAR);
ResultSet rs = cs.executeQuery();
...

My code doesn't throw any exception; but the procedure was not executed
correctly (i.e. it's not doing what it's supposed to do, which is to
simply insert some values into a table).
Any help is appreciated. Thanks in advance.|||
randy.p.ho@.gmail.com wrote:

> Thanks for the reply. I meant "multiple output parameters".
> Here is how I execute the stored procedure:
> declare @.ErrorID int
> declare @.ErrorStr varchar(255)
> exec procName
> @.customerId = '1234567890',
> @.customerName = 'some name',
> @.error_code = @.ErrorID,
> @.error_state = @.ErrorStr
>
> Here is the procedure:
> create procedure uxt1.procName
> @.customerId char(15) output,
> @.customerName char(64) output,
> @.error_code int output,
> @.error_state varchar(255) output
> ... ...
> /* all the business logic */
> ... ...
> return (@.error_state)
> GO
>
> Here's what SQL server gives me if I do a "Script object as Execute":
> DECLARE @.RC int
> DECLARE @.customerId char(15)
> DECLARE @.customerName char(64)
> DECLARE @.error_code int
> DECLARE @.error_state varchar(255)
> EXEC @.RC = [uxt1].[procName] @.customerId, @.customerName, @.error_code
> OUTPUT , @.error_state OUTPUT
>
> The following is what I've tried in a Java program:
> ...
> CallableStatement cs = conn.prepareCall(" {? = call
> uxt1.procName(?,?,?,?)}" );
> cs.registerOutParameter(1,java.sql.Types.INTEGER);
> cs.setString(2,"some ID");
> cs.setString(3,"some Name");
> cs.registerOutParameter(4,java.sql.Types.INTEGER);
> cs.registerOutParameter(5,java.sql.Types.VARCHAR);
> ResultSet rs = cs.executeQuery();
> ...
> My code doesn't throw any exception; but the procedure was not executed
> correctly (i.e. it's not doing what it's supposed to do, which is to
> simply insert some values into a table).
> Any help is appreciated. Thanks in advance.

Is the procedure executing at all? You shouldn't be calling executeQuery()
unless the first thing the procedure does is a select. Use execute() and
then loop:

cs.execute();
while (true)
{
int update_count = ps.getUpdateCount();
ResultSet rs = ps.getResultSet();
if ((rs == null && (update_count == -1)) break; // done

if (rs != null) process rs;
ps.getMoreResults();
}
// after processing inline results, call ps.getXXX() to get output parameters.

Whatever jdbc driver you're suing is pretty flakey if it
returns a result set from executeQuery() and the
procedure didn't do a select for data to go to the caller...

Joe Weinstein at BEA|||I have closely the same problem, excepting that my stored procs returns a "TABLE".

In fact, I have a SQL function that must return a TABLE and get 2 parameters. Its signature is as follow:

FUNCTION myFunction (@.codett varchar(40), @.codet varchar(40) )
RETURNS @.Tab TABLE([cp] [varchar] (40), [pourcentage] [int])

I'd like to call it from my DAO (using JDBC). having that 'cs' is a CallableStatement, I tried:

cs = jdbcConnection.prepareCall("{? = call myFunction(?, ?)}");
cs.registerOutParameter( 1, Types.OTHER );
cs.setString(2, codett);//codett contains a String
cs.setString(3, codet);//codet contains a String
ResultSet rset = cs.executeQuery();

However, when a get to the "executeQuery()" method, I get an SQLException saying that "myFunction" is a function Object.

have you met such a problem ?

JDBC getBoolean/setBoolean

Hello
When I call stmt.setBoolean(columnIndex, true); The value of "1" in inserted to column.
However when I call rs.getBoolean(column) I get "false". If manually set column value to "true", the returned value of rs.getBoolean will be true.
Thx in advance...Ilja,
We have just shipped a QFE to fix this mess, you can get this by contacting product support directly with the information you have posted.

You can verify that the behavior is what you expect by using our latest community tech preview drop which contains this fix:

http://www.microsoft.com/downloads/details.aspx?familyid=f914793a-6fb4-475f-9537-b8fcb776befd&displaylang=en

Thanks,
Angel|||Hi thx for fast reply.
I think I found reason. Column type must be set to "BIT" in order of using booleans on programming side.
Anyway thx.|||

Using BIT certainly helps since this is the type that maps better to the java boolean type. Unfortunatelly for us the JDBC spec is very clear on the allowed conversions for JDBC drivers.

In the case of getboolean we need to support all of the numeric types and do data dependent conversion (conversions that require that the type contain values that can be converted into boolean) for all character based Sql Server types. Before the QFE I mention above we did not.

Glad to hear this worked out for you, good luck

Angel