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
SolvedPrivate

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

Posted on 2014-01-26
2
59 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
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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

This article aims to explain the working of CircularLogArchiver. This tool was designed to solve the buildup of log file in cases where systems do not support circular logging or where circular logging is not enabled
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

839 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