 |
| CAST to get trailing zeros |
 |
| WMI Event Watcher Task |
 |
| Windows Service 4.0 C# |
 |
| app.config |
<?xml version="1.0" encoding="utf-8" ?> <configuration> <appSettings> <add key="WatcherPath" value="d:\MySourceFiles"/> <add key="WatcherFilter" value="*.csv"/> <add key="SqlConnectionString" value="Data Source=.\SQL2012;Initial Catalog=master;Integrated Security=SSPI;"/> <add key="Catalog" value="SSISDB"/> <add key="Folder" value="MyFolder"/> <add key="Project" value="MyProject"/> <add key="Package" value="MyPackage.dtsx"/> <add key="LogFile" value="d:\FileWatcherForSSIS\log.txt"/> </appSettings> </configuration>
 |
| MyCatalogStructure |
 |
| Add References |
// C# code
using System;
using System.Collections.Generic;
using System.Collections.ObjectModel; // Added
using System.ComponentModel;
using System.Configuration; // Added
using System.Data;
using System.Data.SqlClient; // Added
using System.Diagnostics;
using System.IO; // Added
using System.Linq;
using System.ServiceProcess;
using System.Text;
using Microsoft.SqlServer.Management.IntegrationServices; // Added
namespace FileWatcherForSSIS
{
public partial class Service1 : ServiceBase
{
public Service1()
{
InitializeComponent();
}
/// <summary>
/// Method that executes when the services starts
/// </summary>
/// <param name="args"></param>
protected override void OnStart(string[] args)
{
// Create FileSystemWatcher dynamically instead of via the designer
FileSystemWatcher watcher = new FileSystemWatcher();
// Determine the folder you are watching
watcher.Path = ConfigurationManager.AppSettings["WatcherPath"];
// Determine for which files to watch
watcher.Filter = ConfigurationManager.AppSettings["WatcherFilter"];
// Determine whether you also want to watch subfolders
watcher.IncludeSubdirectories = true;
// Determine for which changes to watch (multiple notify
// filters should be seperated by a pipeline |
watcher.EnableRaisingEvents = true;
//watcher.NotifyFilter = NotifyFilters.CreationTime;
// Determine what to do for which events
watcher.Created += new FileSystemEventHandler(watcher_Created);
// Log start of service
logText("FileWatcher started watching " + watcher.Path);
}
/// <summary>
/// Method that executes when the services stops
/// </summary>
protected override void OnStop()
{
// Log start of service
logText("FileWatcher stopped watching ");
}
// Define the event handlers.
private static void watcher_Created(object sender, FileSystemEventArgs e)
{
// Log start of service
logText("FileWatcher catched " + e.FullPath);
// Connection to the database server where the packages are located
using (SqlConnection ssisConnection = new SqlConnection(ConfigurationManager.AppSettings["SqlConnectionString"]))
{
try
{
// SSIS server object with sql connection
IntegrationServices ssisServer = new IntegrationServices(ssisConnection);
// Get values from app.config
string catalog = ConfigurationManager.AppSettings["Catalog"];
string folder = ConfigurationManager.AppSettings["Folder"];
string project = ConfigurationManager.AppSettings["Project"];
string package = ConfigurationManager.AppSettings["Package"];
// The reference to the package which you want to execute
PackageInfo ssisPackage = ssisServer.Catalogs[catalog].Folders[folder].Projects[project].Packages[package];
// Create collection of parameters
Collection<PackageInfo.ExecutionValueParameterSet> executionParameter = new Collection<PackageInfo.ExecutionValueParameterSet>();
// Add a package parameter
executionParameter.Add(new PackageInfo.ExecutionValueParameterSet { ObjectType = 30, ParameterName = "MyFilePathParameter", ParameterValue = e.FullPath });
// Add a logging level parameter: 1=basic, 2=performance
executionParameter.Add(new PackageInfo.ExecutionValueParameterSet { ObjectType = 50, ParameterName = "LOGGING_LEVEL", ParameterValue = 1 });
// Execute package asynchronized and get
// the identifier of the execution
long executionIdentifier = ssisPackage.Execute(false, null, executionParameter);
// Prevent 30 second time-out by getting the execution
// by its ID and then wait until it's completed
ExecutionOperation executionOperation = ssisServer.Catalogs[catalog].Executions[executionIdentifier];
// Wait while package is busy
while (!(executionOperation.Completed))
{
// Refresh and wait 5 seconds before retesting
executionOperation.Refresh();
System.Threading.Thread.Sleep(5000);
}
// Log package completion
logText("Package completed " + package);
}
catch (Exception ex)
{
logText("FileWatcher error " + ex.Message);
}
}
}
/// <summary>
/// Write message to log
/// </summary>
/// <param name="logText">the log message</param>
private static void logText(string logText)
{
// Simple log version. You can replace it by for example an eventlog writer
using (FileStream fs = new FileStream(ConfigurationManager.AppSettings["LogFile"], FileMode.Append, FileAccess.Write))
using (StreamWriter sw = new StreamWriter(fs))
{
sw.WriteLine(DateTime.Now.ToString() + " - " + logText);
}
}
}
}
 |
| Installer.cs |
 |
| Service(Process)Installer |
 |
| Service Properties |
 |
| Bin Release Folder |
 |
| Visual Studio Command Prompt (run as Administrator) |
 |
| The new Windows Service |
 |
| Log file |
 |
| Method not found: 'Boolean Microsoft.SqlServer.Dts.Design.VisualStudio2012Utils.IsVisualStudio2012ProInstalled()'. |
 |
| Microsoft.SqlServer.Dts.Design dll before and after fixing the bug. |
 |
| gacutil/if Microsoft.SqlServer.Dts.Design.dll |
 |
| Different location GacUtil |
 |
| Three clicks instead of one |
 |
| Show Layout toolbar |
 |
| Customize window |
 |
| Add command |
 |
| One click only :-) |
 |
| Loop through *.xls also includes xlsx files |
 |
| My xls loop includes xlsx and xlsm files |
 |
| All xls files? (/b is to remove my Dutch header/footer) |
 |
| Empty/collapsed Sequence Container added |
 |
| Expression with LOWER and RIGHT to check the file extension |
 |
| The result: only two xls files and no xlsx or xlsm files |
 |
| XML Source Connection Manager Page without Connection Managers |
 |
| XML Task can use Connection Managers |
 |
| Select Data Flow Task and press F4 to get properties |
 |
| Property Expression Editor |
 |
| Expression Builder |
 |
| Custom Task with built-in Expression Builder |
 |
| Two extra usings |
 |
| Extra controls in the editor |
// C# code
private void btnExpression_Click(object sender, EventArgs e)
{
try
{
// Create an expression builder popup and make sure the expressions can be evaluated as a string:
// Or change it if you want boolean to System.Boolean, etc. Last property is the textbox containing
// the expression that you want to edit.
using (var expressionBuilder = ExpressionBuilder.Instantiate(_taskHost.Variables,
_taskHost.VariableDispenser,
Type.GetType("System.String"),
txtExpression.Text))
{
// Open the window / dialog with expression builder
if (expressionBuilder.ShowDialog() == DialogResult.OK)
{
// If pressed OK then get the created expression
// and put it in a textbox.
txtExpression.Text = expressionBuilder.Expression;
lblExpressionEvaluated.Text = "";
// Create object to evaluate the expression
Wrapper.ExpressionEvaluator evalutor = new Wrapper.ExpressionEvaluator();
// Add the expression
evalutor.Expression = txtExpression.Text;
// Object for storing the evaluated expression
object result = null;
try
{
// Evalute the expression and store it in the result object
evalutor.Evaluate(DtsConvert.GetExtendedInterface(_taskHost.VariableDispenser), out result, false);
}
catch (Exception ex)
{
// Store error message in label
// Perhaps a little useless in this example because the expression builder window
// already validated the expression. But you could also make the textbox readable
// and change the expression there (without opening the expression builder window)
lblExpressionEvaluated.Text = ex.Message;
}
// If the Expression contains some error, the "result" will be <null>.
if (result != null)
{
// Add evaluated expression to label
lblExpressionEvaluated.Text = result.ToString();
}
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
 |
| Log all events, executable is package |
 |
| Event handlers executing multiple times |
 |
| Expression to filter events that are not from the package |
 |
| No second Sequence Container |
 |
| Using the fullpath |
 |
| Using CallBimlScript |
 |
| Pointing to MSDB instead of SSISDB |
 |
| ADO.Net connection (don't use OLEDB) |
 |
| String variable filled with package path |
 |
| Script Task - ReadOnlyVariables |
 |
| Add references |
// C# Code
#region Namespaces
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
// Added:
using System.Data.SqlClient;
using Microsoft.SqlServer.Management.IntegrationServices;
using System.Collections.ObjectModel;
#endregion
namespace ST_e71fdb73f68c4a3f9595ea5d37464a62
{
/// <summary>
/// ScriptMain is the entry point class of the script. Do not change the name, attributes,
/// or parent of this class.
/// </summary>
[Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{
public void Main()
{
// Boolean variable for firing event messages
bool fireAgain = true;
// Execution of child package starting
Dts.Events.FireInformation(0, "Child Package - " + Dts.Variables["User::SSISDBPackagePath"].Value.ToString(), "Starting", string.Empty, 0, ref fireAgain);
try
{
// Connection to the database server where the packages are located
// SqlConnection ssisConnection = new SqlConnection(@"Data Source=.\SQL2012;Initial Catalog=SSISDB;Integrated Security=SSPI;");
SqlConnection ssisConnection = new SqlConnection(Dts.Connections["mySqlServerAdoNet"].ConnectionString);
// SSIS server object with connection
IntegrationServices ssisServer = new IntegrationServices(ssisConnection);
// Split the variable containing the package path in smaller
// parts: /SSISDB/Folder/Project/Package.Dtsx
string[] SSISDBPackagePath = Dts.Variables["User::SSISDBPackagePath"].Value.ToString().Split('/');
// The reference to the package which you want to execute
// Microsoft.SqlServer.Management.IntegrationServices.PackageInfo ssisPackage = ssisServer.Catalogs["SSISDB"].Folders["folder"].Projects["project"].Packages["package.dtsx"];
Microsoft.SqlServer.Management.IntegrationServices.PackageInfo ssisPackage = ssisServer.Catalogs[SSISDBPackagePath[1]].Folders[SSISDBPackagePath[2]].Projects[SSISDBPackagePath[3]].Packages[SSISDBPackagePath[4]];
// Add execution parameter to override the default asynchronized execution. If you leave this out the package is executed asynchronized
Collection<Microsoft.SqlServer.Management.IntegrationServices.PackageInfo.ExecutionValueParameterSet> executionParameter = new Collection<Microsoft.SqlServer.Management.IntegrationServices.PackageInfo.ExecutionValueParameterSet>();
executionParameter.Add(new Microsoft.SqlServer.Management.IntegrationServices.PackageInfo.ExecutionValueParameterSet { ObjectType = 50, ParameterName = "SYNCHRONIZED", ParameterValue = 1 });
// For adding more parameters go to my WIKI post on MSDN:
// http://social.technet.microsoft.com/wiki/contents/articles/21978.execute-ssis-2012-package-with-parameters-via-net.aspx
// Get the identifier of the execution to get the log
long executionIdentifier = ssisPackage.Execute(false, null, executionParameter);
// If you want to catch the events from the package you are executing then you can add this
// foreach loop. It reads the events and fires them as events. You can remove this loop if
// you're not interested in them.
// Loop through the log and fire events
foreach (OperationMessage message in ssisServer.Catalogs["SSISDB"].Executions[executionIdentifier].Messages)
{
// Translate Message Source Type code and Message Type code to description. See
// MSDN for the complete list http://msdn.microsoft.com/en-us/library/ff877994.aspx
string messageSourceType = "";
switch (message.MessageSourceType)
{
case 10:
messageSourceType = "Entry APIs, such as T-SQL and CLR Stored procedures";
break;
case 20:
messageSourceType = "External process used to run package (ISServerExec.exe)";
break;
case 30:
messageSourceType = "Package-level objects";
break;
case 40:
messageSourceType = "Control Flow tasks";
break;
case 50:
messageSourceType = "Control Flow containers";
break;
case 60:
messageSourceType = "Data Flow task";
break;
}
// Translate Message Type (=event)
string messageType = "";
switch (message.MessageType)
{
case -1:
messageType = "Unknown";
break;
case 120:
messageType = "Error";
break;
case 110:
messageType = "Warning";
break;
case 70:
messageType = "Information";
break;
case 10:
messageType = "Pre-validate";
break;
case 20:
messageType = "Post-validate";
break;
case 30:
messageType = "Pre-execute";
break;
case 40:
messageType = "Post-execute";
break;
case 60:
messageType = "Progress";
break;
case 50:
messageType = "StatusChange";
break;
case 100:
messageType = "QueryCancel";
break;
case 130:
messageType = "TaskFailed";
break;
case 90:
messageType = "Diagnostic";
break;
case 200:
messageType = "Custom";
break;
case 140:
messageType = "DiagnosticEx";
break;
case 400:
messageType = "NonDiagnostic";
break;
case 80:
messageType = "VariableValueChanged";
break;
}
// Fire event depending on the message type (event) in the child package. Since there are event types that you
// can't fire from a Script Task, we need to 'translate' them. For example a TaskFailed event is fired as an
// error event. More info see: http://microsoft-ssis.blogspot.com/2011/02/script-task-and-component-logging.html
switch (message.MessageType)
{
case -1: // Unknown
case 120: // Error
case 130: // TaskFailed
Dts.Events.FireError(Convert.ToInt32(message.MessageType), "Child Package - " + messageSourceType, messageType + " : " + message.Message, string.Empty, 0);
break;
case 110: // Warning
Dts.Events.FireWarning(Convert.ToInt32(message.MessageType), "Child Package - " + messageSourceType, messageType + " : " + message.Message, string.Empty, 0);
break;
default:
Dts.Events.FireInformation(Convert.ToInt32(message.MessageType), "Child Package - " + messageSourceType, messageType + " : " + message.Message, string.Empty, 0, ref fireAgain);
break;
}
} // END FOREACH LOOP
if (ssisServer.Catalogs["SSISDB"].Executions[executionIdentifier].Status == Operation.ServerOperationStatus.Success)
{
// Execution of child package succeeded
Dts.Events.FireInformation(0, "Child Package - " + Dts.Variables["User::SSISDBPackagePath"].Value.ToString(), "Succeeded", string.Empty, 0, ref fireAgain);
Dts.TaskResult = (int)ScriptResults.Success;
}
else
{
// Execution of child package failed
Dts.Events.FireError(0, "Child Package - " + Dts.Variables["User::SSISDBPackagePath"].Value.ToString(), "There may be error messages posted before this with more information about the failure.", string.Empty, 0);
Dts.TaskResult = (int)ScriptResults.Failure;
}
}
catch (Exception ex)
{
// Execution of child package failed (server timeout, can't find package, etc.)
Dts.Events.FireError(0, "Child Package - " + Dts.Variables["User::SSISDBPackagePath"].Value.ToString(), "Failed: " + ex.Message, string.Empty, 0);
Dts.TaskResult = (int)ScriptResults.Failure;
}
}
#region ScriptResults declaration
/// <summary>
/// This enum provides a convenient shorthand within the scope of this class for setting the
/// result of the script.
///
/// This code was generated automatically.
/// </summary>
enum ScriptResults
{
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
};
#endregion
}
}
 |
| The result in Visual Studio |
 |
| Two executions |