Error Redirected to Script Component : Package Doesnt fail.

I have created a data flow task. In that, in a 'data conversion', if a column fails validation then that row is redirected to a script component, which in turn writes the error to a variable.

But though the error is generated and script component receives the error, package doesnt fail.

Is there any way to set the package result to failure inside the script component? I tried set 'FailPackageOnFailure' to true but doesnt work.

Any help is greatly appreciated.

Thanks,

Don

donMahya wrote:

I have created a data flow task. In that, in a 'data conversion', if a column fails validation then that row is redirected to a script component, which in turn writes the error to a variable.

But though the error is generated and script component receives the error, package doesnt fail.

Is there any way to set the package result to failure inside the script component? I tried set 'FailPackageOnFailure' to true but doesnt work.

Any help is greatly appreciated.

Thanks,

Don

You could raise an error within the script component using:

Me.Events.FireError(...) (something like that anyway)

-Jamie

|||

Sorry for the delay. Wasnt working on this part for some time.

I tried

Me.ComponentMetaData.FireError(0, "", strErrDesc, "", 0, True) - Where strErrDesc is error decription.

but still no luck

I tried setting the maxerrorcount to 0 but No Luck. Package still completes without any failure.

Any help or ideas are greatly appreciated.

Thanks,

Don

|||

You should return the failure value inside of your script main method. I do not remember the exact enumeration value but the intellisence will give it to you.

Thanks.

Error Redirected to Script Component : Package Doesnt fail.

I have created a data flow task. In that, in a 'data conversion', if a column fails validation then that row is redirected to a script component, which in turn writes the error to a variable.

But though the error is generated and script component receives the error, package doesnt fail.

Is there any way to set the package result to failure inside the script component? I tried set 'FailPackageOnFailure' to true but doesnt work.

Any help is greatly appreciated.

Thanks,

Don

donMahya wrote:

I have created a data flow task. In that, in a 'data conversion', if a column fails validation then that row is redirected to a script component, which in turn writes the error to a variable.

But though the error is generated and script component receives the error, package doesnt fail.

Is there any way to set the package result to failure inside the script component? I tried set 'FailPackageOnFailure' to true but doesnt work.

Any help is greatly appreciated.

Thanks,

Don

You could raise an error within the script component using:

Me.Events.FireError(...) (something like that anyway)

-Jamie

|||

Sorry for the delay. Wasnt working on this part for some time.

I tried

Me.ComponentMetaData.FireError(0, "", strErrDesc, "", 0, True) - Where strErrDesc is error decription.

but still no luck

I tried setting the maxerrorcount to 0 but No Luck. Package still completes without any failure.

Any help or ideas are greatly appreciated.

Thanks,

Don

|||

You should return the failure value inside of your script main method. I do not remember the exact enumeration value but the intellisence will give it to you.

Thanks.

Error Output for SQL Server Destination

I'm in the process of running some tests to determine which method is faster...

I created a data flow task OleDB Source -> Data Conversion -> OleDb Destination. Error outputs from the OleDB destination is sent to a flat file destination. This works great.

I'm importing millons of rows and found that using SQL Server Destination (local) is much faster than the OleDB Destination. However, I have not figured out how to output errors to a flat file destination like I did when using the OleDB destination.

Is there any way to trap errors in a flat file when using a SQL Server Destination?

Thanks!

NLC

OK..I found the answer to my problem. Books online indicates that SQL Server destination does not support an error output. I guess I'll have to use OleDB destination instead.

NLC

Error message?

Hi,

I have an error message:

Exception Details:System.Data.SqlClient.SqlException: Conversion failed when converting the varchar value '@.qty' to data type int.

It doesnt say which line of code it is reffering to however..

Could someone help me?

The parameter for @.qty is Textbox1.Text - maybe I could make the text box int only or something?

My code is:

private bool ExecuteUpdate(int quantity)
{
SqlConnection con = new SqlConnection();
con.ConnectionString = "Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\ASPNETDB.MDF;Integrated Security=True;User Instance=True";

con.Open();

SqlCommand command = new SqlCommand();
command.Connection = con;
TextBox TextBox1 = (TextBox)FormView1.FindControl("TextBox1");
Label labname = (Label)FormView1.FindControl("Label3");
Label labid = (Label)FormView1.FindControl("Label13");

command.CommandText = "UPDATE Items SET Quantityavailable = Quantityavailable - @.qty WHERE productID=@.productID";
command.Parameters.Add("@.qty", TextBox1.Text);
command.Parameters.Add("@.productID", labid.Text);
command.ExecuteNonQuery();

con.Close();
return true;
}

private bool ExecuteInsert(String quantity)
{
SqlConnection con = new SqlConnection();
con.ConnectionString = "Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\ASPNETDB.MDF;Integrated Security=True;User Instance=True";

con.Open();

SqlCommand command = new SqlCommand();
command.Connection = con;
TextBox TextBox1 = (TextBox)FormView1.FindControl("TextBox1");
Label labname = (Label)FormView1.FindControl("Label3");
Label labid = (Label)FormView1.FindControl("Label13");

command.CommandText = "INSERT INTO Transactions (Usersname,Itemid,itemname,Date,Qty) VALUES ('@.User','@.productID','@.Itemsname',@.date,'@.qty')";
command.Parameters.Add("@.User", System.Web.HttpContext.Current.User.Identity.Name);
command.Parameters.Add("@.Itemsname", labname.Text);
command.Parameters.Add("@.productID", labid.Text);
command.Parameters.Add("@.qty", TextBox1.Text);
command.Parameters.Add("@.date", DateTime.Now.ToString());
command.ExecuteNonQuery();

con.Close();
return true;
}

protected void Button2_Click(object sender, EventArgs e)
{
TextBox TextBox1 = FormView1.FindControl("TextBox1") as TextBox;
ExecuteUpdate(Int32.Parse(TextBox1.Text) );
}

protected void Button2_Command(object sender, CommandEventArgs e)
{
if (e.CommandName == "Update")
{
TextBox TextBox1 = FormView1.FindControl("TextBox1") as TextBox;
ExecuteInsert(TextBox1.Text);
}
}

Thanks to anyone who can help!!!

Jon

jbear123:

Exception Details:System.Data.SqlClient.SqlException: Conversion failed when converting the varchar value '@.qty' to data type int.

Well, the error clearly says that the server is expecting int type value for the parameter called @.qty and you are providing either nothing or some value which is not int for @.qty.

First, make sure that the value in your textbox TextBox1 in of type int.

Second, for better confirmation, pass the cast(ed) value to the @.qty parameter, like Convert.ToInt32(TextBox1.text). This way you'll immediately get notified on the line where you try to convert it to int if the textbox is holding non int values.

jbear123:

command.CommandText = "INSERT INTO Transactions (Usersname,Itemid,itemname,Date,Qty) VALUES ('@.User','@.productID','@.Itemsname',@.date,'@.qty')";

I don't know whether this line is being executed or not, but anyway you'll get an error if at all this line gets executed. The problem is in the insert query you've written. You don't need to add ' (single quotation) around the parameters. It is required only if you're passing the values directly in the query.

Hope this will help.

|||

Hi,

dhimant:

First, make sure that the value in your textbox TextBox1 in of type int.

Just make sure the value typed in is int? Or do i have to edit the settings to make the textbox int only?

The value typed in is always int..

dhimant:

Second, for better confirmation, pass the cast(ed) value to the @.qty parameter, like Convert.ToInt32(TextBox1.text). This way you'll immediately get notified on the line where you try to convert it to int if the textbox is holding non int values.

Where should I put the Convert.ToInt32(TextBox1.text) code?

dhimant:

I don't know whether this line is being executed or not, but anyway you'll get an error if at all this line gets executed. The problem is in the insert query you've written. You don't need to add ' (single quotation) around the parameters. It is required only if you're passing the values directly in the query.

Thanks! Done!

Thanks alot,

Jon

|||

jbear123:

Where should I put the Convert.ToInt32(TextBox1.text) co

Just where you're setting the value for the command parameter value. Instead of just TetBox1.Text write Convert.ToInt32(TextBox1.Text).

|||

Hi,

I had tried that it gives me the same error:

System.Data.SqlClient.SqlException: Conversion failed when converting the varchar value '@.qty' to data type int.

..?

Cheers,

Jon

|||

Now, can you re-post the all modified code again ? I don't see any problem if you've made all the correction. One more thing, Why are you using both button2_command and click events and what are you trying to do with them ?

|||

Hi,

Code:

private bool ExecuteUpdate(int quantity)
{
SqlConnection con = new SqlConnection();
con.ConnectionString = "Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\ASPNETDB.MDF;Integrated Security=True;User Instance=True";

con.Open();

SqlCommand command = new SqlCommand();
command.Connection = con;
TextBox TextBox1 = (TextBox)FormView1.FindControl("TextBox1");
Label labname = (Label)FormView1.FindControl("Label3");
Label labid = (Label)FormView1.FindControl("Label13");

command.CommandText = "UPDATE Items SET Quantityavailable = Quantityavailable - '@.qty' WHERE productID=@.productID";
command.Parameters.Add("@.qty", TextBox1.Text);
command.Parameters.Add("@.productID", labid.Text);
command.ExecuteNonQuery();

con.Close();
return true;
}

private bool ExecuteInsert(String quantity)
{
SqlConnection con = new SqlConnection();
con.ConnectionString = "Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\ASPNETDB.MDF;Integrated Security=True;User Instance=True";

con.Open();

SqlCommand command = new SqlCommand();
command.Connection = con;
TextBox TextBox1 = (TextBox)FormView1.FindControl("TextBox1");
Label labname = (Label)FormView1.FindControl("Label3");
Label labid = (Label)FormView1.FindControl("Label13");

command.CommandText = "INSERT INTO Transactions (Usersname,Itemid,itemname,Date,Qty) VALUES (@.User,@.productID,@.Itemsname,@.date,@.qty)";
command.Parameters.Add("@.User", System.Web.HttpContext.Current.User.Identity.Name);
command.Parameters.Add("@.Itemsname", labname.Text);
command.Parameters.Add("@.productID", labid.Text);
command.Parameters.Add("@.qty", Convert.ToInt32(TextBox1.Text));
command.Parameters.Add("@.date", DateTime.Now.ToString());
command.ExecuteNonQuery();

con.Close();
return true;
}

protected void Button2_Click(object sender, EventArgs e)
{
TextBox TextBox1 = FormView1.FindControl("TextBox1") as TextBox;
ExecuteUpdate(Int32.Parse(TextBox1.Text) );
}

protected void Button2_Command(object sender, CommandEventArgs e)
{
if (e.CommandName == "Update")
{
TextBox TextBox1 = FormView1.FindControl("TextBox1") as TextBox;
ExecuteInsert(TextBox1.Text);
}
}
}

dhimant:

One more thing, Why are you using both button2_command and click events and what are you trying to do with them ?

I used them to initiate update and insert, someone suggested that I didnt put them both in button click? Should I?

Thanks again!

Jon

Error Message conversion failed when converting the nvarchar value to data type int?

Hi,

I can't seem to fix the following error in my stored procedure.

Error Message: Conversion failed when converting the nvarchar value '1007-001' to data type int.

The line of code in my stored procedure that seems to be the problem is the following:

CASE WHEN [Order Details].[Job No] IS NULL THEN [Orders].[Order No] ELSE [Order Details].[Job No] END

Order No has a data type of INT and Job No has a data type NVARCHAR(8). In the above case statement i'm not trying to convert anything but just display a column depending on the out come of the case statement. If anyone knows how to get around this error you help would be very welcome.

hi,

Aston35 wrote:

Hi,

I can't seem to fix the following error in my stored procedure.

Error Message: Conversion failed when converting the nvarchar value '1007-001' to data type int.

The line of code in my stored procedure that seems to be the problem is the following:

CASE WHEN [Order Details].[Job No] IS NULL THEN [Orders].[Order No] ELSE [Order Details].[Job No] END

Order No has a data type of INT and Job No has a data type NVARCHAR(8). In the above case statement i'm not trying to convert anything but just display a column depending on the out come of the case statement. If anyone knows how to get around this error you help would be very welcome.

as you pointed out, the 2 columns are of a different data type, so you have to deal accordingly.. as you can not convert 'abc' to an integer data type, you have to perform the opposite, convert the int to a varchar...

SET NOCOUNT ON;

DECLARE @.b int;

SET @.b = 0;

PRINT 'works';

SELECT CASE WHEN @.b = 0 THEN 1 ELSE 'a' END AS [Result];

SET @.b = 1;

PRINT 'does not work';

SELECT CASE WHEN @.b = 0 THEN 1 ELSE 'a' END AS [Result];

GO

DECLARE @.b int;

SET @.b = 1;

PRINT 'works';

SELECT CASE WHEN @.b = 0 THEN CONVERT(varchar, 1) ELSE 'a' END AS [Result];

--<

works

Result

--

1

does not work

Result

--

Msg 245, Level 16, State 1, Line 9

Conversion failed when converting the varchar value 'a' to data type int.

works

Result

a
regards|||Thanks Andrea, will give your suggestion ago and see how i get on.