Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2007
    Posts
    2

    SSIS Job fails with error

    Hi,
    I have hourly job running which I inherited from developers. I get this errors in the job. I can't figure out what's wrong and why it's failing.
    I can't troubleshoot it either because I do not have exact dev enviornment to reproduce the problem. error is attached.
    Please advise
    =================
    Executed as user: US\sqldbagent. ...Execute Package Utility Version 9.00.3042.00 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 10:06:02 AM Error: 2007-08-21 10:06:03.30 Code: 0xC020837F Source: Fill Update Table with new data DataReader Source [1] Description: The data type of "output column "AvailabilityBy" (1885)" does not match the data type "System.String" of the source column "AvailabilityBy". End Error Error: 2007-08-21 10:06:03.30 Code: 0xC004706B Source: Fill Update Table with new data DTS.Pipeline Description: "component "DataReader Source" (1)" failed validation and returned validation status "VS_NEEDSNEWMETADATA". End Error Error: 2007-08-21 10:06:03.30 Code: 0xC004700C Source: Fill Update Table with new data DTS.Pipeline Description: One or more component failed validation. End Error Error: 2007-08-21 10:06:03.30 Code: 0xC0024107 Source: Fill Update Table with new data Description:. The step failed.
    ===========
    I can run the job for testing. I can't really change anything on server to test this other than database.
    Thanks a lot in advance

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,554
    What does the package do? It appears to be choking on a datatype mismatch in the AvailabilityBy filed (a date field?). Check the source to make sure it contains only dates.

  3. #3
    Join Date
    Aug 2007
    Posts
    2
    This package is supposed to create a new database from portnewschema and move data to portreporting database.
    I checked the avaialbilityby fieled in both places.
    in old database it's set to int and new database it's set to nvarchar(50).
    here are some of my question:
    How do I check if one procedure which is used to do this rpocess has execute priviledge to the user sa.?
    How do I check which username is used to run this job other than sqldbagent?
    When I looked at the data source that it's coming from,
    I see x sign on one box with ERD kind of diagram(where you modify the package or job) it shows that portnewschema is not accessible via ".NET Provider/sqlClient data provider" how do I check to see if this is working fine on my server?

    Thanks for reply

  4. #4
    Join Date
    Jan 2008
    Posts
    1
    Hi Folks

    I am face same error. please guide me how to resolve..

    The points to help to understand the issue:
    1) The first component in the package is Datareader source
    2) I have .dtconfig file. configured the database user id/pwd properly
    3) ProtectionLevel is "DontSaveSensitive"


    The Error:

    #Fields: event,computer,operator,source,sourceid,executioni d,starttime,endtime,datacode,databytes,message
    OnError,,,,,,03/02/2008 5:49:35 AM,03/02/2008 5:49:35 AM,-1071611009,0x,The data type of "output column "TradingAccNo" (226)" does not match the data type "System.String" of the source column "TradingAccNo".

    OnError,,,,,,03/02/2008 5:49:35 AM,03/02/2008 5:49:35 AM,-1073450901,0x,"component "DataReader Source" (1)" failed validation and returned validation status "VS_NEEDSNEWMETADATA".

    OnError,,,,,,03/02/2008 5:49:35 AM,03/02/2008 5:49:35 AM,-1073450996,0x,One or more component failed validation.

    OnError,,,,,,03/02/2008 5:49:35 AM,03/02/2008 5:49:35 AM,-1073594105,0x,There were errors during task validation.


    Thanks
    Nagaraj M.

  5. #5
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,039
    Quote Originally Posted by MCrowley
    Check the source to make sure it contains only dates.
    Have you both checked the source data?

    -- This is all just a Figment of my Imagination --

  6. #6
    Join Date
    May 2008
    Posts
    2

    Smile

    @nagarajbu
    When you get a VS_NEEDSNEWMETADATA message, I advise to open the package in Visual Studio, then open each source component and refresh it.

    If you still get the first part of the message
    OnError,,,,,,03/02/2008 5:49:35 AM,03/02/2008 5:49:35 AM,-1071611009,0x,The data type of "output column "TradingAccNo" (226)" does not match the data type "System.String" of the source column "TradingAccNo".
    You may try to force type by using SUBSTR(your_field,1,your_length) AS your_field.

    a+, =)
    -=Clement=-

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •