How to Update DataTable and Commit it to the Database

In SSIS Script Task, it seems helpful to use a select statement to consturct a DataTable, update the DataTable as necessary, and commit the DataTable back to database's table. Below is a working example of this case.

protected int updateDeathLog()
{
    int retVal = 0;
    string connString = Dts.Variables["ConnString1"].Value.ToString();
    string sql = "SELECT ObjectId, NewFileName, PageNum, TargetFileExists, TargetFileExistsDT " +
            "FROM MyTable WHERE SourceFileExists = 1 AND TargetFileExists = 0";

    using (SqlConnection conn = new SqlConnection(connString))
    {
        try
        {
            conn.Open();
            SqlCommand cmd = new SqlCommand(sql, conn);
            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            string newFileFullPath = String.Empty;

            DataTable dt = new DataTable();
            adapter.Fill(dt);
            foreach(DataRow row in dt.Rows)
            {
                // If files exists in target folder, update flags and 
                // datetime field in the DataTable.
                newFileFullPath = Dts.Variables["TargetFolderDeath"].Value.ToString()
                  + "\\" + row["NewFileName"].ToString();

                if(File.Exists(newFileFullPath))
                {
                    row["TargetFileExists"] = true;
                    row["TargetFileExistsDT"] = DateTime.Now;
                }
                else
                {
                    row["TargetFileExists"] = false;
                    row["TargetFileExistsDT"] = System.DBNull.Value;
                }
            }

            // Commit update DataTable to the MyTable.
            // Prior to the commit, set up primary keys in the DataTable 
            // and create a CommandBuilder.
            DataColumn[] keyColumns = new DataColumn[2];
            keyColumns[0] = dt.Columns["ObjectId"];
            keyColumns[1] = dt.Columns["PageNum"];
            dt.PrimaryKey = keyColumns;

            SqlCommandBuilder cmdBuilder = new SqlCommandBuilder(adapter);
            adapter.Update(dt);

            retVal = dt.Rows.Count;
        }
        catch (Exception ex)
        {
            retVal = -1;
            errorMsg = ex.Message;
            stackTrace = ex.StackTrace;
        }
    }

return retVal;
}

ActiveX Script Task in SSIS 2005 and 2008

ActiveX Script Task in SSIS requires the following library to be registered manually using regsvr32.exe on the Windows OS that runs SQL Server.

Otherwise, the SSIS package may fail when it is run as a SQL Server job.

[SQL Server 2005]
regsvr32.exe "C:\Program Files\Microsoft SQL Server\90\COM\AXSCPHST90.DLL"

[SQL Server 2008]
regsvr32.exe "C:\Program Files\Microsoft SQL Server\100\COM\AXSCPHST.DLL"

Another element of surprise to keep in mind is to try configuring the SSIS package to run in 32-bit mode. I realize that SSIS Task such as Excel Destination would only work when run in 32-bit mode. 


When you create a SQL 2008 SSIS package on 64-bit Windows 7, for example, the SSIS package defaults to 64-bit environment (as well as .NET Framework 2.0). You can change these default settings in the property section of either at the Task-scope or project-scope.


Of course, if you choose to use 32-bit environment when you complete your SSIS package, you'd have to run the SQL Server job that runs the SSIS package in 32-bit mode as well. You can set this option in the job properties window.