/* Microsoft SQL Server Integration Services Script Component * Write scripts using Microsoft Visual C# 2008. * ScriptMain is the entry point class of the script.*/ using System; using System.Data; using Microsoft.SqlServer.Dts.Pipeline.Wrapper; using Microsoft.SqlServer.Dts.Runtime.Wrapper; using System.Data.SqlClient; [Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute] public class ScriptMain : UserComponent { IDTSConnectionManager100 connMgr; SqlConnection sqlConn; SqlCommand sqlCmd; SqlParameter sqlParam; public override void AcquireConnections(object Transaction) { connMgr = this.Connections.ADOTestDBConnection; sqlConn = (SqlConnection)connMgr.AcquireConnection(null); } public override void PreExecute() { sqlCmd = new SqlCommand("INSERT INTO NullDefenseReplica(FirstName, LastName, DOB) VALUES(@FirstName, @LastName, @DOB)", sqlConn); sqlParam = new SqlParameter("@FirstName", SqlDbType.VarChar, 50); sqlCmd.Parameters.Add(sqlParam); sqlParam = new SqlParameter("@LastName", SqlDbType.VarChar, 50); sqlCmd.Parameters.Add(sqlParam); sqlParam = new SqlParameter("@DOB", SqlDbType.DateTime); sqlCmd.Parameters.Add(sqlParam); } public override void PostExecute() { base.PostExecute(); /* Add your code here for postprocessing or remove if not needed You can set read/write variables here, for example: Variables.MyIntVar = 100 */ } public override void Input0_ProcessInputRow(Input0Buffer Row) { //This is first set of code without NULL Defense //sqlCmd.Parameters["@FirstName"].Value = Row.FirstName; //sqlCmd.Parameters["@LastName"].Value = Row.LastName; //sqlCmd.Parameters["@DOB"].Value = Row.DOB; //This is second set of code with NULL Defense if (Row.FirstName_IsNull) { sqlCmd.Parameters["@FirstName"].Value = DBNull.Value; } else { sqlCmd.Parameters["@FirstName"].Value = Row.FirstName; } if (Row.LastName_IsNull) { sqlCmd.Parameters["@LastName"].Value = DBNull.Value; } else { sqlCmd.Parameters["@LastName"].Value = Row.LastName; } if (Row.DOB_IsNull) { sqlCmd.Parameters["@DOB"].Value = DBNull.Value; } else { sqlCmd.Parameters["@DOB"].Value = Row.DOB; } //This remains for both first or second set of code sqlCmd.ExecuteNonQuery(); } }