SSIS package failes when running through the SQL Server Agent

  • getting the following error when running the ssis packages through the sql agent as a job.

    Executed as user: . ...ute Package Utility Version 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 9:40:03 AM Error: 2008-05-06 12:38:09.54 Code: 0xC002F210 Source: Insert into Package SQL Task Description: Executing the query "exec udsp_error_handling ?,?,?,?,?,?,?,?,?,?,?" failed with the following error: "Difference of two datetime columns caused overflow at runtime.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. End Error Error: 2008-05-06 12:38:09.56 Code: 0xC002F210 " failed with the following error: "Failed to read prelogin response.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters no. The step failed.

    is anybody facing thae same kind of problem ,

    can anybody give a solution for this problem?

  • Can you run the package successfully in BIDS?

  • Maybe you can try changing the Log On for your SQL Server Agent Service Account, with the current user you are using, whether Local administrator or Domain user (also an administrator for local pc). Hope it helps.

  • Hi,

    I'm not sure !!!! but from the error message which you have listed seems to be error in calling an SP, where you are passsing dates as an input paramter... Check whether valid date is getting passed in the parameter...

  • If it is an error in trying to run the SP, also check to make sure the Agent user has execute rights on the SP.

  • jim.powers (5/6/2008)


    Can you run the package successfully in BIDS?

    I am having the same error. I can run the package fine BIDS but not in the SQL Agent. I get error code: 0xC002F210.

    Anyone have any ideas?

  • I seem to be getting the same error when running from BIDS it works fine.

    Microsoft (R) SQL Server Execute Package Utility

    Version 9.00.3042.00 for 32-bit

    Copyright (C) Microsoft Corp 1984-2005. All rights reserved.

    Started: 10:04:57 PM

    Error: 2008-11-05 22:06:28.06

    Code: 0xC002F210

    Source: Update date in DMI_PARAMETERS with last run date Execute SQL Task

    Description: Executing the query "UPDATE DMI_PARAMETERS SET LAST_START_TIME = to_date('11/5/2008 10:06:27 PM', 'dd/mm/yyyy hh24:mi:ss') WHERE PACKAGE = 'FCAddress_ASIS'" failed with the following error: "ORA-01830: date format picture ends before converting entire input string". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

    End Error

    DTExec: The package execution returned DTSER_SUCCESS (0).

    Started: 10:04:57 PM

    Finished: 10:06:28 PM

    Elapsed: 91.016 seconds

    When running from BIDS and sending to a MsgBox the date is displayed as

    "UPDATE DMI_PARAMETERS SET LAST_START_TIME = to_date('11/5/2008 22:06:27', 'dd/mm/yyyy hh24:mi:ss') WHERE PACKAGE = 'FCAddress_ASIS'"

    Any help would be appreciated

  • Has anyone solved this? I've got a simple execute SQL task (i.e. "exec spname") with no parmaters. The package runs fine in BIDS, but fails when run by the agent: 0xC002F210, Unspecified Error. What's strange is that it runs successfully when I refire the job from SSMS.

    Any help would be beyond awesome.

    Thanks!

    Jason

  • I have a similar issue, but I'm not using an sp. In fact, it's an empty package and does absolutely nothing. If I execute the package from SSIS, it's works fine.

    I don't believe that my issue is a perms prob. The bulk of the errs have to do with not finding the package. I've tried to ref it from File and from SSIS, but I'm still getting the same err. It's driving me mad.

    I'm running SSIS 2008 32 bit. I have a proxy setup with a credential ref user. I'm executing it from a job. Any help is apprec.

    Could not load package

    ...

    because of error 0x80070002. Description: Unable to find the specified file. The package could not be loaded.

    The step failed.,00:00:00,0,0,,,,0

  • Jason-476226 (8/1/2009)


    Has anyone solved this? I've got a simple execute SQL task (i.e. "exec spname") with no parmaters. The package runs fine in BIDS, but fails when run by the agent: 0xC002F210, Unspecified Error. What's strange is that it runs successfully when I refire the job from SSMS.

    Any help would be beyond awesome.

    Thanks!

    Jason

    What version of SQL Server are you running?

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • hi I am also having same problem. I am using sql server 2008 64 bit.

    can execute the pkg from BIDS. but through the job. when i call sp. failed with error

  • Exactly the same error? If not, please post the error message.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • I am having this issue. The only thing I can think of is that the server I am running this on is in Germany where the date format is different than in the USA. The package runs fine in BIDS on the server, but bombs when run with the SQL Job agent.

    I believe this has to do with the data format on the server being different that what I expect.

  • You should start a new thread instead of posting on a year-old one.

    What is the problem exactly? What's the error message?

  • This was failing because the system date format on the German server is not the defualt when I run it in BIDS with the English language pack. By forcing the format to mm/dd/yyyy (101) it works in BIDS and via the SQL job.

    I am not going to bother with a new post. Steve Haines DBA

    Error log:

    Error: 2011-09-28 20:47:19.00

    Code: 0x00000000

    Source: Update Date Fields from MMDDYYYY

    Description: Bei der Konvertierung eines char-Datentyps in einen datetime-Datentyp liegt der datetime-Wert auáerhalb des gltigen Bereichs.

    End Error

    Error: 2011-09-28 20:47:19.01

    Code: 0xC002F210

    Source: Update Date Fields from MMDDYYYY Task 'SQL ausfhren'

    Description: Executing the query "UPDATE Customer

    SET CustCreationDate = CustCreateMM + '/' + CustCreateDD + '/' + CustCreateYYYY

    , CustUpdateDate = CustModMM + '/' + CustModDD + '/' + CustModYYYY" failed with the following error: "Die Anweisung wurde beendet.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

    End Error

    /* Only runs in BIDS with this code */

    UPDATE Customer

    SET CustCreationDate = CustCreateMM + '/' + CustCreateDD + '/' + CustCreateYYYY

    , CustUpdateDate = CustModMM + '/' + CustModDD + '/' + CustModYYYY

    /* Runs via the job or in BIDS with this code */

    UPDATE Customer

    SET CustCreationDate = CONVERT(DATETIME, CustCreateMM + '/' + CustCreateDD + '/' + CustCreateYYYY, 101)

    , CustUpdateDate = CONVERT(DATETIME, CustModMM + '/' + CustModDD + '/' + CustModYYYY, 101)

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply