c# - SSIS Script Task, SqlException Handling Issue -

what trying create framework in ssis generate tab delimited utf8 text files dynamically. data source sql server, , queries output stored in physical database table, sql query plus few other parameters (e.g. file output location) passed ssis script task via variables. query executed , results streamed specified location.

this part works fine – started having few issues when attempted introduce exception handling. if there error in source sql, exception thrown , sql server error message inserted source table. again, got work, using sqlexception class. however, depending on sql error, blank 0 byte text file may or may not generated. example, syntax error in source sql thrown straight catch block, divide 0 error creates blank 0 byte text file. in both cases, source table updated error. how can avoid this? whatever error is, don’t want file generated.

this have cobbled together… noob c# , first ever stab @ c# coding if there glaringly obvious schoolboy errors in here, or if there ways can improve this, appreciate if point these out!

thanks

/*    microsoft sql server integration services script task    write scripts using microsoft visual c# 2008.    scriptmain entry point class of script. */  using system; using system.data; using microsoft.sqlserver.dts.runtime; using system.windows.forms; using system.data.sqlclient; using system.io; using system.text; //using system.data.sqlclient;   namespace st_5f2e36fd61064ad8ae13e81d97fe899e.csproj {     [system.addin.addin("scriptmain", version = "1.0", publisher = "", description = "")]     public partial class scriptmain : microsoft.sqlserver.dts.tasks.scripttask.vstartscriptobjectmodelbase     {          #region vsta generated code         enum scriptresults         {             success = microsoft.sqlserver.dts.runtime.dtsexecresult.success,             failure = microsoft.sqlserver.dts.runtime.dtsexecresult.failure         };         #endregion          public void main()         {             //establish connection data source using adonet connection manager             sqlconnection myadonetconnection = new sqlconnection();             myadonetconnection = (sqlconnection)(dts.connections["adonet"].acquireconnection(dts.transaction) sqlconnection);               //variable declarations             int reportid = (int)dts.variables["reportid"].value;             bool exportcolumnnames =(bool) dts.variables["exportcolumnnames"].value;             string sqlquery = dts.variables["query"].value.tostring();             string strfilepath = "\\\\" + path.combine(path.combine(dts.variables["outputservername"].value.tostring(), dts.variables["outputfilepath"].value.tostring()), dts.variables["outputfilename"].value.tostring());             string strcolumnnames = "";              sqldatareader reader;               using (myadonetconnection)             {                 try                 {                     using (reader = new sqlcommand(sqlquery, myadonetconnection).executereader())                     {                         //get column names query string variable                         //or use datatable instead?  better?                              (int col = 0; col < reader.fieldcount; col++)                             {                                 strcolumnnames += reader.getname(col).tostring() + "\t";                             }                             // remove final \t strcolumnname string                             strcolumnnames = strcolumnnames.remove(strcolumnnames.length - 1);                            using (streamwriter writer = new streamwriter(strfilepath)) //creates file; creates 0 byte file on divide 0 sql error...                         {                             //insert column names (only if flag on table set true)                             if (exportcolumnnames == true)                             {                                 writer.writeline(strcolumnnames);                             }                              //insert data                             while (reader.read())                             {                                 //if (reader.hasrows)                                 //{                                  stringbuilder sb = new stringbuilder(reader[0].tostring());                                  (int = 1; < reader.fieldcount; i++)                                 {                                     sb.appendformat("\t{0}", reader[i]);                                 } //end                                 writer.writeline(sb.tostring());                                  //} //end if                              } //end while                          } //end (using streamwriter)                          //reader.close();                     } //end (using reader) (closes reader)                      //update source table success                     var command = new sqlcommand("update tblanalyticsframeworkcontroltable set exportdate = getdate(), exportsuccessful = 1 id = @id", myadonetconnection);                      sqlparameter id = new sqlparameter();                     id.parametername = "@id";                     id.value = reportid;                     command.parameters.add(id);                      command.executenonquery();                      myadonetconnection.close();                  } //end try                  catch (sqlexception ex)                 {                     //insert error message table                     var command = new sqlcommand("update tblanalyticsframeworkcontroltable set exportdate = getdate(), exportsuccessful = 0, exporterrormessage = @errormessage id = @id", myadonetconnection);                      sqlparameter errormessage = new sqlparameter();                     errormessage.parametername = "@errormessage";                     errormessage.value = ex.message;                     command.parameters.add(errormessage);                      sqlparameter id = new sqlparameter();                     id.parametername = "@id";                     id.value = reportid;                     command.parameters.add(id);                      command.executenonquery();                      //remove blank file created streamwriter method (preferably without removing pre-existing valid files if possible!)                     file.delete(strfilepath);                  } //end catch                  dts.taskresult = (int)scriptresults.success;             }         }     } }