Exceptions in Data Flow Scripts

What is the "correct" way of dealing with exceptions in a data flow script component. i.e. am I supposed to catch all exceptions and then set some failure flag? The reason I ask is I've got a script in a dataflow which is occasionally throwing exceptions when trying to convert an empty string to a decimal. Problem was the package locked up and had to be terminated when the exception was thrown (and not caught in the script)?

The only thing which differentiates this package from others I've created is the data flow has a conditional split which creates 2 seperate paths loading 2 seperate SQL tables - the exception is being thrown in a script on one branch which seems to hand the entire flow

PS I have fixed the script so this doesn't happen, i.e. test if the input string is String.Empty and if so set the _IsNull property.

If you are going to go down the road of custom building functions inside a script versus using the derived column or conditional split transformations to look at your data (test your data for correctness), then you'll also have to build in your own error handling as the script component does not support error outputs as part of the data flow.

Phil|||

Hi Phil,

Thanks for the reply, I'm not sure you're answering my question though The problem is that if an exception is thrown in a script and not caught, the package simply locks up - the unhandled exception seems to be blocking - no event handler is called etc. So what I'm asking is how do you flag a failure in a data flow script (if it were a control flow script I would propably create a global catch handler with Dts.TaskResult = Dts.Results.Failure

Dave

|||I think the package should lock up though, as the script component doesn't support the error output stream. So if an error is encountered in a script task, how should the package continue? That is, the error is fatal to the script component.

Some of the other guys will undoubtedly have some more advice when they get in tomorrow morning, especially those that are well versed in script programming.

Sorry, but I won't be of no more help tonight, especially when I'm not sitting in front of SSIS.

Phil|||Does it really lock up, or does the package simply fail?|||

It locks up, i.e. if run from BIDS the scipt component goes yellow when it starts executing, and nothing more occurs.

In other data flows, exceptions do cause the script component (and ultimatly the package) to fail. The different seemingly being that the package has a conditional split and each path of the split appears to be running concurrently when the exception is thrown in the script.

It's interesting that there's no error output from a script - not sure what to make of that - of all my dataflow components, the ones which fail the most are my scripts , esp since I cannot single step through them. Maybe I need to add and error number colume, handle the exception and set the error number in the script and then pass the subsequent flow through a conditional split?

|||

Dave Waterworth wrote:

Maybe I need to add and error number colume, handle the exception and set the error number in the script and then pass the subsequent flow through a conditional split?

Yep, a very valid solution.

Exception Details: System.Data.SqlClient.SqlException: Procedure spInsertBillingQ expects

Hello Coders,

I need help fixing a problem in my code that I do not understand. I basically have a procedure that is supposed to take all user information and then insert it to the DB or update the Database based on user entry.

The call to the method is this

Dim

banknameAsString =""If (objBill.BillTransaction(Session("conString"), Session("whattobill"), PROC_CURR, Session("lISN"), 0, Session("fISN"), Request.ServerVariables("REMOTE_ADDR"),"scott@.mycleanstart.com", Session("fAmount"), Session("first_name"), Session("last_name"), Session("city"), Session("state"), Session("phone"), Session("address1"), Session("address2"), Session("postal_code"), Session("email_address"), Session("fDesc"), Session("ssn"), Session("nameoncard"), Session("PAN"), Session("cvv"), Session("cardexpirationdate"), bankname, Session("txtroutingnumber"), Session("txtaccountnumber"), Session("txtchecknumber"), Session("bqISN"), _

returnedbqISN, AuthCode, OrderNumber, DeclineCode, TermCode, ErrorMessage, authenticationValue, authenticationTransactionID, str_Centinal_ECI,

"signup", PAResStatus, SignatureVerification, paypalSubAgreeID, notificationLocation, strErrorNo, strErrorDesc, strTransactionID, strStatus, strStatusCode, strReasonCode))Then

And then below is the actual method itself......

NOTE: The code below is contained in a DLL

1public bool BillTransaction(string strDBInstance,string ccORach,string whichProcessor,Int32 lISN, Int32 mISN,2Int32 fISN,string ip,string merchant_email,string total_amount,3string firstname,string lastname,string city,string state,string phone,string address1,string address2,string zip,string customer_email,4string product_desc,string socialsecuritynum,string nameoncard,string creditcardnumber, Int32 cardverifynum, DateTime cardexpiredate,5string bankname,string routingnumber,string accountnumber,string checknumber, Int32 bqISN,6ref Int32 returnedbqISN,ref string AuthCode,ref string OrderNumber,ref string DeclineCode,7ref string TermCode,ref string ErrorMessage,8string authenticationValue,string authenticationTransactionID,string eci,string transactiontype,9string PAResStatus,string SignatureVerification,string paypalSubAgreeID,string notificationLocation,10ref string strErrorNo,ref string strErrorDesc,ref string strTransactionId,ref string strStatus,11ref string strStatusCode,ref string strReasonCode)1213{1415if (ccORach.Trim().Length <= 0)16{17ErrorMessage ="CC or Check?";18return false;19}2021if (whichProcessor.Trim().Length <= 0)22{23ErrorMessage ="Blank processor";24return false;25}2627decimal grand_total = Convert.ToDecimal(decimal.Parse(total_amount).ToString("N2"));28string ProcessorResponse="";29Database db = DatabaseFactory.CreateDatabase(strDBInstance);30DBCommandWrapper dbCmdWrapper =null;31bool retBilling =true;32string sql ="";33string bqAction ="SBILL";34string strFirstName ="";35string strLastName ="";36string[] cardname = nameoncard.Split(new char[] {' '});37strFirstName = cardname[0];38for (int i=1; i <= cardname.GetUpperBound(0); i++)39{40strLastName = strLastName +' ' + cardname[i].Trim();41}4243MCS_Encryption.Encryption mcscrypt =new MCS_Encryption.Encryption();44//card number45string enc_cardnumber = mcscrypt.Encrypt(creditcardnumber);46//account number47string enc_accountnumber = mcscrypt.Encrypt(accountnumber);4849if (bqISN > 0)50{51//if bqISN is present, do UPDATE instead52sql ="spUpdateBillingQ";53dbCmdWrapper = db.GetStoredProcCommandWrapper(sql);54dbCmdWrapper.AddInParameter("@.bqISN",DbType.Int32,bqISN);55dbCmdWrapper.AddInParameter("@.email",DbType.String,customer_email);56dbCmdWrapper.AddInParameter("@.bankname",DbType.String,bankname);57dbCmdWrapper.AddInParameter("@.routingnumber",DbType.String,routingnumber);58dbCmdWrapper.AddInParameter("@.accountnumber",DbType.String,enc_accountnumber);59dbCmdWrapper.AddInParameter("@.checknumber",DbType.String,checknumber);60dbCmdWrapper.AddInParameter("@.nameoncard",DbType.String,nameoncard);61dbCmdWrapper.AddInParameter("@.cardnumber",DbType.String,enc_cardnumber);62dbCmdWrapper.AddInParameter("@.cardverifynum",DbType.Int32,cardverifynum);63dbCmdWrapper.AddInParameter("@.cardexpirationdate",DbType.DateTime,cardexpiredate);64dbCmdWrapper.AddInParameter("@.billaddress1",DbType.String,address1);65dbCmdWrapper.AddInParameter("@.billaddress2",DbType.String,address2);66dbCmdWrapper.AddInParameter("@.billcity",DbType.String,city);67dbCmdWrapper.AddInParameter("@.billstate",DbType.String,state);68dbCmdWrapper.AddInParameter("@.billzip",DbType.String,zip);69dbCmdWrapper.AddInParameter("@.billphone",DbType.String, phone);70dbCmdWrapper.AddInParameter("@.ip",DbType.String, ip);71dbCmdWrapper.AddInParameter("@.fISN",DbType.Int32, fISN);72dbCmdWrapper.AddInParameter("@.price",DbType.Currency, grand_total);73dbCmdWrapper.AddInParameter("@.description",DbType.String, product_desc);74dbCmdWrapper.AddInParameter("@.bqaction",DbType.String, bqAction);75dbCmdWrapper.AddInParameter("@.creditamount",DbType.Currency, 0);76dbCmdWrapper.AddInParameter("@.processor",DbType.String, whichProcessor);77db.ExecuteNonQuery(dbCmdWrapper) ;7879returnedbqISN = bqISN;80}81else82{83//insert into billingQ or update if bqISN is passed84sql ="spInsertBillingQ";85dbCmdWrapper = db.GetStoredProcCommandWrapper(sql);86dbCmdWrapper.AddInParameter("@.lISN",DbType.Int32,lISN);87dbCmdWrapper.AddInParameter("@.mISN",DbType.Int32,mISN);88dbCmdWrapper.AddInParameter("@.email",DbType.String,customer_email);89dbCmdWrapper.AddInParameter("@.bankname",DbType.String,bankname);90dbCmdWrapper.AddInParameter("@.routingnumber",DbType.String,routingnumber);91dbCmdWrapper.AddInParameter("@.accountnumber",DbType.String,enc_accountnumber);92dbCmdWrapper.AddInParameter("@.checknumber",DbType.String,checknumber);93dbCmdWrapper.AddInParameter("@.nameoncard",DbType.String,nameoncard);94dbCmdWrapper.AddInParameter("@.cardnumber",DbType.String,enc_cardnumber);95dbCmdWrapper.AddInParameter("@.cardverifynum",DbType.Int32,cardverifynum);96dbCmdWrapper.AddInParameter("@.cardexpirationdate",DbType.DateTime,cardexpiredate);97dbCmdWrapper.AddInParameter("@.billaddress1",DbType.String,address1);98dbCmdWrapper.AddInParameter("@.billaddress2",DbType.String,address2);99dbCmdWrapper.AddInParameter("@.billcity",DbType.String,city);100dbCmdWrapper.AddInParameter("@.billstate",DbType.String,state);101dbCmdWrapper.AddInParameter("@.billzip",DbType.String,zip);102dbCmdWrapper.AddInParameter("@.billphone",DbType.String, phone);103dbCmdWrapper.AddInParameter("@.ip",DbType.String, ip);104dbCmdWrapper.AddInParameter("@.fISN",DbType.Int32, fISN);105dbCmdWrapper.AddInParameter("@.price",DbType.Currency, grand_total);106dbCmdWrapper.AddInParameter("@.description",DbType.String, product_desc);107dbCmdWrapper.AddInParameter("@.bqaction",DbType.String, bqAction);108dbCmdWrapper.AddInParameter("@.creditamount",DbType.Currency, 0);109dbCmdWrapper.AddInParameter("@.processor",DbType.String, whichProcessor);110returnedbqISN = 0;111returnedbqISN = (Int32)db.ExecuteScalar(dbCmdWrapper) ;112113}114115116117//bill credit card or ACH118switch (ccORach.Trim())119{120case"CC":121retBilling = BillCreditCard(whichProcessor,ip,merchant_email,grand_total, strFirstName.Trim(), strLastName.Trim(),122city,state, phone, address1,address2, zip, customer_email,123product_desc, socialsecuritynum,creditcardnumber, cardverifynum, cardexpiredate,returnedbqISN,124ref AuthCode,ref OrderNumber,ref DeclineCode,ref TermCode,ref ErrorMessage,ref ProcessorResponse,125authenticationValue,authenticationTransactionID, eci, transactiontype, PAResStatus, SignatureVerification);126break;127case"ACH":128retBilling = BillACH(whichProcessor, ip,merchant_email, grand_total, firstname, lastname,129city,state, phone, address1,address2, zip, customer_email,130product_desc, socialsecuritynum,bankname, routingnumber, accountnumber, checknumber, returnedbqISN,131ref AuthCode,ref OrderNumber,ref DeclineCode,ref TermCode,ref ErrorMessage,ref ProcessorResponse);132break;133case"PAYPAL":134retBilling = BillPayPal(ip,merchant_email,grand_total,firstname,lastname,135city,state,phone,address1,address2,zip,customer_email,136product_desc,socialsecuritynum,returnedbqISN, paypalSubAgreeID, notificationLocation,137ref strErrorNo,ref strErrorDesc,ref strTransactionId,ref strStatus,138ref strStatusCode,ref strReasonCode);139break;140}141142//insert into billingDetail143char bqRecurring ='N';144sql ="spInsertBillingDetail2";145dbCmdWrapper = db.GetStoredProcCommandWrapper(sql);146147dbCmdWrapper.AddInParameter("@.mISN",DbType.Int32,mISN);148dbCmdWrapper.AddInParameter("@.bqISN",DbType.Int32,returnedbqISN);149dbCmdWrapper.AddInParameter("@.bdamountcollected",DbType.Currency,grand_total);150dbCmdWrapper.AddInParameter("@.bdtransactioncode",DbType.String,AuthCode);151dbCmdWrapper.AddInParameter("@.bdstatus",DbType.Boolean,retBilling);152dbCmdWrapper.AddInParameter("@.bdauthcode",DbType.String,AuthCode);153dbCmdWrapper.AddInParameter("@.bdordernumber",DbType.String,OrderNumber);154dbCmdWrapper.AddInParameter("@.bdtracecode",DbType.String,returnedbqISN);155dbCmdWrapper.AddInParameter("@.email",DbType.String,customer_email);156dbCmdWrapper.AddInParameter("@.bankname",DbType.String,bankname);157dbCmdWrapper.AddInParameter("@.routingnumber",DbType.String,routingnumber);158dbCmdWrapper.AddInParameter("@.accountnumber",DbType.String,enc_accountnumber);159dbCmdWrapper.AddInParameter("@.checknumber",DbType.String,checknumber);160dbCmdWrapper.AddInParameter("@.nameoncard",DbType.String,nameoncard);161dbCmdWrapper.AddInParameter("@.cardnumber",DbType.String,enc_cardnumber);162dbCmdWrapper.AddInParameter("@.cardverifynum",DbType.Int32,cardverifynum);163dbCmdWrapper.AddInParameter("@.cardexpirationdate",DbType.DateTime,cardexpiredate);164dbCmdWrapper.AddInParameter("@.billaddress1",DbType.String,address1);165dbCmdWrapper.AddInParameter("@.billaddress2",DbType.String,address2);166dbCmdWrapper.AddInParameter("@.billcity",DbType.String,city);167dbCmdWrapper.AddInParameter("@.billstate",DbType.String,state);168dbCmdWrapper.AddInParameter("@.billzip",DbType.String,zip);169dbCmdWrapper.AddInParameter("@.billphone",DbType.String, phone);170dbCmdWrapper.AddInParameter("@.ip",DbType.String, ip);171dbCmdWrapper.AddInParameter("@.bqrecurring",DbType.String, bqRecurring);172dbCmdWrapper.AddInParameter("@.Message",DbType.String, ErrorMessage);173dbCmdWrapper.AddInParameter("@.ProcessorResponse",DbType.String, ProcessorResponse);174dbCmdWrapper.AddInParameter("@.processor",DbType.String, whichProcessor);175176dbCmdWrapper.AddInParameter("@.strErrorNo",DbType.String, strErrorNo);177dbCmdWrapper.AddInParameter("@.strErrorDesc",DbType.String, strErrorDesc);178dbCmdWrapper.AddInParameter("@.strTransactionId",DbType.String, strTransactionId);179dbCmdWrapper.AddInParameter("@.strStatus",DbType.String, strStatus);180dbCmdWrapper.AddInParameter("@.strStatusCode",DbType.String, strStatusCode);181dbCmdWrapper.AddInParameter("@.strReasonCode",DbType.String, strReasonCode);182183184Int32 returnedbdISN = 0;185returnedbdISN = (Int32)db.ExecuteScalar(dbCmdWrapper) ;186if (retBilling)187{188if (UpdateBillingQStatuses(strDBInstance,returnedbqISN,'S'))189{190return true;191}192}193else194{195if (UpdateBillingQStatuses(strDBInstance,returnedbqISN,'F'))196{197return false;198}199}200return false;201202}

Dollarjunkie:

83//insert into billingQ or update if bqISN is passed
84sql ="spInsertBillingQ";
85dbCmdWrapper = db.GetStoredProcCommandWrapper(sql);
86dbCmdWrapper.AddInParameter("@.lISN",DbType.Int32,lISN);
87dbCmdWrapper.AddInParameter("@.mISN",DbType.Int32,mISN);
88dbCmdWrapper.AddInParameter("@.email",DbType.String,customer_email);
89dbCmdWrapper.AddInParameter("@.bankname",DbType.String,bankname);
90dbCmdWrapper.AddInParameter("@.routingnumber",DbType.String,routingnumber);
91dbCmdWrapper.AddInParameter("@.accountnumber",DbType.String,enc_accountnumber);
92dbCmdWrapper.AddInParameter("@.checknumber",DbType.String,checknumber);
93dbCmdWrapper.AddInParameter("@.nameoncard",DbType.String,nameoncard);
94dbCmdWrapper.AddInParameter("@.cardnumber",DbType.String,enc_cardnumber);
95dbCmdWrapper.AddInParameter("@.cardverifynum",DbType.Int32,cardverifynum);
96dbCmdWrapper.AddInParameter("@.cardexpirationdate",DbType.DateTime,cardexpiredate);
97dbCmdWrapper.AddInParameter("@.billaddress1",DbType.String,address1);
98dbCmdWrapper.AddInParameter("@.billaddress2",DbType.String,address2);
99dbCmdWrapper.AddInParameter("@.billcity",DbType.String,city);
100dbCmdWrapper.AddInParameter("@.billstate",DbType.String,state);
101dbCmdWrapper.AddInParameter("@.billzip",DbType.String,zip);
102dbCmdWrapper.AddInParameter("@.billphone",DbType.String, phone);
103dbCmdWrapper.AddInParameter("@.ip",DbType.String, ip);
104dbCmdWrapper.AddInParameter("@.fISN",DbType.Int32, fISN);
105dbCmdWrapper.AddInParameter("@.price",DbType.Currency, grand_total);
106dbCmdWrapper.AddInParameter("@.description",DbType.String, product_desc);
107dbCmdWrapper.AddInParameter("@.bqaction",DbType.String, bqAction);
108dbCmdWrapper.AddInParameter("@.creditamount",DbType.Currency, 0);
109dbCmdWrapper.AddInParameter("@.processor",DbType.String, whichProcessor);
110returnedbqISN = 0;
111returnedbqISN = (Int32)db.ExecuteScalar(dbCmdWrapper) ;

This would seem to be the pertinent part of your code. Unfortunately you are using a custom object (dbCmdWrapper) and a custom method (AddInParameter) for building your SQL command, so it's probably going to be tough for us to help you without seeing that code. What does dbCmdWrapper look like right before you execute line 111?

|||

Hi,

From the error message, we can see that the @.bankname parameter is missing in the stored procedure execution.

In this case, you can check in your wrapper class to see if it has been added successfully.

HTH. If this does not answer you question, please feel free to mark it as Not Answered and post your reply. Thanks!