Changing SSIS package in C# causing LoadFromXML fail

Question

I am attempting to create a copy of an existing dtsx file so I can change a few variables based on input from the user. I am able to make a copy of the file, look at the variables, and set the variables to the correct input. However, when I go to look at the file in Visual Studio I get a few errors.

Microsoft Visual Studio is unable to load this document: The package failed to load to to error 0xC0010014 "One or more error occurred. There should be more specific errors preceding this one that explains the details of the errors. This message is used as a return value from functions that encounter errors". This occurs when CPackage::LoadFromXML fails.

The errors contained in the error list: Error 3 Error loading test.dtsx: Error loading value "<DTS:Property xmlns:DTS="www.microsoft.com/SqlServer/Dts" DTS:Name="PackageFormatVersion">6</DTS:Property>" from node "DTS:Property". E:\test.dtsx 1 1

The version number in the package is not valid. The version number cannot be greater than current version number.

I looked into these errors and I saw potential issues with the server year and visual studio year. Both of these are the 2008 version. My code:

        string pkgPath = @"\\server\TestFolder\test.dtsx"            
        app = new Microsoft.SqlServer.Dts.Runtime.Application();
        pkg = app.LoadPackage(pkgPath, null);

        Console.WriteLine(pkg.Variables["filename"].Value.ToString());
        pkg.Variables["filename"].Value = "testFile";
        Console.WriteLine(pkg.Variables["filename"].Value.ToString());

        app.SaveToXml(pkgPath, pkg, null);

If I open the file I am using to make a copy of in Visual Studio, it works no problem -- something strange is happening when I do app.SaveToXML();

Any ideas or suggestion would be wonderful.

Could you try solving this problem by using DtsProperty objects? I haven't tried it, but it should be similar to the /Set parameter for dtexec.exe: https://msdn.microsoft.com/en-us/library/microsoft.sqlserver.dts.runtime.dtsproperty(v=sql.120).aspx — Mark Wojciechowicz, Jul 07 '15 at 13:27
@MarkWojciechowicz I will check that out. The information of achieving this task seem to be nonexistent — Kevin Mee, Jul 07 '15 at 13:29
Btw, I think the encoding might be changing from UTF8 to UTF16. I have had a similar problem with ssis configured files in an automated deployment process in the past. — Mark Wojciechowicz, Jul 07 '15 at 21:25
@MarkWojciechowicz is there a way to specify the encoding to prevent this? — Kevin Mee, Jul 08 '15 at 12:34
I can't guarantee that this is the issue, but you can give it a go: http://stackoverflow.com/questions/7125050/change-the-encoding-to-utf-8-on-a-stream-memorymappedviewstream — Mark Wojciechowicz, Jul 09 '15 at 00:11
@MarkWojciechowicz I am not sure if that is the issue. I find it strange that changing some values and saving it is messing up with it. I have tried from VS 2013 which I think is where the issue is happening though I am not sure why because I am not trying to run the package or anything just making modifications — Kevin Mee, Jul 09 '15 at 13:59
If nothing else works, execute dtexec as a process and use the /set parameter to change property values — Mark Wojciechowicz, Jul 09 '15 at 14:19
@MarkWojciechowicz How would you do that? I have never done anything like that before but willing to try! — Kevin Mee, Jul 09 '15 at 14:20

score 2 · Accepted Answer · answered Jul 09 '15 at 14:50

To run this as a process from DTEXEC, it would look something like below. Please check out these two links for further details about ProcessStartInfo and how to use /SET so you can add that to your argument. Test this from the command line first because the syntax can be finicky.

https://msdn.microsoft.com/en-us/library/system.diagnostics.processstartinfo(v=vs.110).aspx

https://technet.microsoft.com/en-us/library/ms162810(v=sql.105).aspx

Using System.Diagnostics;

string args = @"/F'c:\MyPackage.dtsx' /SET'\package.variables[myvariable].Value;myvalue'";
ProcessStartInfo executePackage = new ProcessStartInfo("dtexec", args);
executePackage.UseShellExecute = false;
executePackage.RedirectStandardError = true;
executePackage.RedirectStandardOutput = true;
executePackage.CreateNoWindow = true;
StringBuilder output = new StringBuilder();

Process executing = Process.Start(executePackage);
while(!executing.StandardOutput.EndOfStream)
{
    output.AppendLine(executing.StandardOutput.ReadLine();
}
executing.WaitForExit():

Thank you for this.. I am working with this to try and achieve my goal. I am marking it as accepted because it seems to do what I need to. Thank you — Kevin Mee, Jul 09 '15 at 20:10

Changing SSIS package in C# causing LoadFromXML fail

1 Answers1