Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: C# .NET SQL-Server SSIS
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:
 
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 13:13pm
Vic91421
Edited 29-Apr-13 14:16pm
v2
Comments
Dave Kreskowiak at 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 at 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 at 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 at 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 at 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 at 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 at 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

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

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:
 
using Microsoft.SqlServer.Management.IntegrationServices;
using Microsoft.SqlServer.Management.Sdk.Sfc;
 
and here's the code:
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!
  Permalink  
v2

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

  Print Answers RSS
0 OriginalGriff 587
1 Sergey Alexandrovich Kryukov 479
2 Maciej Los 305
3 BillWoodruff 220
4 Mathew Soji 195
0 OriginalGriff 7,356
1 Sergey Alexandrovich Kryukov 6,817
2 DamithSL 5,461
3 Manas Bhardwaj 4,946
4 Maciej Los 4,475


Advertise | Privacy | Mobile
Web03 | 2.8.1411023.1 | Last Updated 15 May 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100