Get rid of extra double quotes in the text column of flat file

start = 0;
// fix extra " chars, like ["some text " with quote in the middle"]
while ((pos = line.IndexOf('"', start)) != -1)
{
   // if not first and last char
   if( (pos != 0) && (pos != line.Length-1))
   {
      if ((line.Substring(pos, 2) == "\"" + delimiter))  // like "|
         justStop = true; // stop and skip
      else if ((line.Substring(pos - 1, 2) == delimiter + "\""))  // like |"
         justStop = true; // stop and skip

      // If two Double Quotes in the middle, drop both
      else if ((line.Substring(pos, 2) == "\"\""))  // like ""
      {
         // need to check that it is NOT ""| where we can drop both " and leave the text not closed with "
         if (pos < line.Length -2) // safe to sub 3 chars, like ""|CRLF
         {
            if ((line.Substring(pos, 3) == "\"\"" + delimiter))
               line = line.Remove(pos, 1);  // remove JUST ONE double quote char
            else // not before | then drop two
               line = line.Remove(pos, 2);  // remove TWO double quote chars
         }
         else // it is ""CRLF where we drop just one "
            line = line.Remove(pos, 1);  // remove JUST ONE double quote char
     
         isAnyDoubleQuotes = true;
      }

      // just single Double Quote
      else
      {
         line = line.Remove(pos, 1);  // otherwise remove double quote char
         isAnyDoubleQuotes = true;
      }
   }

   start = pos + 1;
}

Fire Error event in case of Connection error

try
{
    SqlConnection conn = (Dts.Connections[adonConn].AcquireConnection(null) as SqlConnection);
    SqlCommand cmd = new SqlCommand(sql, conn);
    ....
}
catch (Exception ex)
{
    string text = string.Format("Failed to acquire connection to '{0}-[{1}]'. Error Message='{2}'"
        , adonConn
        , Dts.Connections[adonConn].ConnectionString
        , ex.Message).Replace(Environment.NewLine, "");
    Dts.Events.FireError(-1, "", text, "", 0);
}

Ho to fire Event from Script component

bool fireAgain = true;
IDTSComponentMetaData100 metaData;
metaData = this.ComponentMetaData;
metaData.FireError(-1, "", text, string.Empty, 0, ref fireAgain);

Neutral dateformat for SQL Server for data load and presentation

Working a lot with loading SAP flat files on Chevron AuditDAD project we came into deeper need to understand some universal, language/culture neutral dateformat to use with SQL Server.

Classic format proposed by MS was yyyymmdd (DECLARE @d datetime = '20140101'; ). It was true (and is) for SQL Server 2008+ and before.
'yyyy-mm-dd' format works ok as neutral format for SQL Server 2008+ (date, datetime2 types), but is language dependent for datetime type (SQL Server 2005).
If we add a time part, then it is tough to find the right format to use.

Browsing the network we found an interesting format which is supported by SQL Server and can be safety used. It is ISO 8601. yyyy-mm-ddThh:mm:ss[.mmm] (no spaces) format. T means Time part.

And SQL Server CONVERT function knows that standard to generate string presentation of date/datetime values:
   SELECT CONVERT( nvarchar(30), SYSDATETIME(), 126 ) AS [iso8601];

and opposite is true and acceptable to convert from literal version to datetime type:
   DECLARE @d datetime2 = '2014-01-01T13:02:35'; 

Cluster Indexes from Kimberly

http://www.sqlskills.com/blogs/kimberly/more-considerations-for-the-clustering-key-the-clustered-index-debate-continues/

Interesting reading about Indexes in scope of finding the duplicate ones

http://www.sqlskills.com/blogs/kimberly/how-can-you-tell-if-an-index-is-really-a-duplicate

Get, Set variable methods to make code easily readable

1. Intead of declaring variables in Script task, when you always forget to declare them and then have errors during runtime - forget declaring, use easy and nice way

// No need to declare READ, WRITE variable and short form of accessing
private T Get<T>(string varName)
{
    Variables lockedVariables = null;
    T var;
    Dts.VariableDispenser.LockOneForRead(varName, ref lockedVariables);
    var = (T)lockedVariables[varName].Value;
    lockedVariables.Unlock();
    return var;
}

private void Set<T>(string varName, T value)
{
    Variables lockedVariables = null;
    Dts.VariableDispenser.LockOneForWrite(varName, ref lockedVariables);
    lockedVariables[varName].Value = value;
    lockedVariables.Unlock();
}

2. Here is example code to use variables

string someName = Get<string>("User::someName");
int someInt = Get<int>("User::someInt");
Set("User::content", "some text");
// OR 
Set<string>("User::content", "some text");