SSIS manipulating variables and params in a script task

For the script task to be able to access varaibles and parameters the task needs to be given access to them. Do this by double clicking it and entering them as either readonly or readwrite values.

Getting and setting variables

string query = Convert.ToString(Dts.Variables["User::PrimaryNurseQuery"].Value);

Dts.Variables["User::PrimaryNurseQuery"].Value = query;

Getting parameters

string idsToExclude = Convert.ToString(Dts.Variables["$Package::PrimaryNurseIdsToExclude"].Value);

NOTE: needs the $


The whole thing:

try

{

    bool fireAgain = true;

    string idsToExclude = Convert.ToString(Dts.Variables["$Package::PrimaryNurseIdsToExclude"].Value);

       if (string.IsNullOrEmpty(idsToExclude))

       {

              idsToExclude = "'NoneFound'";

       }

    Dts.Events.FireInformation(10, "Build Primary Nurse SQL Command","Got idsToExclude as " + idsToExclude, "", 0, ref fireAgain);

       string query = Convert.ToString(Dts.Variables["User::PrimaryNurseQuery"].Value);

    query = query.Replace("'ReplaceMe'", idsToExclude);

       Dts.Variables["User::PrimaryNurseQuery"].Value = query;

       Dts.Events.FireInformation(10, "Build Primary Nurse SQL Command", "Query to use is " + query, "", 0,ref fireAgain);

       Dts.TaskResult = (int) ScriptResults.Success;

}

catch (Exception ex)

{

       MessageBox.Show(ex.ToString());

    Dts.Events.FireError(10, "Build Primary Nurse SQL Command", "Error: " + ex.ToString(), "", 0);

    Dts.TaskResult = (int) ScriptResults.Failure;

}