Overriding SSIS Configuration Connection String in .NET

Question

I am writing an application that programatically imports user selected flat files into a database (with other logic, so I really have to use SSIS). I have the list of SSIS packages in a table and load the package and execute it in code. However my SSIS packages are using design time parameters to lookup their configuration in another database server (QA, in this case) rather than runtime config parameters.

How can I override the connection string to use one that I would supply in code?

Here is a sample of the source I am using

Dim app As New Microsoft.SqlServer.Dts.Runtime.Application
Dim pkg As Microsoft.SqlServer.Dts.Runtime.Package = New Microsoft.SqlServer.Dts.Runtime.Package()

pkg = app.LoadFromSqlServer(packageName, serverName, Nothing, Nothing, Nothing)

pkg(0).ConfigurationType = DTSConfigurationType.ISqlServer


Dim result = pkg.Execute

N West · Accepted Answer · 2013-10-22 12:51:43Z

3

Take a look at the source code for DTLoggedExec.

In the Program.cs file there is some code that sets property values, you can probably use this in your program.

DtsProperty p;
Variable pkgObj;
pkgObj = (Variable)package.GetObjectFromPackagePath(valuePath, out p);                                        

if (p != null && pkgObj != null)
{
Console.WriteLine(" (Parameter Type: " + pkgObj.DataType + ")");
p.SetValue(pkgObj, Convert.ChangeType(s[1], pkgObj.DataType));
}

The valuePath is the path to the object and s1 is your value you want to put into that property. For a connection manager, it usually is something like:

\Package.Connections[CONNMGRNAME].Properties[ConnectionString]

answered Oct 22, 2013 at 12:51

N West

6,79428 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

N West Over a year ago

I believe that this will work even if the design-time parameters are set. The problem stil may crop up if the conn string is being set later by an expression. In that case, you can change the value of the expression using similar code.

Tachi Over a year ago

Thanks a lot N West. I finally overrode the parameters successfully following your suggestion.

Jari Turkia Over a year ago

Ten years later: I think you cannot alter the connection string value from loaded package. I had to clear the connection string to be able to alter it.

White_Raven · Accepted Answer · 2013-10-22 12:58:23Z

1

Your package object should expose Connections property/collection of ConnectionManager objects. You can iterate this collection to find CM You want to modify. Afterwards just set its ConnectionString property to any correct value.

answered Oct 22, 2013 at 12:58

White_Raven

1661 bronze badge

2 Comments

White_Raven Over a year ago

Oh, and remember that this value (as any other) can only be changed from code before You run Execute on package object. What @NWest had said still applies though: if You have an in package expression set on this particular ConnestionManager.ConnectionString its calculated value will take precedence before the one You have set from code.

Tachi Over a year ago

Thanks White_Raven. This is the issue that I have been dealing with, where the values for the parameters that I set in code were overridden by design time values.

Collectives™ on Stack Overflow

Overriding SSIS Configuration Connection String in .NET

2 Answers 2

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related