Calling SP fails second itteration

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:
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!
 
You're adding parameters in a loop, to a command outside the loop, that means increasingly many command parameters.
 
Back
Top Bottom