SolvedPrivate

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

Posted on 2014-01-26
2
61 Views
Last Modified: 2016-02-10
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

flow1.jpgflow-2.jpgprecedence-constraint.jpgscript-task-editor.jpgfolder.jpg
0
Comment
Question by:blossompark
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 16

Accepted Solution

by:
Surendra Nath earned 500 total points
ID: 39810686
Your code looks good...

There should not be an issue at all
so, please start looking to the below stuff

1) Ensure that the file exists from the command prompt
     --> i.e. by doing dir c:\test\ProcessCompleted.txt
     --> Reason for doing this test will be sometimes we might encounter a space after the extension, just to rule that out.

2) Try to trim spaces for the FilePath variable in the C# code, before you can File.exists method , you can use trim() function
 after the below statement
String Filepath = Dts.Variables["User::DirectoryPath"].Value.ToString() + Dts.Variables["User::FileName"].Value.ToString();

add the below statement
Filepath = Filepath.Trim();

3) If the file is good, then What I ask of you is to create a share folder in the C: or D: drive and give permission to everyone (both read  and write) access and put the file in this location and run the code again and see what's happening now
      --> reason: we want to ensure that permissions are not coming into play here

4) Next one will be, is your SSIS running on a different machine than the one that you are thinking off ..

In order to ensure that this is not the case, add the below statement to your script and check the computer name is same as the one that you are thinking where the file exists

MessageBox.Show(System.Environment.MachineName.ToString());
0
 

Author Closing Comment

by:blossompark
ID: 39811562
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;  
        }
         
        }
    }
0

Featured Post

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Performance in games development is paramount: every microsecond counts to be able to do everything in less than 33ms (aiming at 16ms). C# foreach statement is one of the worst performance killers, and here I explain why.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

740 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question