CODEWITHSUNDEEP
c#sql-serverssis
AutomatedChaos
AutomatedChaos

Reputation: 7500

How to find and configure required parameters for SSIS packages to execute them from code

I want to execute SSIS packages that are located on a server from C# code.

I am trying to get this running:

public void Execute()
{
    string folderName = "MYFOLDER";
    string projectName = "MYPROJECT";
    string serverName = @"MYSERVER\REGION";

    String connectionString = String.Format("Data Source={0};Initial Catalog=msdb;Integrated Security=SSPI;", serverName);

    using(SqlConnection sqlConnection = new SqlConnection(connectionString)) {
        IntegrationServices integrationServices = new IntegrationServices(sqlConnection);

        Catalog catalog = integrationServices.Catalogs["SSISDB"];
        CatalogFolder catalogFolder = catalog.Folders[folderName];

        PackageInfo package = catalogFolder.Projects[projectName].Packages[PackageName];


        PackageInfo.ExecutionValueParameterSet batchIdParameter = new PackageInfo.ExecutionValueParameterSet {
            ObjectType = package.Parameters["BatchId"].ObjectType,
            ParameterName = "BatchId",
            ParameterValue = package.Parameters["BatchId"].DesignDefaultValue
        };
        PackageInfo.ExecutionValueParameterSet dateIdParameter = new PackageInfo.ExecutionValueParameterSet
        {
            ObjectType = package.Parameters["DateId"].ObjectType,
            ParameterName = "DateId",
            ParameterValue = package.Parameters["DateId"].DesignDefaultValue
        };

        Collection<PackageInfo.ExecutionValueParameterSet> setValueParameters = new Collection<PackageInfo.ExecutionValueParameterSet>();
        setValueParameters.Add(batchIdParameter);
        setValueParameters.Add(dateIdParameter);

        long executionId = package.Execute(false, null, setValueParameters);
    }
}

When I run this code, I get an error: System.Data.SqlClient.SqlException: In order to execute this package, you need to specify values for the required parameters.
(Both parameters do have a DesignDefaultValue)

But when I look into the package through MS SQL Server Management Studio, I only see the two parameter BatchId and DateId as required parameters under the Parameters page>Parameters tab.

Why won't the package run?

Upvotes: 3

Views: 4965

Answers (1)

AutomatedChaos
AutomatedChaos

Reputation: 7500

Found it: You also have to set the reference to the Environment and provide it to the PackageInfo.Execute Method instead of using null:

EnvironmentReference environmentReference = catalogFolder.Projects[projectName].References["ProjectName", "."];

long executionId = package.Execute(false, environmentReference, setValueParameters);

Upvotes: 6

Related Questions