SSIS - SQL Data Type Mapping

Reference Link

SSIS Data Type	SSIS Expression	SQL Server
single-byte signed integer	(DT_I1)
two-byte signed integer	(DT_I2)	smallint
four-byte signed integer	(DT_I4)	int
eight-byte signed integer	(DT_I8)	bigint
single-byte unsigned integer	(DT_UI1)	tinyint
two-byte unsigned integer	(DT_UI2)
four-byte unsigned integer	(DT_UI4)
eight-byte unsigned integer	(DT_UI8)
float	(DT_R4)	real
double-precision float	(DT_R8)	float
string	(DT_STR, «length», «code_page»)	char, varchar
Unicode text stream	(DT_WSTR, «length»)	nchar, nvarchar, sql_variant, xml
date	(DT_DATE)	date
Boolean	(DT_BOOL)	bit
numeric	(DT_NUMERIC, «precision», «scale»)	decimal, numeric
decimal	(DT_DECIMAL, «scale»)	decimal
currency	(DT_CY)	smallmoney, money
unique identifier	(DT_GUID)	uniqueidentifier
byte stream	(DT_BYTES, «length»)	binary, varbinary, timestamp
database date	(DT_DBDATE)	date
database time	(DT_DBTIME)
database time with precision	(DT_DBTIME2, «scale»)	time(p)
database timestamp	(DT_DBTIMESTAMP)	datetime, smalldatetime
database timestamp with precision	(DT_DBTIMESTAMP2, «scale»)	datetime2
database timestamp with timezone	(DT_DBTIMESTAMPOFFSET, «scale»)	datetimeoffset(p)
file timestamp	(DT_FILETIME)
image	(DT_IMAGE)	image
text stream	(DT_TEXT, «code_page»)	text
Unicode string	(DT_NTEXT)	ntext

Decrypting the Encrypted Connection Strings

var key = Dts.Variables["SymmetricKey"].Value.ToString();
                var keyIV = Dts.Variables["SymmetricKeyIV"].Value.ToString();              

                // DB Decryption
                var encryptedDBConString = Dts.Variables["EncryptedDBConString"].Value.ToString();
                var decryptedDBConString = (new SecurityUtility(key, keyIV)).Decrypt(encryptedDBConString);
                Dts.Variables["DecryptedDBConString"].Value = decryptedDBConString;
                using (var Conn = new OleDbConnection(decryptedDBConString))
                {
                    Conn.Open();
                }

    /// <summary>
    /// This class provides helper methods for security E.G. encryption/decryption.
    /// </summary>
    internal class SecurityUtility
    {
        #region - Properties and Fields -
        /// <summary>
        /// Gets the Byte Array for the Key
        /// </summary>
        private byte[] KeyArray
        {
            get
            {
                return Convert.FromBase64String(_symmetricKey);
            }
        }

        /// <summary>
        /// Gets the Byte Array for the Key IV
        /// </summary>
        private byte[] KeyIVArray
        {
            get
            {
                return Convert.FromBase64String(_symmetricKeyIV);
            }
        }

        private string _symmetricKey;

        private string _symmetricKeyIV;
        #endregion

        #region - Methods -
        internal SecurityUtility(string symmetricKey, string symmetricKeyIV)
        {
            _symmetricKey = symmetricKey;
            _symmetricKeyIV = symmetricKeyIV;
        }

        /// <summary>
        /// Encrypts the specified plain data.
        /// </summary>
        /// <param name="plainData">The plain data.</param>
        /// <returns>The encrypted data.</returns>
        internal string Encrypt(string plainData)
        {
            // Convert the passed string to a byte array.
            byte[] toEncrypt = new ASCIIEncoding().GetBytes(plainData);
            byte[] inArray = Encrypt(toEncrypt);
            return inArray == null ? string.Empty : Convert.ToBase64String(inArray);
        }

        /// <summary>
        /// Decrypts the specified encrypted data.
        /// </summary>
        /// <param name="encryptedData">The encrypted data.</param>
        /// <returns>The decrypted data.</returns>
        internal string Decrypt(string encryptedData)
        {
            byte[] toDecrypt = Convert.FromBase64String(encryptedData);
            byte[] bytes = Decrypt(toDecrypt);
            return bytes == null ? string.Empty : Encoding.ASCII.GetString(bytes);
        }

        /// <summary>
        /// Encrypts the specified data.
        /// </summary>
        /// <param name="data">The data.</param>
        /// <returns>The encrypted data.</returns>
        private byte[] Encrypt(byte[] data)
        {
            using (MemoryStream ms = new MemoryStream(data.Length))
            {
                using (var tripleDES = new TripleDESCryptoServiceProvider())
                {
                    using (CryptoStream cs = new CryptoStream(ms, tripleDES.CreateEncryptor(KeyArray, KeyIVArray), CryptoStreamMode.Write))
                    {
                        cs.Write(data, 0, data.Length);
                        cs.FlushFinalBlock();
                        return ms.ToArray();
                    }
                }
            }
        }

        /// <summary>
        /// Decrypts the specified data.
        /// </summary>
        /// <param name="data">The data.</param>
        /// <returns>The decrypted data.</returns>
        private byte[] Decrypt(byte[] data)
        {
            using (MemoryStream ms = new MemoryStream(data.Length))
            {
                using (var tripleDES = new TripleDESCryptoServiceProvider())
                {
                    using (CryptoStream cs = new CryptoStream(ms, tripleDES.CreateDecryptor(KeyArray, KeyIVArray), CryptoStreamMode.Read))
                    {
                        ms.Write(data, 0, data.Length);
                        ms.Position = 0L;
                        string s = new StreamReader(cs).ReadToEnd();
                        return Encoding.ASCII.GetBytes(s);
                    }
                }
            }
        }
        #endregion
    }

Populating a List using LINQ

List<string> PayFrequency = new List<string>();

using (var con = new OleDbConnection(Connections.XYZDB.ConnectionString.ToString()))
            {
con.Open();
using (var cmdPF = new OleDbCommand(getPFQuery, con))
                {
                    using (var rdrPF = cmdPF.ExecuteReader())
                    {
                        PayFrequency = (from IDataRecord rPF in rdrPF
                                        select (string)rPF["ImportFrequencyCode"]
                                         ).ToList();
                    }
                }
}

Querying the List of class objects using LINQ

public string GetExceptionMessage(Int16 messageID, List<ExceptionMessage> exMsg)
        {

            var exceptionMessage = (from em in exMsg
                                    where em.ExceptionMessageID == messageID
                                    select em.ExceptionMsg).FirstOrDefault();

            return string.IsNullOrWhiteSpace(exceptionMessage) ? string.Empty : exceptionMessage;
        }

C# code to validate Date format

        public static bool IsValidDate(string inputDate, out DateTime parsedDate)
        {
            try
            {
                string changedDate = string.Empty;
                string[] DatePattern = {
                                           "yyyy.mm.dd", "mm/dd/yyyy", "yyyymmdd","mmddyyyy", "mm-dd-yyyy", "yyyy/mm/dd", "yyyy-mm-dd",
                                           "yyyy.m.dd", "m/dd/yyyy", "m-dd-yyyy", "yyyy/m/dd", "yyyy-m-dd",
                                           "yyyy.mm.d", "mm/d/yyyy","mm-d-yyyy", "yyyy/mm/d", "yyyy-mm-d",
                                           "yyyy.m.d", "m/d/yyyy", "m-d-yyyy", "yyyy/m/d", "yyyy-m-d"
                                       };

                if (DateTime.TryParseExact(inputDate, DatePattern, null, DateTimeStyles.None, out parsedDate))
                {                  
                    if (inputDate.IndexOf("-") > 0 || inputDate.IndexOf("/") > 0 || inputDate.IndexOf(".") > 0)
                    {
                        // Edge case scenario's like 09/31/2015 or 02/30/2015
                        DateTime.TryParse(inputDate, null, DateTimeStyles.None, out parsedDate);
                        if (!DateTime.MinValue.ToShortDateString().Equals(parsedDate.ToShortDateString()))
                        {
                            return true;
                        }
                    }
                    else
                    {
                        if (Convert.ToInt32(inputDate.Substring(0, 2)) <= 12)
                        {
                            changedDate = inputDate.Substring(0, 2) + "/" + inputDate.Substring(2, 2) + "/" + inputDate.Substring(4);                        
                        }
                        else
                        {                        
                            changedDate = inputDate.Substring(4, 2) + "/" + inputDate.Substring(6) + "/" + inputDate.Substring(0,4);                          
                        }
                        return DateTime.TryParse(changedDate, out parsedDate);
                    }
                }
                return false;
            }
            catch
            {
                parsedDate = DateTime.MinValue;
                return false;
            }
        }

NULL Handling in SSIS

Using Derived Column Transformation

Samples:
-----------

TRIM([CONVERSION_DATE  ]) == "" ? (DT_DATE)"1/1/1900" : (DT_DATE)TRIM([CONVERSION_DATE  ])

TRIM([CICD RESOURCE_SEQ_NUM]) == "" ? NULL(DT_I4) : (DT_I4)TRIM([CICD RESOURCE_SEQ_NUM])

TRIM([CICD USAGE_RATE_OR_AMOUNT]) == "" ? NULL(DT_NUMERIC,19,4) : (DT_NUMERIC,19,4)TRIM([CICD USAGE_RATE_OR_AMOUNT])

TRIM([BR STANDARD_RATE_FLAG]) == "" ? "-" : (DT_WSTR,1)TRIM([BR STANDARD_RATE_FLAG])

TRIM(ORGANIZATION_ID) == "" ? NULL(DT_I8) : (DT_I8)TRIM(ORGANIZATION_ID)

Tables having same column names

SELECT C.Name AS ColumnName
,  STUFF(
   (SELECT ',' + O1.Name
   FROM SYS.OBJECTS O1
   JOIN SYS.Columns C1 ON O1.object_id = C1.object_id
   WHERE C1.Name = C.Name
   FOR XML PATH('')),1,1,'') AS TableNames
FROM
SYS.OBJECTS O
JOIN SYS.Columns C ON O.object_id = C.object_id
JOIN SYS.Types T ON C.user_type_id = T.user_type_id
WHERE O.TYPE = 'U' AND C.Name like '%dead%'
GROUP BY C.Name
ORDER BY C.Name