Skip to content

Instantly share code, notes, and snippets.

@billinkc
Created August 15, 2015 16:01
Show Gist options
  • Save billinkc/c7ae8aa3b69cbab20d3a to your computer and use it in GitHub Desktop.
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
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