SSIS Package Failure - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

SSIS Package Failure

Author

Topic

poser
Posting Yak Master

124 Posts

Posted - 2011-10-13 : 12:20:26

I have a SSIS package that fails...sometimes running this code.
It does not always fail...
Any help is much appreciated...

IF DATEPART(weekday, GETDATE()) = 7
BEGIN
UPDATE Ttable
SET RUN = 'Y'
WHERE ID = 1
END
ELSE
BEGIN
RAISERROR('Ran',16,1)
END

I have several other steps run
Then I reset the Run to N

UPDATE Ttable
SET RUN = 'N', DT = GETDATE()
WHERE ID = 1

I get this error:

UPDATE Ttable SET Run = 'Y' WHERE ID = 1 END ELSE BEGIN RAISERROR('RAN',16,1) END" failed with the following error: "RAN". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. End Error Warning: 2011-10-13 06:25:02.10 Code: 0x80019002 Source: Daily - Transfer db to DB@ Description: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1);... The package execution fa... The step failed.

R/P

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-13 : 12:49:45

thats because you've written logic inside to throw the error from sql script.So all days except when its 7th day of week it will throw error message and fail the package.
Can you explain what you're trying to do with above script?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

poser
Posting Yak Master

124 Posts

Posted - 2011-10-13 : 13:01:48

So the way this is written this will only be successful on day 7 (Saturday) Rest of the time this step will fail. So they must only want this to run on Saturday.

We are having a lot of jobs fail.....I'm just now getting involved ;(
Her is another failure message:
I'm thinking a lot of their issue is their code....

Message
Executed as user: test. ly, parameters not set correctly, or connection not established correctly. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 7:58:13 PM
Finished: 8:00:46 PM Elapsed: 153.235 seconds. The package execution failed. The step failed.

Thanks for your responses...
R/P

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-13 : 13:14:59

yep. Eaxctly. it will be success only for 7th day of week

The second error looks like its returning no values in resultset under certain circumstances which is attributed again to code logic.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources