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
– DavidG, Commented Sep 19, 2017 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
– sagi, Commented Sep 19, 2017 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
– Camilo Terevinto, Commented Sep 19, 2017 at 15:04

DavidG · Accepted Answer · 2017-09-19 14:57:51Z

3

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"
});

answered Sep 19, 2017 at 14:57

DavidG

120k13 gold badges232 silver badges239 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

sagi Over a year ago

Thank you. In 5 minutes I'll approve this.

Collectives™ on Stack Overflow

Stored Procedure error when called from Entity Framework

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related