Created
August 15, 2015 16:01
-
-
Save billinkc/c7ae8aa3b69cbab20d3a to your computer and use it in GitHub Desktop.
Rough cut of where I got in passing project parameters in to an SSIS package that uses the project deployment model but isn't stored in the SSISDB
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
using System; | |
using System.Collections.Generic; | |
using System.Linq; | |
using System.Text; | |
using System.Threading.Tasks; | |
using Microsoft.SqlServer.Dts.Runtime; | |
namespace ParameterPasser | |
{ | |
/// <summary> | |
/// http://stackoverflow.com/a/26012658/181965 | |
/// </summary> | |
class MyEventListener : DefaultEvents | |
{ | |
public override void OnInformation(DtsObject source, int informationCode, string subComponent, string description, string helpFile, int helpContext, string idofInterfaceWithError, ref bool fireAgain) | |
{ | |
base.OnInformation(source, informationCode, subComponent, description, helpFile, helpContext, idofInterfaceWithError, ref fireAgain); | |
Console.WriteLine("Info in {0}/{1} : {2}", source, subComponent, description); | |
} | |
public override bool OnError(DtsObject source, int errorCode, string subComponent, string description, string helpFile, int helpContext, string idofInterfaceWithError) | |
{ | |
Console.WriteLine("Error at {0}/{1} : {2}", source, subComponent, description); | |
return false; | |
} | |
public override void OnTaskFailed(TaskHost taskHost) | |
{ | |
Console.WriteLine(taskHost.Name); | |
} | |
} | |
class Program | |
{ | |
static void Main(string[] args) | |
{ | |
string packagePath = string.Empty; | |
string isPacPath = @"C:\sandbox\so_31812951\so_31812951\bin\Development\so_31812951.ispac"; | |
string packageName = "Package.dtsx"; | |
// Has a package level parameter but no project | |
packagePath = @"C:\sandbox\so_31812951\so_31812951\Package_solo.dtsx"; | |
Application app = new Application(); | |
Package pkg = null; | |
// https://msdn.microsoft.com/en-us/library/ff930196(v=sql.110).aspx | |
Project proj = null; | |
PackageItem pi = null; | |
MyEventListener eventListener = null; | |
eventListener = new MyEventListener(); | |
/////////////////////////////////////////////////////////////////// | |
// Run an SSIS package that has a package parameter | |
/////////////////////////////////////////////////////////////////// | |
pkg = app.LoadPackage(packagePath, eventListener); | |
// This is how we specify a package parameter value | |
pkg.Parameters["PackageParam"].Value = 1; | |
var results = pkg.Execute(); | |
Console.WriteLine(results.ToString()); | |
/////////////////////////////////////////////////////////////////// | |
// Run an SSIS package that has a Project parameter | |
/////////////////////////////////////////////////////////////////// | |
proj = Project.OpenProject(isPacPath); | |
proj.Parameters["ProjectParameter"].Value = 10; | |
pi = proj.PackageItems[packageName]; | |
// Yes, I can see the project parameters in there | |
foreach (var item in proj.PackageItems) | |
{ | |
Console.WriteLine(item.StreamName); | |
} | |
pi.LoadPackage(eventListener); | |
// Loaded | |
Console.WriteLine(string.Format("> state: {0}", pi.State)); | |
// Package.dtsx | |
Console.WriteLine(string.Format("> name: {0}", pi.StreamName)); | |
// Now what? | |
// Cannot convert type 'Microsoft.SqlServer.Dts.Runtime.PackageItem' | |
// to 'Microsoft.SqlServer.Dts.Runtime.Package' via a reference conversion, | |
//boxing conversion, unboxing conversion, wrapping conversion, or null type conversion | |
//pkg = proj.PackageItems[packageName] as Package; | |
// package has parameters as does the project | |
// $Package::PackageParam | |
// $Project::ProjectParameter | |
// All the same work but now with a package that has a project parameter | |
packagePath = @"C:\sandbox\so_31812951\so_31812951\Package.dtsx"; | |
pkg = app.LoadPackage(packagePath, eventListener); | |
// The parameter "ProjectParameter" cannot be found. | |
// This error occurs when an attempt to retrieve a parameter from a | |
// parameters collection and the parameter is not there. | |
// pkg.Parameters["ProjectParameter"].Value = 10; | |
Console.WriteLine("Press any key to continue"); | |
Console.ReadKey(); | |
} | |
} | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment