Script Object Model object in SSIS (commonly used in the SSIS script tasks)
Just a quick code snippet on the usage of the ScriptObjectModel in SSIS script tasks... It takes one through all the required functions that can be exposed using the script object model:-
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.SqlServer.Dts.DtsClient;
using Microsoft.SqlServer.Dts.Runtime;
using Microsoft.SqlServer.Dts.Tasks.ScriptTask;
using Microsoft.SqlServer.Dts.Tasks;
namespace ScriptObjectNotifier
{
public class SOCall
{
ScriptObjectModel objModel;
public SOCall(ScriptObjectModel valObjModel)
{
objModel = valObjModel;
}
/// <summary>
/// gets the connection string from a conn Parameter
/// </summary>
/// <param name="connName">name of the connection</param>
/// <returns>string</returns>
public string GetConnectionString(string connName)
{
string connectionString = objModel.Connections[connName].ConnectionString;
return connectionString;
}
/// <summary>
/// gets the variable value from a attribute name/ variable name
/// </summary>
/// <param name="attName">name of the variable</param>
/// <returns>string</returns>
public string GetVariableValue(string attName)
{
string varValue = objModel.Variables[attName].Value.ToString();
return varValue;
}
/// <summary>
/// fires an event based on message and message type
/// </summary>
/// <param name="message">message value to be triggered depending on type of event</param>
/// <param name="messageType">type of message </param>
public void FireEvents(string message, int messageType)
{
bool val = false;
switch (messageType)
{
case 1: objModel.Events.FireInformation(0, "My display here", "Fire some info @ runtime", "helpfile path", 0, ref val);
break;
case 2: objModel.Events.FireError(0, "My error here", "some err description here", "helpfile path", 0);
break;
case 3: objModel.Events.FireProgress("progress description --> 20% etc...", 50, 0, 100, "subComponentName", ref val);
break;
case 4: objModel.Events.FireQueryCancel();
break;
case 5: object[] objList = new object[] { "argslist" };
objModel.Events.FireCustomEvent("new event", "event text", ref objList, "sub component", ref val);
break;
default: //do nothing
break;
}
}
/// <summary>
/// returns the current execution value
/// </summary>
/// <returns>Execution Value object</returns>
public object ReturnExecutionValue()
{
return objModel.ExecutionValue;
}
/// <summary>
/// returns the current task result
/// </summary>
/// <returns>returns the task result of the SSIS package... you can also set the value of the same</returns>
public int ReturnTaskResult()
{
return objModel.TaskResult;
}
/// <summary>
/// log text messages to the SSIS output stream
/// </summary>
public void LogTextMessagesAtRunTime(string messageValue)
{
byte[] getDataCode = new byte[0];//empty byte here... you can redirect a buffer output here if one likes......
objModel.Log(messageValue, 0, getDataCode);
}
}
}
There are not much comments as most of the methods are just snippets and showcases the way of using this specific object. If anyone needs more clarity just buzz me back on this post (& maybe I can clarify them ;) )
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.SqlServer.Dts.DtsClient;
using Microsoft.SqlServer.Dts.Runtime;
using Microsoft.SqlServer.Dts.Tasks.ScriptTask;
using Microsoft.SqlServer.Dts.Tasks;
namespace ScriptObjectNotifier
{
public class SOCall
{
ScriptObjectModel objModel;
public SOCall(ScriptObjectModel valObjModel)
{
objModel = valObjModel;
}
/// <summary>
/// gets the connection string from a conn Parameter
/// </summary>
/// <param name="connName">name of the connection</param>
/// <returns>string</returns>
public string GetConnectionString(string connName)
{
string connectionString = objModel.Connections[connName].ConnectionString;
return connectionString;
}
/// <summary>
/// gets the variable value from a attribute name/ variable name
/// </summary>
/// <param name="attName">name of the variable</param>
/// <returns>string</returns>
public string GetVariableValue(string attName)
{
string varValue = objModel.Variables[attName].Value.ToString();
return varValue;
}
/// <summary>
/// fires an event based on message and message type
/// </summary>
/// <param name="message">message value to be triggered depending on type of event</param>
/// <param name="messageType">type of message </param>
public void FireEvents(string message, int messageType)
{
bool val = false;
switch (messageType)
{
case 1: objModel.Events.FireInformation(0, "My display here", "Fire some info @ runtime", "helpfile path", 0, ref val);
break;
case 2: objModel.Events.FireError(0, "My error here", "some err description here", "helpfile path", 0);
break;
case 3: objModel.Events.FireProgress("progress description --> 20% etc...", 50, 0, 100, "subComponentName", ref val);
break;
case 4: objModel.Events.FireQueryCancel();
break;
case 5: object[] objList = new object[] { "argslist" };
objModel.Events.FireCustomEvent("new event", "event text", ref objList, "sub component", ref val);
break;
default: //do nothing
break;
}
}
/// <summary>
/// returns the current execution value
/// </summary>
/// <returns>Execution Value object</returns>
public object ReturnExecutionValue()
{
return objModel.ExecutionValue;
}
/// <summary>
/// returns the current task result
/// </summary>
/// <returns>returns the task result of the SSIS package... you can also set the value of the same</returns>
public int ReturnTaskResult()
{
return objModel.TaskResult;
}
/// <summary>
/// log text messages to the SSIS output stream
/// </summary>
public void LogTextMessagesAtRunTime(string messageValue)
{
byte[] getDataCode = new byte[0];//empty byte here... you can redirect a buffer output here if one likes......
objModel.Log(messageValue, 0, getDataCode);
}
}
}
There are not much comments as most of the methods are just snippets and showcases the way of using this specific object. If anyone needs more clarity just buzz me back on this post (& maybe I can clarify them ;) )
Comments