How do I pass DTEXEC utility command line argument

Question

I have to pass parameters to DTEXEC utility through ASP.NET code. I am using the process.start() method to trigger the SSIS package execution.

String 1

dtexec /file C:\ssis\pkg1.dtsx 
       /conn "MyConnectionManager";"\"Data Source=localhost\TestSQL2008R2;Initial Catalog=ConnDB;Integrated Security=SSPI;\""

String 2

/file C:\ssis\pkg1.dtsx 
/conn "MyConnectionManager;Data Source=localhost\TestSQL2008R2;Initial Catalog=ConnDB;Integrated Security=SSPI;"

The above command line arguments are generated while manually executing the package through DTEXEC utility. However, I need to pass through this command line to process.start() method in ASP.NET using C#.

How can I represent the above command line within C# string statement? In other words, how do I escape the special characters by making use of @ character and pass a valid statement to process.start()?

Answer 1

I didn't test these, but the following should do it.

var string1 = 
"dtexec /file C:\\ssis\\pkg1.dtsx /conn \"MyConnectionManager\";\"\\\"Data Source=localhost\\TestSQL2008R2;Initial Catalog=ConnDB;Integrated Security=SSPI;\\\"\"";

var string2 = "dtexec /file C:\\ssis\\pkg1.dtsx /conn \"MyConnectionManager;Data Source=localhost\\TestSQL2008R2;Initial Catalog=ConnDB;Integrated Security=SSPI;\"";

If you go the old fashioned route like I've shown above, you just need to escape \ (because that IS your escape char) and " with a \ anywhere in the string. If you use ver batim like the other answer suggests, it will be easier to read and maintain.

Answer 2

A verbatim string literal is probably easiest to use for hard coding such strings.

You only need to double up every inner ":

@"dtexec /file C:\ssis\pkg1.dtsx /conn ""MyConnectionManager"";""\""Data Source=localhost\TestSQL2008R2;Initial Catalog=ConnDB;Integrated Security=SSPI;\""""";

And:

@"/file C:\ssis\pkg1.dtsx /conn ""MyConnectionManager;Data Source=localhost\TestSQL2008R2;Initial Catalog=ConnDB;Integrated Security=SSPI;""";

With "regular" strings, you would need to escape each " and \ in the original with a \ before them.

Answer 3

At the risk of a downvote for being off topic, you have a better mechanism than calling the dtexec executable. Since you're in .net, use the existing object model. It's far more powerful than the limited abstraction provided through dtexec.

Rough cut of the code would look like

string packagePath = @"C:\ssis\pkg1.dtsx";
string connectionString = @"Data Source=localhost\TestSQL2008R2;Initial Catalog=ConnDB;Integrated Security=SSPI;"
Application app = new Application();
Package pkg = null;
pkg = app.LoadPackage(packagePath, null);
pkg.Connections["MyConnectionManager"].ConnectionString = connectionString;
pkg.Execute();