August 8, 2012 at 1:33 pm
I have an SSIS that ran in SQL Agent, but apparently it failed, but SQL Agent thought it ran ok.
Here's the step output:
Server myserver
Job Name dealerimport
Step Name dealerimport
Duration 00:00:01
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0
Message
Executed as user: myserver\sqlagent. Microsoft (R) SQL Server Execute Package Utility Version 10.0.4000.0 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 9:39:57 AM Error: 2012-08-08 09:39:57.93 Code: 0xC0202055 Source: Data Flow Task Flat File Source 1 [454] Description: The column delimiter for column "Type" was not found. End Error Error: 2012-08-08 09:39:57.93 Code: 0xC0202091 Source: Data Flow Task Flat File Source 1 [454] Description: An error occurred while skipping data rows. End Error Error: 2012-08-08 09:39:57.94 Code: 0xC0047038 Source: Data Flow Task SSIS.Pipeline Description: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "Flat File Source 1" (454) returned error code 0xC0202091. 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_SUCCESS (0). Started: 9:39:57 AM Finished: 9:39:57 AM Elapsed: 0.64 seconds. The package executed successfully. The step succeeded.
What can I do?
August 8, 2012 at 1:48 pm
The reason SQL Agent reported success is you're not failing the package when that step fails. In your package, on the Control Flow tab, click on your Data Flow task. In the Properties pane, "FailPackageOnFailure" should be set to True.
Now for the rest. In your Flat File Source are you skipping header rows?
In Visual Studio/BIDS, go to the Connection Managers tab, right-click your Flat File Source and select Edit...
In the field labelled "Header rows to skip", what do you have?
Try running the package inside VS/BIDS in debug mode by clicking the green arrow button. This may give you a better idea of what's causing the failure.
August 8, 2012 at 1:58 pm
ok, I'll check the settings.
Yes, someone downloaded or named the file wrong and so we got a different format that it tried to import.
August 8, 2012 at 2:09 pm
I suggest you set up a framework to log SSIS package progress. It will make troubleshooting these issues much easier. Andy Leonard may have a blog on the subject. I first read about SSIS Frameworks in this book
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply