November 17, 2014 at 2:36 pm
I'm ready to pull my hairs out.
I have a SSIS package that includes a data flow that imports a flat file and puts in a destination table. When i run the package in BIDS it succeeds with no errors or warning. But when it runs through a scheduled job on SQL server agent it returns this error.
--------------------------------------------------------------------------------------------------
Source: Data Flow Task Flat File Source [1] Description: Data conversion failed. The data conversion for column "Column 0" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.". End Error Error: 2014-11-17 07:00:06.21 Code: 0xC020902A
Source: Data Flow Task Flat File Source [1] Description: The "output column "Loc" (10)" failed because truncation occurred, and the truncation row disposition on "output column "Loc" (10)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component. End Error Error: 2014-11-17 07:00:06.21 Code: 0xC0202092 Source: Data Flow Task Flat File
Source [1] Description: An error occurred while processing file "C:\temp\location.txt" on data row 763. End Error Error: 2014-11-17 07:00:06.21 Code: 0xC0047038 Sou
rce: Data Flow Task SSIS.Pipeline Description: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "Flat File
Source" (1) returned error code 0xC0202092. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure. End Error DTExec: The package execution returned DTSER_FAILURE (1).
--------------------------------------------------------------------------------------------------
The column lengths for both the flat file source and the destination are exactly the same. And i don't understand why it's successful when run alone on BIDS but fails with this error when ran by SQL server agent. Any help would be greatly appreciated.
November 17, 2014 at 2:53 pm
When the package works, and the job fails in Agent, there's only one culprit to do all of your checking against: The user just changed.
When you run in BIDS/VS, you're the user, and it uses your C:\temp\location.txt.
When Agent runs, it uses the one on the server.
Check the server's C:\temp\location.txt and make sure it's the same file you were working against on your machine. Also, try RDC'ing into the machine and running it in the local BIDS.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 18, 2014 at 10:05 am
I forgot to add that i have been running this in BIDS on the server itself. so the C:\temp\location.txt is actually on the server. The file is imported to that location from an FTP server each time the job runs.
November 18, 2014 at 11:42 am
Sickmyduck091 (11/18/2014)
I forgot to add that i have been running this in BIDS on the server itself. so the C:\temp\location.txt is actually on the server. The file is imported to that location from an FTP server each time the job runs.
Ah, well, that certainly eases troubleshooting.
My personal next step would be to determine if my BIDS run was actually seeing an equivalent issue. I've seen minor errors, like truncations, only be displayed in the execution results without actually failing a package.
So, what I'd do next is in my BIDS copy is a few quick things. Create a dummy variable of type object just as a dump site. Next, go into the dataflow and disconnect the destination from the second to last object. Slap a Conditional Split into that. LEN( LOC) > 10 gets shipped to a separate data stream. Send the main down into the destination, your new datastream off to a new recordset destination. Rt-click the stream to the recordset destination and activate the data viewer. See what comes up.
My guess is the file actually does have a problem, and BIDS isn't being strict enough in enforcement.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 18, 2014 at 3:01 pm
Evil Kraig F (11/18/2014)
Sickmyduck091 (11/18/2014)
I forgot to add that i have been running this in BIDS on the server itself. so the C:\temp\location.txt is actually on the server. The file is imported to that location from an FTP server each time the job runs.Ah, well, that certainly eases troubleshooting.
My personal next step would be to determine if my BIDS run was actually seeing an equivalent issue. I've seen minor errors, like truncations, only be displayed in the execution results without actually failing a package.
So, what I'd do next is in my BIDS copy is a few quick things. Create a dummy variable of type object just as a dump site. Next, go into the dataflow and disconnect the destination from the second to last object. Slap a Conditional Split into that. LEN( LOC) > 10 gets shipped to a separate data stream. Send the main down into the destination, your new datastream off to a new recordset destination. Rt-click the stream to the recordset destination and activate the data viewer. See what comes up.
My guess is the file actually does have a problem, and BIDS isn't being strict enough in enforcement.
Thanks for the awesome suggestion, i did the conditional split as suggested and all the records made it the original destination table, suggesting that there is no loc greater than 10 characters. In the progress report there is no mention of warnings, truncation or conversion errors.. At this point i'm just stumped.
November 19, 2014 at 2:28 pm
Dansong00 (11/18/2014)
Thanks for the awesome suggestion, i did the conditional split as suggested and all the records made it the original destination table, suggesting that there is no loc greater than 10 characters. In the progress report there is no mention of warnings, truncation or conversion errors.. At this point i'm just stumped.
My pleasure. Little tricks like that can make troubleshooting a lot easier. In this case though?
I have no fricking idea. I'm sorry, you're not the only one stumped. That makes no sense from an RDC. Unless, somehow, it's Ftp'ing a different file as the Agent instead of as you... it's the only thing I can come up with.
Run the package as the agent, and let it fail. Then go into C:\temporary and yank the file it actually pulls. Stick in in excel or something and see what the heck the agent is actually downloading.
If THAT still doesn't show an issue... errr... ummm...
Glitch. Reload it to the server. Something's possibly corrupted on the uploaded one. If that doesn't help, try rebuilding the package from scratch.
Cause I got nothing else that could be the problem without getting onsite and digging into it and hopefully spotting something I'm forgetting to ask about.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply