September 9, 2015 at 11:30 am
hello,
I am trying to wriet a c# code in script task.
Objective: Connect to Db2 Database through c# and run the storedprocedure.
Parameters are declared as variables
1) Startdate , Enddate at pacakge level . Datatype as string
2) Connection string declared as variable "connection" at package level
3) To store result ,"Result" as type object.
Question: How to pass package variables as parameters here while calling the stored procedure ? ( I am bad at c# but i trying to write the code below)
My script:( not complete)
public void Main()
{
Dts.TaskResult = (int)ScriptResults.Success;
}
private void callDB2StoredProcedure()
{
OleDbConnection connection = new OleDbConnection();
connection.ConnectionString = Dts.Variables["@connection"].Value.ToString() ; /***** passing connection string as varaible ***/
OleDbCommand cmd = new OleDbCommand();
cmd.CommandText = "call SP_STATUS (<input parameter>,?,?,?)"; /**** how to pass package variables as paramters here ****/
cmd.Connection = connection;
OleDbDataAdapter dtAdapter = new OleDbDataAdapter(cmd);
DataTable dtTable = new DataTable();
connection.Open();
dtAdapter.Fill(dtTable);
dtTable.AcceptChanges();
Dts.Variables["vResultSet"].Value = dtTable;
connection.Close();
dtAdapter.Dispose();
dtTable.Dispose();
cmd.Dispose();
connection.Dispose();
}
September 10, 2015 at 12:45 am
Quick suggestion, use the Parameters (OleDbParameterCollection) collection which is a member of the OleDbCommand Class.
😎
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply