SSIS, Sql Server Integration Services, is a full functional development environment. Unfortunately when we are building a package, before register and release the package, we need to Log, trace or debug the script. This process is a MUST if we are using a script task, a task which give you the availability to work directly with C# or VB.NET.

Package Example.

Create a simple SSIS project and insert a Sequence Container, then in the sequence container insert a Script Task and choose the C# language.

script log

Please don’t take care about my screenshot, I know the package fails, but because I toke a screenshot and I’m **lazy …
** This is just an example, ok?

 

 

 

Now let’s create a couple of Global Variables in our package and let’s call it: VARIABLE01 and VARIABLE02.

Finally let’s go to edit our script task (right click on the task, edit script in the properties).

Some C# code.

First of all in out package class let’s go to create a function that return our variable value.

   1: private string ReturnVariableValue(string name)
   2: {
   3:     string value = string.Empty;
   4:     value = (string)Dts.Variables[name].Value;
   5:     return value;
   6: }

And now, let’s write this if block in the main routine:

   1: if(ReturnVariableValue("VARIABLE01") == string.empty
   2: {
   3:     WriteLog(string.format("The variable value is {0}",
   4:       ReturnVariableValue("VARIABLE01"));
   5:     Dts.TaskResult = (int)ScriptResults.Success;
   6: }else{
   7:     WriteLog("The variable is empty!!");
   8:     Dts.TaskResult = (int)ScriptResults.Success;
   9:  
  10: }

Now we are ready to view how we can interact with the users in the SSIS package.
It’s very important that in the main routine you assign a result for the package

execution, don’t forget!!

Write to the Log, to the Output or to the Windows.

Well, now we need to build our WriteLog function. We have 3 way to do that.

The first one is to write directly on the Log:

   1: Dts.Log(message, 999, null);

The second one is to write to the Output, so when you will run the package in the Business Intelligence IDE, you will see the message in the output console:

   1: Dts.Events.FireInformation(
   2:   -1, 
   3:   "Check Variables", 
   4:   message, 
   5:   string.Empty, 
   6:   -1, 
   7:   ref False);

Or you can send a MessageBox:

   1: System.Windows.Forms.MessageBox.Show(message);