Super_Grover
New member
- Joined
- Nov 24, 2016
- Messages
- 2
- Programming Experience
- 3-5
Hi all, I'll try to make a brief description of my issue:
I'm executing SSIS packages through a stored procedure. I call the SP in my C# application.
The SP has one input parameter: the name of the package. It has one output parameter: execution status.
I itterate through a datagridview which holds the package names. Package name goes into variable, which becomes the input parameter of the SP.
All works fine if I select only 1 package and thus call the stored procedure only one time.
But when I select more than 1 package: it does the first run just fine and on the 'ExecuteNonQuery()' it crashes and says I pass too many arguments. How is that possible? It's the exact same code...
Here's the C# code:
Here's stored procedure (generated ALTER SP code):
ANy ideas to help me out? Many thanks in advance!
I'm executing SSIS packages through a stored procedure. I call the SP in my C# application.
The SP has one input parameter: the name of the package. It has one output parameter: execution status.
I itterate through a datagridview which holds the package names. Package name goes into variable, which becomes the input parameter of the SP.
All works fine if I select only 1 package and thus call the stored procedure only one time.
But when I select more than 1 package: it does the first run just fine and on the 'ExecuteNonQuery()' it crashes and says I pass too many arguments. How is that possible? It's the exact same code...
Here's the C# code:
C#:
private void button2_Click(object sender, EventArgs e)
{
string connetionString = null;
SqlConnection connection;
SqlCommand command = new SqlCommand();
Cursor.Current = Cursors.WaitCursor;
dataGridView1.ClearSelection();
DateTime Starttime = DateTime.Now;
DateTime EndTime = DateTime.Now;
connetionString = "Data Source=NLDSLASSQLTST;Initial Catalog=CS_ConversieAanlevering;Trusted_Connection=True";
connection = new SqlConnection(connetionString);
foreach (DataGridViewRow rw in dataGridView1.Rows)
{
if (Convert.ToBoolean(rw.Cells[0].Value) == true)
{
string ls_duur = string.Empty;
var result=(Object)null;
gs_pckName = rw.Cells[2].Value.ToString() + ".dtsx";
command.Connection = connection;
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "sp_RunPCK_var";
command.Parameters.Add("@PckName", SqlDbType.VarChar).Value = gs_pckName;
var returnParameter = command.Parameters.Add("@ReturnVal", SqlDbType.Int);
returnParameter.Direction = ParameterDirection.ReturnValue;
adapter = new SqlDataAdapter(command);
command.CommandTimeout = 0;
try
{
connection.Open();
command.ExecuteNonQuery();
result = returnParameter.Value;
EndTime = DateTime.Now;
TimeSpan Timediff = EndTime - Starttime;
ls_duur = (Timediff.ToString()).Substring(0, 8);
rw.Cells[1].Value = "OK";
rw.Cells[3].Value = ls_duur;
rw.Cells[1].Style.BackColor = Color.LightGreen;
}
catch (SqlException ex)
{
SaveLog(DateTime.Now, gs_pckName, "Err", ex.ToString());
}
finally
{
connection.Close();
}
SaveLog(EndTime, gs_pckName, result.ToString(), "Package execution voltooid in: " + ls_duur);
if (Convert.ToInt32(result) != 0)
{
break;
}
}
}
Cursor.Current = Cursors.Default;
}
Here's stored procedure (generated ALTER SP code):
C#:
ALTER PROCEDURE [dbo].[sp_RunPCK_var]
@PckName varchar(50)
AS
BEGIN
DECLARE @intExecutionID bigint
DECLARE @synchronized BIT = 1
EXEC [SSISDB].catalog.create_execution 'AgendaTest','runAgendaTest', @PckName,NULL,0,@intExecutionID OUTPUT
EXEC [SSISDB].[catalog].[set_execution_parameter_value]
@intExecutionID
, @object_type = 50
, @parameter_name = N'SYNCHRONIZED'
, @parameter_value = 1
EXEC [SSISDB].[catalog].[start_execution] @intExecutionID
END
GO
ANy ideas to help me out? Many thanks in advance!