SSIS SCRIPT TASK USING C#

[EDN Admin]

Hi All ,
I want to pass Forloop container variable value in to my SQL Query so that I can pull the data from my table ,
the for loop container variable increments 4 times and stops so my query is like this

select Patientname,patientemail from table where @PatientId=@Forloopcontainervariable

@Forloopcontainer variable increments 4 times so I get 4 patients from the table thats the logic I worte a code but I go this error the variable is not declared

Here is the code all I want is to take the variable value from @K in to the SQL Query so that I can increment each time one after the other so I can send 4 emails to 4 patients.
can any one help
<pre class="prettyprint /*
Microsoft SQL Server Integration Services Script Task
Write scripts using Microsoft Visual C# 2008.
The ScriptMain is the entry point class of the script.
*/
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.Data;
using System.Data.SqlClient;
namespace ST_c967da9a25bd418f8dece721f33d5c0f.csproj
{
[System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{
#region VSTA generated code
enum ScriptResults
{
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
};
#endregion
/*
The execution engine calls this method when the task executes.
To access the object model, use the Dts property. Connections, variables, events,
and logging features are available as members of the Dts property as shown in the following examples.
To reference a variable, call Dts.Variables["MyCaseSensitiveVariableName"].Value;
To post a log entry, call Dts.Log("This is my log text", 999, null);
To fire an event, call Dts.Events.FireInformation(99, "test", "hit the help message", "", 0, true);
To use the connections collection use something like the following:
ConnectionManager cm = Dts.Connections.Add("OLEDB");
cm.ConnectionString = "Data Source=localhost;Initial Catalog=AdventureWorks;Provider=SQLNCLI10;Integrated Security=SSPI;Auto Translate=False;";
Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.

To open Help, press F1.
*/
public void Main()
{

SqlConnection Con = new SqlConnection
("Data Source=3Z3CP4J;Initial Catalog=AE_SSIS_EMAIL;Integrated Security=true");
{
int @k;
@k= (int)Dts.Variables["User::Counter"].Value;
MessageBox.Show(k.ToString());

Con.Open();
using (SqlCommand cmd = new SqlCommand
("DECLARE @Patientname varchar(30),@Patientfeed varchar(3),@PatientEmail varchar(30)select @Patientname=(select PatientName from dbo.AE_FEEDBACK where FeedBack_ID=@k)select @Patientfeed=(select PatientFeedback from dbo.AE_FEEDBACK where FeedBack_ID=@k)select @PatientEmail=(select PatientEmail from dbo.AE_FEEDBACK where FeedBack_ID=@k)select @Patientname as PatientName,@Patientfeed as Feedback,@PatientEmail as Email", Con))



{

SqlDataReader reader = cmd.ExecuteReader();
SqlParameterCollection SqlParams = cmd.Parameters;
SqlParams.AddWithValue("@k", @k);
while (reader.Read())
{
string a = (string)reader[0];
string b = (string)reader[1];
string c = (string)reader[2];
Dts.Variables["User:atientName"].Value = a;
Dts.Variables["User:atientFeed"].Value = b;
Dts.Variables["User:atientEmail"].Value = c;
MessageBox.Show(Dts.Variables["User:atientName"].Value.ToString());
MessageBox.Show(Dts.Variables["User:atientFeed"].Value.ToString());
MessageBox.Show(Dts.Variables["User:atientEmail"].Value.ToString());
string d = "Y";
string e = "N";
if (b == "Y")
{
Dts.Variables["User::Condition"].Value = 1;
}
else if (b == "N")
{
Dts.Variables["User::Condition"].Value = 2;
}




}
}
}
}
}
}[/code]
<br/>

<hr class="sig Sri.Tummala

View the full article