SolvedPrivate

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

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Sum of items in two tables not equal. 5 44
SQL Throw Error 7 32
"The resource cannot be found" 1 22
Visual Studio 2015 auto inserted code 12 66
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
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…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

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