If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > Microsoft SQL Server > SSIS Job fails with error

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #2 (permalink)  
Old
Wage drone 24601
 
Join Date: Jan 2003
Location: Massachusetts
Posts: 5,439
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.
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
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.
Reply With Quote
  #5 (permalink)  
Old
9th inning DBA
 
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 --
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
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
Quote:
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=-
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On