CODEWITHSUNDEEP
c#ssis
KorsaR
KorsaR

Reputation: 572

Passing user parameters to SSIS package

Here's my problem. I have a SSIS package that takes 1 parameter on input called SegmentID. But I cant pass it from the c# code. I'm searching for2 days so far, and here's a solution I came up with, which should work, but doesn't - SSIS package fails at the stage of getting parameter. What a hell am I doing wrong?

        using (SqlConnection con = GetConnection())
        {
            var integrationServices = new IntegrationServices(con);

            if (integrationServices.Catalogs.Contains("SSISDB"))
            {
                var catalog = integrationServices.Catalogs["SSISDB"];

                if (catalog.Folders.Contains("PSO"))
                {
                    var folder = catalog.Folders["PSO"];

                    if (folder.Projects.Contains("PSO_SSIS"))
                    {
                        var project = folder.Projects["PSO_SSIS"];

                        project.Parameters["SegmentID"].Set(ParameterInfo.ParameterValueType.Literal, segmentID);

                        if (project.Packages.Contains("Main.dtsx"))
                        {
                            var package = project.Packages["Main.dtsx"];

                            long executionIdentifier = package.Execute(false, null);
                            return catalog.Executions[executionIdentifier];
                        }
                    }
                }
            }
        }

Upvotes: 2

Views: 4472

Answers (1)

KorsaR
KorsaR

Reputation: 572

Thanks, guys, but i figured it out. You have to do something like this:

var setValueParameters = new Collection<PackageInfo.ExecutionValueParameterSet>();
                            setValueParameters.Add(new PackageInfo.ExecutionValueParameterSet
                            {
                                ObjectType = 20,
                                ParameterName = "SegmentID",
                                ParameterValue = 1
                            });

and then call:

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

Why it confused me so much is that I thought that this thing is just for system parameters, like logging level or something, and NOWHERE was mentioned that the code for user parameters is 20, and this construction can be used for assigning them. Hope it'll help somebody to avoid butthurt I had for 2 days.

Upvotes: 7

Related Questions