Providing environment variables to package execution using PackageInfo class

Question

0.00/5 (No votes)

See more:

I've been looking around the web for days now and I haven't been able to find a single example on how to pass environment variables to a package.

We're using the new ssis project development to deploy packages to the SSIS catalog so I need to use the Microsoft.SQLServer.Management.IntegrationServices assembly to access packages. I found an excelent example on how to run it here. The problem is, every one of our packages (more than 100) have around 7 parameters they need to execute and I can't provide values for them! Not even hard-coded values. It's driving me insane. I keep getting the Reference not made to an instance of an object error because apparently the PackageInfo class is static. For this project, there are different environments configured in the SSIS Catalog that I need to map to the variables of the packages.

I've tried setting the parameters like they set the SYNCHRONIZED parameter on the example, but to no effect. For some variables I get an error saying that I don't have sufficient permissions to access them, which makes sense but I can't figure out how (or where) to provide the needed credentials (It might be good to mention that we've used windows authentication until now, configuring permissions to the domain users that are working on the project).

I am stuck. Has anybody done this? I know its really easy to set the variables the old way (Dts.Runtime assembly) but this new assembly is poorly documented and very few examples are available on the web (none of which touch the subject of environment variables). Any help would be gratefully accepted!

[EDIT]

This is the code I'm using:

C#

SqlConnection connection = new SqlConnection(@"Data Source=my-ssis-server;Initial Catalog=MyDB;Integrated Security=SSPI;");
IntegrationServices ssis = new IntegrationServices(connection);

//Get handle on package
PackageInfo pk = ssis.Catalogs["SSISDB"].Folders["MyFolder"].Projects["MyProject"].Packages["MyPackage.dtsx"];
var executionParameter = new Collection<PackageInfo.ExecutionValueParameterSet>();

executionParameter.Add(new PackageInfo.ExecutionValueParameterSet
   {
   	   ObjectType = 50,
   	   ParameterName = "SYNCHRONIZED",
   	   ParameterValue = 1
   });

//This throws: The parameter 'MyParam' does not exist or you do not have sufficient permissions.
/*executionParameter.Add   
   (new PackageInfo.ExecutionValueParameterSet
   {
       ObjectType = 50,
       ParameterName = "MyParam",
       ParameterValue = "myvalue"
   });*/

//This throws: Object reference not set to an instance of an object.
pk.Parameters["MyParam"].Set(
   ParameterInfo.ParameterValueType.Literal,
   "MyValue"
);

pk.Alter();

long executionIdentifier = pk.Execute(false, null, executionParameter);

Posted 29-Apr-13 12:13pm

Vic91

Updated 29-Apr-13 13:16pm

v2

Add a Solution

Comments

Dave Kreskowiak 29-Apr-13 18:41pm

Without seeing the code that your using to launch a job, it's pretty much impossible to tell you what you're doing wrong.

What do you mean by "I can't provide values for them!"?

"Reference ... an instance of an object" means that you're trying to get/set a property or call a method on an null object. You're code is assuming that something is being returned and you're tryingo to use it when null was returned.

Vic91 29-Apr-13 19:17pm

I included the link to the example I'm refering to but I added the code anyways. Still, I don't yet have code for passing on envirenment variables.

Dave Kreskowiak 29-Apr-13 23:58pm

You have the code for passing in a value.

You just need the value to pass in from the Environment, which is easily done with Environment.GetEnvironmentVariable("variableName").

Vic91 30-Apr-13 19:32pm

And where do I asign this value? I mean, where do I specify to the package which environment to use? Could you expand on where do I find this and how to use it?
I tried accessing it like this.
EnvironmentInfo env = ssis.Catalogs["SSISDB"].Folders["MyFolder"].Environments["MyEnvironment"]
but this doesn't seem to have that method. And even if I get the values I still can't pass them on to the package because I'm accessing it remotely maybe...

Dave Kreskowiak 30-Apr-13 19:40pm

You already have the code. See the executionParameter junk?? I suggest you start reading the documentation on the PackageInfo.

Vic91 30-Apr-13 19:57pm

Oh! I get it. Thanks. But do you have any idea why I can't access the parameters? I know it has something to do with security, but I tried adding a user and password to the connection string and the parameters are still not visible.
The parameter 'MyParam' does not exist or you do not have sufficient permissions.

I opened up the project on SSDT and saw that the parameters are configured at project level and not package level, so it kind of makes sense for them not show up, but then, how is it asking for them?

Dave Kreskowiak 1-May-13 0:35am

I have no idea. It's been a VERY long time since I'vehad to do anything with SSIS.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Vic91 · Accepted Answer · 2013-05-15T11:01:00

I finally solved this! The problem was that I was trying to set the parameter values individually, when actually these where defined project-wise. All I had to do was give the execution an EnvironmentReference object and voilà!

These are the assembly references:

C#

using Microsoft.SqlServer.Management.IntegrationServices;
using Microsoft.SqlServer.Management.Sdk.Sfc;

and here's the code:

C#

SqlConnection connection = new SqlConnection(@"Data Source=my-ssis-server;Initial Catalog=SSISDB;Integrated Security=SSPI;");
//ssis object
IntegrationServices ssis = new IntegrationServices(connection);
 
//Get handle on package
PackageInfo pk = ssis.Catalogs["SSISDB"].Folders["MyFolder"].Projects["MyProject"].Packages["MyPackage.dtsx"];
var executionParameter = new Collection<packageinfo.executionvalueparameterset>();
//Make the execution synchronous
executionParameter.Add(new PackageInfo.ExecutionValueParameterSet
   {
   	   ObjectType = 50,
   	   ParameterName = "SYNCHRONIZED",
   	   ParameterValue = 1
   });

//GET HANDLE ON ENVIRONMENT REFERENCE (References.["Name", "FolderName"])
EnvironmentReference re = ssis.Catalogs["SSISDB"].Folders["MyFolder"].Projects["MyProject"].References["Development", "MASTER"];

//run package
long execution_id = pk.Execute(false, re, setValueParameters);
            
Console.WriteLine(execution_id.ToString());
Console.Read();

I think it's very interesting that there's no documentation of this; none that I could find at least (and believe me, I really looked!), and the examples I read on the internet didn't include this kind of scenario. I hope this helps someone and avoids them a couple of days of research!