November 17, 2011 at 1:53 pm
Hello!
I'm having a hard time trying to get out of a script and also fail the task in my SSIS package.
Basically I'm trying to read a text file (in a Row object) and validate if a date in the file is accurate.
So the code (listed below) does test it and finds the issue.
The thing is, I can't break out of it and this is over populating the log file with the same error message.
I want it to, if the date is incorrect, fail the Script (which is not happening) and get out of the loop.
This might be more on the C# side than the SSIS but It's worth a try.
Thaks in advance
---
---
---
/* Microsoft SQL Server Integration Services Script Component
* Write scripts using Microsoft Visual C# 2008.
* ScriptMain is the entry point class of the script.*/
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using System.Windows.Forms;
[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
public override void PreExecute()
{
base.PreExecute();
/*
Add your code here for preprocessing or remove if not needed
*/
}
public override void PostExecute()
{
base.PostExecute();
/*
Add your code here for postprocessing or remove if not needed
You can set read/write variables here, for example:
Variables.MyIntVar = 100
*/
}
public override void Input0_ProcessInputRow(Input0Buffer Row)
{
int defYear = 0;
bool pbCancel = true;
try
{
do
{
#region File Structure
Row.DealerNumber = Row.Column0.ToString() + Row.Column3.ToString();
defYear = 2000;
Row.BillDate = (defYear.ToString() + "-" + Row.Column6.Substring(2, 2) + "-" + Row.Column6.Substring(4, 2));
if (Row.Column18.ToString().Equals(DateTime.Today.ToString("yyyy-MM-dd")))
{
//MessageBox.Show(DateTime.Today.ToString("yyyy-MM-dd"));
}
else
{
// Here's where I'm having the problem...
pbCancel = false;
this.ComponentMetaData.FireError(0, "Validating Data File", "Error on data file date.", "", 0, out pbCancel);
break; // I break the process here
}
} while (Row.NextRow());
return; // Even throwing a return here but it's not breaking out of the loop.
}
catch (Exception e)
{
throw e;
}
}
}
November 17, 2011 at 2:26 pm
This is in a transformation script component?
You need to include the equivalent to this VB in your Catch.
dts.TaskResult = dts.Results.Failure
That should cause a hard stop on the row that's caught, and fail the dataflow at that point. I'm not entirely sure if you force a hard stop in a package that it'll immediately stop processing the existing stream members though. I can't do a full test at the moment, my environment's locked up on an 8 hour process I'm loathe to interfere with.
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 17, 2011 at 2:56 pm
Thanks for the reply.
I use the C# equivalent ...
DTSExecResult = DTSExecResult.DTSER_FAILURE;
But for some reason gives me an error.
I really don't know what's going on.
I did use a workaround but it's not pretty as it was supposed to be. 🙂
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply