August 4, 2015 at 9:22 am
Hello,
I'm stuck at implementing the code found at
for use in a script component (reading a composed excel file using a oledb connection).
ConnectionManager cm = Dts.Connections["MyConnection"];
Getting
The type or namespace name 'ConnectionManager' could not be found.
The Name 'Dts' does not exist in the current context.
Any clues how to read oledb inside a scriptcomponent (dataflow)?
Thanks in advance
August 4, 2015 at 11:35 am
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
August 5, 2015 at 4:38 am
Thanks, I'll try it out
August 5, 2015 at 9:21 am
Got away with changing the connection from native oledb Microsoft Ace... to .NET Provider/Microsoft Ace...
Seems to work in the script component and sufficient for the small excelfiles.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply