Stored Procedure error when called from Entity Framework

Question

I'm trying to build a simple project that will run my stored procedure .

When I execute the SP through SQL-Server it works fine :

EXECUTE RunSSISPackage1        
  @folder_name1 = N'SSIS projects',
  @project_name1=N'CalculateReports',
  @package_name1=N'CalculateReports.dtsx',
  @param1 = N'2017-04-01 00:00:00',
  @param1name = N'startDate'

My controller:

public IActionResult Test()
{
    using (var cmd = _context.Database.GetDbConnection().CreateCommand())
    {
        cmd.CommandText = "RunSSISPackage";
        cmd.CommandType = CommandType.StoredProcedure;
        // set some parameters of the stored procedure
        cmd.Parameters.Add(new SqlParameter("@package_name1", SqlDbType.NVarChar)
        {
            Value = "N'CalculateReports.dtsx'"
        });
        cmd.Parameters.Add(new SqlParameter("@folder_name1", SqlDbType.NVarChar)
        {
            Value = "N'SSIS projects'"
        });
        cmd.Parameters.Add(new SqlParameter("@project_name1", SqlDbType.NVarChar)
        {
            Value = "N'CalculateReports.dtsx'"
        });
        cmd.Parameters.Add(new SqlParameter("@param1", SqlDbType.NVarChar)
        {
            Value = "N'2017-04-01 00:00:00'"
        });
        cmd.Parameters.Add(new SqlParameter("@param1name", SqlDbType.NVarChar)
        {
            Value = "N'startDate'"
        });
        if (cmd.Connection.State != ConnectionState.Open)
            cmd.Connection.Open();

        cmd.ExecuteNonQuery();

        return View();
    }
}

I'm getting an error :

Cannot access the package or the package does not exist. Verify that the package exists and that the user has permissions to it.

This error occurs when the parameters of package_name\folder_name\project_name is not sent correctly.

How can I pass prefix values as a parameter?

You don't need the `N'...'` wrap in your code, that's only a SQL Server thing for unicode conversion. Instead, do this: `Value = "CalculateReports.dtsx"` — DavidG, Sep 19 '17 at 14:53
@DavidG Never mind, I also passed a wrong project name :X , I hate that tiny mistakes. Thank you, it solved my problem. If you want you can post it as answer and I'll approve. — sagi, Sep 19 '17 at 14:57
Please only used needed tags. ASP.NET has absolutely nothing to do with your question, so I've removed those tags — Camilo Terevinto, Sep 19 '17 at 15:04

score 3 · Accepted Answer · answered Sep 19 '17 at 14:57

You don't need the N'...' wrap in your code, that's only a SQL Server thing for unicode conversion. For example, see this. So when you're doing that, you're actually passing the package name of N'CalculateReports.dtsx' which obviously doesn't exist.

Instead, do this Value = "CalculateReports.dtsx", for example:

cmd.Parameters.Add(new SqlParameter("@package_name1", SqlDbType.NVarChar)
{
    Value = "CalculateReports.dtsx"
});
cmd.Parameters.Add(new SqlParameter("@folder_name1", SqlDbType.NVarChar)
{
    Value = "SSIS projects"
});
cmd.Parameters.Add(new SqlParameter("@project_name1", SqlDbType.NVarChar)
{
    Value = "CalculateReports.dtsx"
});
cmd.Parameters.Add(new SqlParameter("@param1", SqlDbType.NVarChar)
{
    Value = "2017-04-01 00:00:00"
});
cmd.Parameters.Add(new SqlParameter("@param1name", SqlDbType.NVarChar)
{
    Value = "startDate"
});

Thank you. In 5 minutes I'll approve this. – sagi Sep 19 '17 at 14:59 — sagi, Sep 19 '17 at 14:59

Stored Procedure error when called from Entity Framework

1 Answers1