Jo i use a script task to pull that out for other script tasks to consume all the time..
here's a full script task example, but the piece you need is the reference to using Microsoft.SqlServer.Dts.Runtime;:
in my case, depending on whetheri'm developing in VS2008 thru VS2013, i need to strip out some info related to the provider that exists int eh connectionstring.
/*
Microsoft SQL Server Integration Services Script Task
Write scripts using Microsoft Visual C# 2008.
The ScriptMain is the entry point class of the script.
*/
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
namespace ST_5cee32343b354b8e8e2e8a338eab7876.csproj
{
[System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{
#region VSTA generated code
enum ScriptResults
{
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
};
#endregion
/*
The execution engine calls this method when the task executes.
To access the object model, use the Dts property. Connections, variables, events,
and logging features are available as members of the Dts property as shown in the following examples.
To reference a variable, call Dts.Variables["MyCaseSensitiveVariableName"].Value;
To post a log entry, call Dts.Log("This is my log text", 999, null);
To fire an event, call Dts.Events.FireInformation(99, "test", "hit the help message", "", 0, true);
To use the connections collection use something like the following:
ConnectionManager cm = Dts.Connections.Add("OLEDB");
cm.ConnectionString = "Data Source=localhost;Initial Catalog=AdventureWorks;Provider=SQLNCLI10;Integrated Security=SSPI;Auto Translate=False;";
Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
To open Help, press F1.
*/
public void Main()
{
string conn = Dts.Connections["LocalHost.SandBox"].ConnectionString.Replace("Provider=SQLNCLI10.1;", "").Replace("Provider=SQLNCLI11;", "").Replace("Provider=SQLNCLI11.1;", "").Replace("Auto Translate=False;", "");
if (conn.ToLower().IndexOf("connect timeout") < 0)
{
conn = conn + "Connect Timeout=0;";
}
else
{
//replacing default timeout to be 600? or 0?
conn = conn.Replace("Connect Timeout=30;", "Connect Timeout=0;");
}
//Connect Timeout=600;
Dts.Variables["Connectionstring"].Value = conn;
Dts.TaskResult = (int)ScriptResults.Success;
}
}
}
Lowell