asked on

Simple C# script in SQL Server 2008 SSIS Package not writing to Variable

Hi,
I am new to C# scripting and I have written the following script.
The purpose of the script is to write a 1 to a variable if a file is detected. If a file is detected, the Precedence constraint allows the next step ," Write Time to Table", in the process to execute.
This is not happening.
I have put in some debug steps which show that the path exists but the variable value is not being changed to 1.
a copy of the script is below,
I have added screengrabs of the the debug outputs ,
the scrpt task editor window,
the precedence constraint,
the folder where the file is located,

Any guidance appreciated,
Thanks

/*
   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;
using System.IO;

namespace ST_a400c24e782648ea90176536249cd677.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

               public void Main()
        {
            // TODO: Add your code here
              String Filepath = Dts.Variables["User::DirectoryPath"].Value.ToString() + Dts.Variables["User::FileName"].Value.ToString();
            if (
                File.Exists(Filepath))
                {
                Dts.Variables["User::FileExistsFlg"].Value = 1;
            }
            String FileExistsFlag = Dts.Variables["User::FileExistsFlg"].Value.ToString();
            MessageBox.Show(Filepath);//Show the folder path with file name
            MessageBox.Show(FileExistsFlag);//show the flag value, 1 for exists and 0 for not exists
           Dts.TaskResult = (int)ScriptResults.Success;  
        }
          
        }
    }

Open in new window

ASKER CERTIFIED SOLUTION

Surendra Nath

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

blossompark

ASKER

Hi Surrendra Ganti,
Thanks for your very informative comment, it is very useful to me as a beginner C# scripter !!
Thank you again

I used the following to resolve my issue.
/*
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;
using System.IO;

namespace ST_a400c24e782648ea90176536249cd677.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

public void Main()
{
// TODO: Add your code here
String[] DEPOSFiles = Directory.GetFiles(Dts.Variables["User::DirectoryPath"].Value.ToString());

if (
// File.Exists(Filepath))
(DEPOSFiles.Length != 0))
{
Dts.Variables["User::FileExistsFlg"].Value = 1;
}
else
{
Dts.Variables["User::FileExistsFlg"].Value = 0;
}
String FileExistsFlag = Dts.Variables["User::FileExistsFlg"].Value.ToString();
//MessageBox.Show(Filepath);//Show the folder path with file name
//MessageBox.Show(FileExistsFlag);//show the flag value, 1 for exists and 0 for not exists
Dts.TaskResult = (int)ScriptResults.Success;
}

}
}