<Biml xmlns:NzSsisFrameworkv1="http://schemas.BigFishGames.com/NzSsisFrameworkv1" xmlns="http://schemas.varigence.com/biml.xsd">
<ScriptProjects>
<ScriptTaskProject ProjectCoreName="ST_1c75adcca0a24afd96fb09b8b920d5c7" Name="ExecuteScript">
<AssemblyReferences>
<AssemblyReference AssemblyPath="Microsoft.SqlServer.ManagedDTS.dll" />
<AssemblyReference AssemblyPath="Microsoft.SqlServer.ScriptTask.dll" />
<AssemblyReference AssemblyPath="System.dll" />
<AssemblyReference AssemblyPath="System.AddIn, Version=3.5.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089, processorArchitecture=MSIL" />
<AssemblyReference AssemblyPath="System.Data.dll" />
<AssemblyReference AssemblyPath="System.Windows.Forms.dll" />
<AssemblyReference AssemblyPath="System.Xml.dll" />
</AssemblyReferences>
<Files>
<File Path="AssemblyInfo.cs">using System.Reflection;
using System.Runtime.CompilerServices;
//
// General Information about an assembly is controlled through the following
// set of attributes. Change these attribute values to modify the information
// associated with an assembly.
//
[assembly: AssemblyTitle("ST_1c75adcca0a24afd96fb09b8b920d5c7.csproj")]
[assembly: AssemblyDescription("")]
[assembly: AssemblyConfiguration("")]
[assembly: AssemblyCompany("Varigence")]
[assembly: AssemblyProduct("ST_1c75adcca0a24afd96fb09b8b920d5c7.csproj")]
[assembly: AssemblyCopyright("Copyright @ Varigence 2011")]
[assembly: AssemblyTrademark("")]
[assembly: AssemblyCulture("")]
//
// Version information for an assembly consists of the following four values:
//
// Major Version
// Minor Version
// Build Number
// Revision
//
// You can specify all the values or you can default the Revision and Build Numbers
// by using the '*' as shown below:
[assembly: AssemblyVersion("1.0.*")]</File>
<File Path="ScriptMain.cs">
/*=============================================================================
Description:
This script will execute a PowerShell, Perl script, C# Script or Windows Executable and
log the output to the Data Directory (default) or some other location (if an override
variableis present).
This component has the following requirements:
1. Several standard variables must be present in the context that it is run.
2. The standard NZ ETL Framework variables must be present.
3. PowerShell, Perl, and CS-Script (cscs.exe) are in the environment path.
4. Cygwin is installed at c:\cygwin and c:\cygwin\bin is in the path.
=============================================================================
History:
20111208 david.darden Initial Version
=============================================================================*/
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.Diagnostics; // Used to execute a process
using System.IO; // Used for path operations
using System.Text; // Used for a Log stringbuilder
using System.Text.RegularExpressions; // Use for token parsing
namespace ExecuteScript.csproj
{
[System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{
#region Module level variables
// String builder for a consolidated log
static StringBuilder ProcessLog = new StringBuilder();
// String builder for an error only log
static StringBuilder ErrorLog = new StringBuilder();
#endregion
#region VSTA generated code
enum ScriptResults
{
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
};
#endregion
public void Main()
{
//////////////////////////////////////////////////////////////////
// Get the required parameters from the user
//////////////////////////////////////////////////////////////////
// These will automatically throw an error if they don't exist in
// the appropriate conntext.
string scriptPath = ReadVariable("_Script_Path").ToString();
string scriptArguments = ReadVariable("_Script_Arguments").ToString();
int exitCode = -42; // Overwritten when the Script is executed
// Check if the optional parameters exist, and assign defaults if not
string scriptLogDir = string.Empty;
string scriptWorkingDir = string.Empty;
bool useCygwin = false; // Execute under Cygwin
bool useCygwinPaths = false; // Convert \ to / in paths; forced if useCygwin is set
int retryLimit = 0;
int retryWaitSeconds = 60;
// Append a backslash to the log dir path if required as we'll be adding to it later
if (VariableExists("_Script_Log_Dir"))
{
scriptLogDir = ReadVariable("_Script_Log_Dir").ToString();
if (!scriptLogDir.EndsWith("\\")) { scriptLogDir += "\\"; }
}
else
{
scriptLogDir = ReadVariable("f_Dir_Data").ToString();
if (!scriptLogDir.EndsWith("\\")) { scriptLogDir += "\\"; }
scriptLogDir += "Log\\";
}
// Working Directory defaults to the Job Directory. This can be overriden
// if a job uses sub directories (for organization) and has a dependency in the directory
// (such as a Perl module).
if (VariableExists("_Script_Working_Dir"))
{
scriptWorkingDir = ReadVariable("_Script_Working_Dir").ToString();
if (!scriptWorkingDir.EndsWith("\\")) { scriptWorkingDir += "\\"; }
}
else
{
scriptWorkingDir = ReadVariable("f_Dir_Job").ToString();
if (!scriptWorkingDir.EndsWith("\\")) { scriptWorkingDir += "\\"; }
}
// Flag to use Cygwin style file paths (convert \ to /).
// This needs to be evaluated before '_Script_Use_Cygwin' is!
if (VariableExists("_Script_Use_Cygwin_Paths"))
{
useCygwinPaths = Convert.ToBoolean(ReadVariable("_Script_Use_Cygwin_Paths"));
}
// Number of times to Retry on a failure condition.
if (VariableExists("_Script_Retry_Limit"))
{
retryLimit = Convert.ToInt32(ReadVariable("_Script_Retry_Limit"));
}
// Number of seconds to wait before a Retry
if (VariableExists("_Script_Retry_Wait_Seconds"))
{
retryWaitSeconds = Convert.ToInt32(ReadVariable("_Script_Retry_Wait_Seconds"));
}
// Flag to execute a script using Cygwin instead of Windows shell
if (VariableExists("_Script_Use_Cygwin"))
{
useCygwin = Convert.ToBoolean(ReadVariable("_Script_Use_Cygwin"));
// Force useCygwinPaths if useCygwin is set since everything will break
// otherwise.
if (useCygwin)
{
useCygwinPaths = true;
}
}
int scriptSuccessCode = 0;
if (VariableExists("_Script_Success_Code"))
{
scriptSuccessCode = (int)ReadVariable("_Script_Success_Code");
}
//////////////////////////////////////////////////////////////////
// Pull the Framework variables we need to create the log name
//////////////////////////////////////////////////////////////////
// Replace spaces with underscores since we'll use this to create a directory.
string stepKey = ReadVariable("f_Step_Key").ToString();
string stepCd = ReadVariable("_Step_Cd").ToString().Replace(" ", "_");
//////////////////////////////////////////////////////////////////
// Validate inputs
//////////////////////////////////////////////////////////////////
// Verify the Script exists
// Verify the file exists; it can either be in the Job Dir (working path) or an absolute path
// but must be in one.
//if (!File.Exists(scriptPath) && !File.Exists(scriptWorkingDir + scriptPath))
//{
// throw new ArgumentException(string.Format("The script '{0}' does not exist (checked absolute path and job directory). Check your path.", scriptPath));
//}
// Get the extension and error if it is not recognized
string scriptExtension = Path.GetExtension(scriptPath);
string scriptType = string.Empty; // Used to determine how to execute the script
switch (scriptExtension)
{
case ".ps1":
scriptType = "PowerShell";
break;
case ".pl":
scriptType = "Perl";
break;
case ".exe": // Fall through the standard executable tables
case ".bat":
case ".cmd":
scriptType = "Executable";
break;
case ".cs":
scriptType = "CSharp";
break;
default:
throw new ArgumentException(string.Format("The script '{0}' has an unhandled extension. This component handles .ps1, .pl, .cs, .exe., .cmd, and .bat files.", scriptPath));
}
//////////////////////////////////////////////////////////////////
// Create a log file name
//////////////////////////////////////////////////////////////////
// The scriptLogDir has a trailing backslash appended when it is set.
string scriptLogPath = scriptLogDir + stepKey + "_" + stepCd.Replace(" ", "_") + ".log";
//////////////////////////////////////////////////////////////////
// Log inputs
//////////////////////////////////////////////////////////////////
WriteInfo("Script Name: {0}", scriptPath);
WriteInfo("Script Arguments: {0}", scriptArguments);
WriteInfo("Script Log Path: {0}", scriptLogPath);
WriteInfo("Script Working Path: {0}", scriptWorkingDir);
WriteInfo("Script Success Code: {0}", scriptSuccessCode);
WriteInfo("Use Cygwin: {0}", useCygwin);
WriteInfo("Use Cygwin Paths: {0}", useCygwinPaths);
WriteInfo("Retry Limit: {0}", retryLimit);
WriteInfo("Retry Wait: {0}", retryWaitSeconds);
//////////////////////////////////////////////////////////////////
// Create the Log Directory if it doesn't exist
//////////////////////////////////////////////////////////////////
// This should not happen, but might in the case of an Archive script
// where the directory does not exist yet.
if (!Directory.Exists(scriptLogDir))
{
WriteInfo("Creating Log Dir: {0}", scriptLogDir);
Directory.CreateDirectory(scriptLogDir);
}
//////////////////////////////////////////////////////////////////
// Convert file paths to Cygwin style if requested
//////////////////////////////////////////////////////////////////
if (useCygwinPaths)
{
scriptArguments = ConvertToCygwinFilePaths(scriptArguments);
scriptPath = ConvertToCygwinFilePaths(scriptPath);
}
//////////////////////////////////////////////////////////////////
// Set up the process to run
//////////////////////////////////////////////////////////////////
// The FileName is the executable to run; this will be the script
// interpreter for PowerShell and Perl
// The Arguments is the rest of the command line; this includes what
// you are actually executing in the case of a script.
string processFileName = string.Empty;
string processArgumentsTemplate = string.Empty;
string processArguments = string.Empty;
if ((scriptType == "PowerShell") && (!useCygwin))
{
processFileName = "PowerShell.exe";
// NonInteractive will keep PowerShell from creating a window and will not pause for user input.
// NoProfile will skip the profile; useful for testing where the packag is run under the
// developer account (which might have PS libraries loaded that the service account does not have).
// ExecutionPolicy ByPass will force the script to run regardless of security on the server.
// exit $LASTEXITCODE; will return the Exit Code from the preceeding command; it is swallowed otherwise.
// Passing quotes for the arguments is problematic due to the double parsing; not implementing at
// the current time since it is not required.
// A possible failure condition is when the Script Path and/or Script Arguments are not valid PS
// syntax; the command will error, but not return an error code. This is handled by parsing
// the log for the scenario below.
processArgumentsTemplate = "-NonInteractive -NoProfile -ExecutionPolicy ByPass -Command \" & {{ \"{0}\" {1} ; exit $LASTEXITCODE; }} \" ";
processArguments = string.Format(processArgumentsTemplate, scriptPath, scriptArguments);
}
else if ((scriptType == "PowerShell") && (useCygwin))
{
throw new ArgumentException(string.Format("You cannot run PowerShell under Cygwin. Checker your variables."));
}
else if ((scriptType == "Perl") && (!useCygwin))
{
processFileName = "Perl.exe";
processArgumentsTemplate = " -S \"{0}\" {1} ";
processArguments = string.Format(processArgumentsTemplate, scriptPath, scriptArguments);
}
else if ((scriptType == "Perl") && (useCygwin))
{
// Start a cygwin shell
processFileName = @"c:\cygwin\bin\bash.exe";
// Perl executable needs to be hard coded to make sure we use ActiveState Perl instead of the Cygwin Perl.
// IMPORTANT NOTE: If you shell out for Windows commands you will use a Windows shell. This may or may not
// be useful.
processArgumentsTemplate = "--login -c \"C:/Perl64/bin/perl.exe {0} {1}\"";
processArguments = string.Format(processArgumentsTemplate, scriptPath, scriptArguments);
}
else if ((scriptType == "CSharp") && (useCygwin))
{
throw new ArgumentException(string.Format("You cannot run C# Scripts under Cygwin. Checker your variables."));
}
else if ((scriptType == "CSharp") && (!useCygwin))
{
processFileName = "cscs.exe"; // cs-script executable
processArgumentsTemplate = "\"{0}\" {1} ";
processArguments = string.Format(processArgumentsTemplate, scriptPath, scriptArguments);
}
else if ((scriptType == "Executable") && (!useCygwin))
{
processFileName = scriptPath;
processArguments = scriptArguments;
}
else if ((scriptType == "Executable") && (useCygwin))
{
throw new ArgumentException(string.Format("Running Executables under Cygwin is not implmented. Check your variables or implement."));
}
else
{
// Should never be reached
throw new ArgumentException(string.Format("The script '{0}' has an unhandled extension. This component handles .ps1, .pl, .cs, .exe., .cmd, and .bat files.", scriptPath));
}
// Log the command for debugging purposes
WriteInfo("Full Command to Execute (as {0}): {1}", scriptType, processArguments);
for (int retryCount = 0; retryCount <= retryLimit; ++retryCount)
{
WriteInfo("Performing try {0} out of a maximum of {1}", retryCount, retryLimit);
//////////////////////////////////////////////////////////////////
// Run the process
//////////////////////////////////////////////////////////////////
// Run the command and wait for it to complete.
Process process = new Process();
process.StartInfo.FileName = processFileName;
process.StartInfo.Arguments = processArguments;
process.StartInfo.UseShellExecute = false; // This allows us to redirect output for logging.
process.StartInfo.RedirectStandardError = true; // Redirect so we can log it.
process.StartInfo.RedirectStandardOutput = true; // Redirect so we can log it.
process.StartInfo.CreateNoWindow = true;
process.StartInfo.WorkingDirectory = scriptWorkingDir;
// Use asychronous reads of both the Standard and Error output streams
// to be able to build a single log. You cannot have both of these as synchronous
// reads because of deadlocking. We use a combined log to ensure the error messages
// appear inline with the rest of the output.
// These events appear to be fired as the output is written (non-buffered), but we
// might find out that isn't always the case. This is where to investigate.
process.OutputDataReceived += new DataReceivedEventHandler(process_OutputDataReceived);
process.ErrorDataReceived += new DataReceivedEventHandler(process_ErrorDataReceived);
process.Start();
process.BeginOutputReadLine(); // Start the async write process.
process.BeginErrorReadLine(); // Start the async write process.
process.WaitForExit(); // Don't continue until all output is flushed.
exitCode = process.ExitCode;
//////////////////////////////////////////////////////////////////
// Handle any special cases that we always want to cause failures
//////////////////////////////////////////////////////////////////
if (scriptType == "PowerShell")
{
string processLog = ProcessLog.ToString();
// If PowerShell can't figure out the command line, it will exit without an error
// code; Haven't found a way to trap it. We'll look for the parsing error in the
// log as an indication. There is a possibility that a script will generate this
// error but the developer explicitly logged but continued through the error; this
// will cause unexpected behavior then (it will force the failure of the component).
// Handle that scenario if we run across it.
if (processLog.IndexOf("ParserError:") > 0)
{
exitCode = 1;
}
}
WriteInfo("Detected Exit Code: {0}", exitCode);
// If the process completed with success then exit the loop.
if (exitCode == scriptSuccessCode)
{
break;
}
else if ((exitCode != scriptSuccessCode) && (retryCount < retryLimit))
{
WriteInfo("Sleeping for '{0}' seconds", retryWaitSeconds);
System.Threading.Thread.Sleep(retryWaitSeconds * 1000);
}
} // Retry Loop
//////////////////////////////////////////////////////////////////
// Write the log
//////////////////////////////////////////////////////////////////
using (StreamWriter sw = new StreamWriter(scriptLogPath))
{
sw.WriteLine(ProcessLog.ToString());
sw.WriteLine(string.Format("Exit Code: {0}", exitCode));
}
// Check the log for a magic string that indicates a variable we want to
// pass back to SSIS. The variable is a string in the format
// "SSIS_VARIABLE:<MyVariableName>:<DataType>=<variableValue>"
// Only Int32, Double, DateTime, and String data types are handled
// (and must be explicitly cast to be handed back). If no variable
// matches (case sensitive!) they are ignored.
string regex = "SSIS_VARIABLE:([a-zA-Z0-9_]+):([a-zA-Z0-9]+)=(.*)";
RegexOptions options = ((RegexOptions.IgnorePatternWhitespace | RegexOptions.Multiline)
| RegexOptions.IgnoreCase);
Regex reg = new Regex(regex, options);
foreach (Match m in reg.Matches(ProcessLog.ToString()))
{
string variableName = m.Groups[1].Value.ToString();
string dataTypeName = m.Groups[2].Value.ToString();
string valueString = m.Groups[3].Value.ToString();
object value = ConvertVariableValue(dataTypeName, valueString);
// Set the value of the variable if it exists
if(VariableExists(variableName))
{
WriteVariable(variableName, value);
WriteInfo("Setting {0}={1}", variableName,value);
}
else
{
WriteInfo("NOT setting (no matching variable) {0}={1}", variableName,value);
}
}
//////////////////////////////////////////////////////////////////
// Set outputs
//////////////////////////////////////////////////////////////////
// If the return code doesn't match our expected return code then treat it as an error
if (exitCode != scriptSuccessCode)
{
throw new Exception(string.Format("Process exited with '{0}'. Expecting '{1}'. Check the log file at '{2}'.\r\nActual error:\r\n{3}", exitCode, scriptSuccessCode, scriptLogPath,ErrorLog.ToString()));
}
else
{
Dts.TaskResult = (int)ScriptResults.Success;
}
}
#region Logging Delegates
/// <summary>
/// Handles the ErrorDataReceived event of the process control.
/// </summary>
/// <param name="sender">The source of the event.</param>
/// <param name="e">The <see cref="System.Diagnostics.DataReceivedEventArgs"/> instance containing the event data.</param>
static void process_ErrorDataReceived(object sender, DataReceivedEventArgs e)
{
ProcessLog.AppendLine(e.Data);
ErrorLog.AppendLine(e.Data); // Used to return the actual error text to the SSIS log
}
/// <summary>
/// Handles the OutputDataReceived event of the process control.
/// </summary>
/// <param name="sender">The source of the event.</param>
/// <param name="e">The <see cref="System.Diagnostics.DataReceivedEventArgs"/> instance containing the event data.</param>
static void process_OutputDataReceived(object sender, DataReceivedEventArgs e)
{
ProcessLog.AppendLine(e.Data);
}
#endregion
#region Helpers
/// <summary>
/// Convert a variable value from a string to the correct datatype.
///
/// Supports boolean, int32, double, datetime, and string (any other type).
/// </summary>
/// <param name="dataTypeName">Data type to convert to.</param>
/// <param name="input">String to convert.</param>
/// <returns>Object of the correct type.</returns>
private object ConvertVariableValue(string dataTypeName, string input)
{
object value = null;
// String may have a return at the end; strip it
input = input.Replace("\r", string.Empty);
switch (dataTypeName.ToLower())
{
case "boolean":
value = Convert.ToBoolean(input);
break;
case "int32":
value = Convert.ToInt32(input);
break;
case "double":
value = Convert.ToDouble(input);
break;
case "datetime":
value = Convert.ToDateTime(input);
break;
default:
value = input;
break;
}
return value;
}
/// <summary>
/// Parses a string of tokens and converts \ to / for anything that looks like a file path.
/// This is used for compatibility between Windows, Perl, and Cygwin. Most of Windows
/// has no issue with this style of path, but Cygwin can't handle a standard Windows
/// Path.
/// </summary>
/// <param name="input">String to process.</param>
/// <returns>String with any changes.</returns>
private string ConvertToCygwinFilePaths (string input)
{
StringBuilder output = new StringBuilder();
char[] delimiters = new char[] { ' ', '\t' }; // Split on spaces or tabs
foreach (string token in input.Split(delimiters, StringSplitOptions.RemoveEmptyEntries))
{
string outputToken = token;
if (Regex.IsMatch(token, @"^[a-zA-Z]\:\\"))
{
outputToken = outputToken.Replace(@"\", "/");
}
output.Append(outputToken + " ");
}
return output.ToString();
}
#endregion
#region Logging Methods
/// <summary>
/// Logs messages using a standard method.
/// </summary>
/// <param name="message">Message to write.</param>
public void WriteInfo(string formatString, params object[] args)
{
bool fireAgain = false;
string message = string.Format(formatString, args);
Dts.Events.FireInformation(0, "ExecuteScript", message, string.Empty, 0, ref fireAgain);
}
#endregion
#region Variable Access Methods
/// <summary>
/// Get an arbitrary variable from the collection.
/// </summary>
/// <param name="variableName">Name of the variable to read.</param>
/// <returns>Value as an object.</returns>
public object ReadVariable(string variableName)
{
object value = null;
// Create a variables collection to hold you object
Variables variables = default(Variables);
try
{
// Lock the variable first to make sure that you have exclusive access
Dts.VariableDispenser.LockOneForRead(variableName, ref variables);
// Get the value (as an object, so it will need to be casted back later)
value = variables[variableName].Value;
}
catch (Exception ex)
{
// For debugging purposes
throw ex;
}
finally
{
// Explicitly unlock before exiting, just in case
variables.Unlock();
}
return value;
}
/// <summary>
/// Set an arbitrary variable in the collection.
/// </summary>
/// <param name="variableName">Name of the variable to write.</param>
/// <param name="value">Value to set the variable to.</param>
public void WriteVariable(string variableName, object value)
{
// Create a variables collection to hold you object
Variables variables = default(Variables);
try
{
// Lock the variable first to make sure that you have exclusive access
Dts.VariableDispenser.LockOneForWrite(variableName, ref variables);
variables[variableName].Value = value;
}
catch (Exception ex)
{
throw ex;
}
finally
{
// Explicitly unlock before exiting, just in case
variables.Unlock();
}
}
/// <summary>
/// Check if a variable exists.
/// </summary>
/// <param name="variableName">Name of the variable to check for.</param>
/// <returns>True if it exists, False if not.</returns>
public bool VariableExists(string variableName)
{
return Dts.VariableDispenser.Contains(variableName);
}
#endregion
}
}</File>
</Files>
<Annotations>
</Annotations>
</ScriptTaskProject>
</ScriptProjects>
</Biml>
ExecuteScript Script Project The ExecuteScript Script Project is a project we use to make shelling out and executing various processes easier.
The ExecuteScript Script Project is a project we use to make shelling out and executing various processes easier. You'll probably need to modify this a bit since it relies on certain variables to exist in context (such as the Log Directory and a Step Key) but you should get the idea.
This script project uses some "magic variables" to know what to run and handles setting arguements, combining the standard/error streams into a log file, automatic retries, and setting variables in the calling package based on the output that has been logged. It currently handles standard executables (.cmd, .bat, .exe), Perl, CS-Script, and executables under Cygwin (assuming you have all these things set up and in your path).
It accepts the following User variables to control the behavior:
1. ScriptPath - Required - The full path to the executable. This is typically done as an expression. Example: @f_Dir_Job + "MyScript.ps1"
-
ScriptArguments - Required - Any arguments to pass to the script/executable. Avoid spaces in arguments as escaping double quotes is sometimes problematic. C# - just use \" to pass a quote; the expression
"\"Hello World\" 42"passes 2 parameters,"Hello world"and42 -
ScriptLog_Dir - Optional - The path to write the log to. Defaults to
"<Data Dir>\Log". This is not typically used. -
ScriptWorking_Dir - Optional - The Working Directory path. Defaults to
"<Job Dir>". -
ScriptSuccess_Code - Optional - The exit code that the component treats as Success. Defaults to
0. -
ScriptUse_Cygwin - Optional - The executable will run under the Cygwin environment (bash). Note that when using Perl any system commands will still execute under Windows.
-
ScriptUseCygwinPaths - Optional - Any file paths (that look like
"[a-zA-Z]:\"will have \ switched to /. This is automatically set to true when ScriptUse_Cygwin is set to true. -
ScriptRetry_Limit - Optional - Number of times to retry a script if it fails. Defaults to
0. -
ScriptRetryWaitSeconds - Optional - Number of seconds to wait before retrying a script. Defaults to
60. Only applies if ScriptRetry_Limit is also set.
This script project allows you to return multiple variables to SSIS; it will set the variables if it finds them in scope and log the value if not. The format for the variables is:
SSIS_VARIABLE:<Variable Name>:<Data Type>=<value>
Data Types = boolean, int32, double, datetime, string
Other formats will be passed back as a string but will fail if the receiving variable is of an incompatible type.
Here is an example of the code you would have in your script or executable to set the appropriate variable:
Console.WriteLine("SSIS_VARIABLE:MyDouble:Double=42.42");
Console.WriteLine("SSIS_VARIABLE:MyString:String=Yo I'm a string");
Console.WriteLine("SSIS_VARIABLE:StartDateTime:DateTime=" + DateTime.Now.ToString());
Console.WriteLine("SSIS_VARIABLE:IsItTrue:Boolean=" + true.ToString());
Here's a Biml Snippet that shows calling a script and setting values such as IntVar, DoubleVar, etc.
<Container Name="test_job_CSharpTest" ConstraintMode="Linear">
<Variables>
<Variable Name="_Script_Arguments" DataType="String" EvaluateAsExpression="true" IncludeInDebugDump="Exclude">"\"Param 1\" Param2 42"</Variable>
<Variable Name="_Script_Path" DataType="String" EvaluateAsExpression="true" IncludeInDebugDump="Exclude">"REP-5044_Test.cs"</Variable>
<Variable Name="IntVar" DataType="Int32">0</Variable>
<Variable Name="DoubleVar" DataType="Double">0</Variable>
<Variable Name="StringVar" DataType="String"></Variable>
<Variable Name="DateTimeVar" DataType="DateTime">1900-01-01</Variable>
<Variable Name="BoolVar" DataType="Boolean">false</Variable>
</Variables>
<Tasks>
<Script ProjectCoreName="ST_b1fe693d99d64422b09278dc9fbad0a6" Name="Execute csharp">
<ScriptTaskProjectReference ScriptTaskProjectName="ExecuteScript" />
</Script>
</Tasks>
</Container>
Comments
There are no comments yet.