Purpose: I have an SSIS Solution with various packages, one set of these packages is currently created with a package per table due to them having different structures. The aim is to create a template package (done), update variables/table names (done), reinitialise and re-map columns, execute package for each table.
Problem: So, as you can tell, I'm up to the point that i need to reinitialise but I am unable to get to the data flow task and keep getting this error:
No such interface supported (Exception from HRESULT: 0x80004002 (E_NOINTERFACE))
When I run this code:
//// Get Data Flow task
TaskHost tHost = pkgOut.Executables[0] as TaskHost;
MainPipe dataFlowTask = (MainPipe)tHost.InnerObject;
The code that I am using is below:
public void Main()
{
// Set Project Variables
List<string> pkgVars = new List<string>
{
@"pPR_SSIS_Catalog_Server",
@"pPR_SSIS_Catalog_Project",
@"pPR_SSIS_Catalog_Folder"
};
// Get Package
String pkgLocation = @"C:\PATH\TO\TEMPLATE\PACKAGE\a_Load_SAP_Template.dtsx";
Application app = new Application();
Package pkgIn = app.LoadPackage(pkgLocation, null);
String pkgName = "DynamicPackage";
// Add Connections (cos they're project connections and aren't stored in package)
ConnectionEnumerator allConns = Dts.Connections.GetEnumerator();
while ((allConns.MoveNext()) && (allConns.Current != null))
pkgIn.Connections.Join(allConns.Current);
// Convert new package to XML so we can cheat and just do a find and replace
XmlDocument pkgXML = new XmlDocument();
pkgIn.SaveToXML(ref pkgXML, null, null);
// Replace strings
// Set SAP table
String pkgStr = pkgXML.OuterXml.ToString();
pkgStr = pkgStr.Replace("#SAP_SRC_TABLE#", "MyF-ingSAPTables"); // Replace SAP Table Name
// Set Project Variables references == values -- REMEMBER TO CHECK FOR INT PARAMS zzz
foreach (string var in pkgVars)
pkgStr = pkgStr.Replace(@"@[$Project::" + var + @"]", @"""" + Convert.ToString(Dts.Variables[var].Value) + @"""");
// Convert back to XML
XmlDocument newXML = new XmlDocument();
newXML.LoadXml(pkgStr);
Package pkgOut = new Package();
pkgOut.LoadFromXML(newXML, null);
//// Get Data Flow task
TaskHost tHost = pkgOut.Executables[0] as TaskHost;
MainPipe dataFlowTask = (MainPipe)tHost.InnerObject; // THIS IS WHERE THE CODE ERRORS
new Application().SaveToXml(String.Format(@"D:\PATH\TO\SAVE\LOCATION\{0}.dtsx", pkgName), pkgOut, null);
Dts.TaskResult = (int)ScriptResults.Success;
}
I have tried:
- Removing everything but the data flow
- Recreated a blank data flow
- Recreated the package
- Made a package in the script and created the data flow (this works but when i open the package, the data flow does not appear...might have added it wrong but it can see it when I run the TaskHost section)
I am not sure what else I can try, I have seen information that I might need to re-register some .dlls but I do not have admin access and I'd prefer not to go through the hassle for something where I do not know whether it will work.
Any help would be appreciated.
Thanks,
