Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Private
  • Views: 65
  • Last Modified:

Error message “Use of unassigned local variable file“ with SQL Server 2008 SSIS C# Script

Hi ,
the C# script below  throws exceptions   when  “no file is found”.
(I have included an explanation of the code below also, mainly courtesy of Surendra Ganti)
I want to make an amendment to this script whereby instead of throwing an exception, I want to write to a variable,
Effectively replacing
throw new ArgumentException("No source data files exist that match the LogicalFileName(" + Dts.Variables["LogicalFileName"].Value.ToString() + ")", "LogicalFileName");

Open in new window

with
Dts.Variables["User::FileExistsFlg"].Value = 0;

Open in new window

The issue I have is, when I comment out any of the “throw” lines, I get  an error
Use of unassigned local variable ‘file’
This is related to the  FileInfo type instance file.
I would appreciate any guidance with this,
 thank you.
/*
   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.IO;
using System.Data;
using System.Collections.Generic;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.Text.RegularExpressions;

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

        private Int32 fileIndex;
        public void Main()
        {
            // TODO: Add your code here
                   String[] DEPOSFiles = Directory.GetFiles(Dts.Variables["SourceDataFileFolder"].Value.ToString(), "CMGDEPOS*");
                      MessageBox.Show((DEPOSFiles.Length).ToString());
            if (DEPOSFiles.Length != 0)
            {
                for (int i = 0; i < DEPOSFiles.Length; i++)
                {
                    File.Move(DEPOSFiles[i], DEPOSFiles[i].Replace("DEPOS", "Corpos"));
                }
            }
            String[] opicsFiles = Directory.GetFiles(Dts.Variables["SourceDataFileFolder"].Value.ToString(), Dts.Variables["LogicalFileName"].Value.ToString() + "_*");
                   FileInfo file;
            MessageBox.Show((opicsFiles.Length).ToString());
            if (opicsFiles.Length != 1)
            {
                try
                {
                    Int32 fileLen = 100;
                    for (int i = 0; i < opicsFiles.Length; i++)
                    {
                        if (opicsFiles[i].Length < fileLen)
                        {
                            fileLen = opicsFiles[i].Length;
                            fileIndex = i;
                        }
                    }
                    MessageBox.Show((Dts.Variables["SourceDataFileFolder"].Value.ToString()));
                    MessageBox.Show((Dts.Variables["LogicalFileName"].Value.ToString()));
                    if (Regex.Matches(opicsFiles[fileIndex], "_").Count == Regex.Matches(Dts.Variables["LogicalFileName"].Value.ToString(), "_").Count + 1)
                    {
                        file = new FileInfo(opicsFiles[fileIndex]);


                    }
                    else
                    {
                        //throw new ArgumentException("No source data files exist that match the LogicalFileName(" + Dts.Variables["LogicalFileName"].Value.ToString() + ")", "LogicalFileName");
                         // Dts.Variables["User::FileExistsFlg"].Value = 0;
                    }
                }
                catch
                {
                    throw new ArgumentException("No source data files exist that match the LogicalFileName(" + Dts.Variables["LogicalFileName"].Value.ToString() + ")", "LogicalFileName");
                    // Dts.Variables["User::FileExistsFlg"].Value = 0;
                }
            }
            else
            {
                if (Regex.Matches(opicsFiles[0], "_").Count == Regex.Matches(Dts.Variables["LogicalFileName"].Value.ToString(), "_").Count + 1)
                {
                    file = new FileInfo(opicsFiles[0]);
                }
                else
                {
                    throw new ArgumentException("No source data files exist that match the LogicalFileName(" + Dts.Variables["LogicalFileName"].Value.ToString() + ")", "LogicalFileName");
                    // Dts.Variables["User::FileExistsFlg"].Value = 0;
                }
            }
            while (IsFileLocked(file))
            {
            }
            Dts.Variables["SourceDataFilePath"].Value = file.FullName;
           
            if
        (file.Exists)
            {
                Dts.Variables["User::FileExistsFlg"].Value = 1;
            }
            else
            {
                Dts.Variables["User::FileExistsFlg"].Value = 0;
            }
          
            Dts.TaskResult = (int)ScriptResults.Success;
        }
        protected virtual bool IsFileLocked(FileInfo file)
        {
            FileStream stream = null;
            try
            {
                stream = file.Open(FileMode.Open, FileAccess.ReadWrite, FileShare.None);
            }
            catch (IOException)
            {
                return true;
            }
            finally
            {
                if (stream != null)
                    stream.Close();
            }
            return false;
        }
    }
}

Open in new window


Script Explanation
a string array named DEPOSFiles is created and populates with all FileNames beginning with CMGDEPOS_ at source SourceDataFileFolder,
there may not be any files at the location SourceDataFileFolder fulfilling  the requirement,in that case the Array will be empty.
If any filenamess are present in the array, it replaces the characters DEPOS in the title with Corpos.
All entries in the array DEPOSFiles will start with the name CMGCorpos_          
(The value at variable LogicalFileName is CMGCorpos)
The  opicsFiles array will contain all the files that matches the criteria in the source directory..

if there are more than 1 file in that directory

-- then it will try to find the file with the shortest overall file name (including the extension).
for example 12.txt, abc.txt,abcd.txt abcd1.txt abcd0.txt, out of these files, the 12.txt is considered as the shortest file by your code in the next code.

at the same time it keeps the number (index) where the filename is stored in the opics array

Now after the loop finishes off then, it is checking, if the opics array matches with the logical file name criteria given in the user variable logical file name.

if that is the case then the file information about the file above will be taken and put in the object file.


if there is only one file in the logical directory then the same above process will repeat but it will just pick the only file.

If the file name does not match with the logical file name then an error will be thrown
or else the script will result

The script will wait until the file is released of any locks (locks can be placed on a file, for example ,if you open the file in a word document while this script is executing it will be locked by lock and it will get struck in the while loop below
while(isFileLocked)

once the locks are released then source data file path will be set with the file name determined above.
0
blossompark
Asked:
blossompark
  • 7
  • 5
1 Solution
 
Surendra NathTechnology LeadCommented:
The reason for this is your while isfilelocked function is using the object file which is not initialized, as it is being initialized only when you hav eat least one file.

As you are already setting the file exists to zero in the catch block there is no need to run while isfilelocked and if file.exists.

What I would do is

If (dots.variables(fil exists) != 0)
While isfilelocked
....
If (file.exists)
....
....

I have not put the code correctly over here, it should just be used as a reference.
0
 
blossomparkAuthor Commented:
Hi,
thanks for your comments, i am getting an error  when I put in the IF statement

if (Dts.Variables["User::FileExistsFlg"].Value)!=0)
            
            while (IsFileLocked(file))
            {
            }
            Dts.Variables["SourceDataFilePath"].Value = file.FullName;

            if
        (file.Exists)
            {
                Dts.Variables["User::FileExistsFlg"].Value = 1;
            }
            else
            {
                Dts.Variables["User::FileExistsFlg"].Value = 0;
            }
          
            Dts.TaskResult = (int)ScriptResults.Success;

Open in new window


Cannot implicitly convert type object to bool, an explicit conversion exists(are you missing a cast?)

can you give me guidance on this? thank you
0
 
Surendra NathTechnology LeadCommented:
change the below statement
if (Dts.Variables["User::FileExistsFlg"].Value)!=0)

Open in new window


to

if (Dts.Variables["User::FileExistsFlg"].Value.ToString().Trim())!='0')

Open in new window

0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
blossomparkAuthor Commented:
same issue
conversion
0
 
Surendra NathTechnology LeadCommented:
oops i got it..
it is an extra parenthesis

try the below one

if (Dts.Variables["User::FileExistsFlg"].Value.ToString().Trim()!='0')
0
 
blossomparkAuthor Commented:
Now error is
Operator '!=' cannot be applied to operands of type 'string' and 'char'
0
 
blossomparkAuthor Commented:
changed to
  // if (Dts.Variables["User::FileExistsFlg"].Value)!=0)
            if (Dts.Variables["User::FileExistsFlg"].Value.ToString().Trim()!="")
            //if (Dts.Variables["User::FileExistsFlg"].Value.ToString().Trim())!='0')
           
            while (IsFileLocked(file))
            {
            }
            Dts.Variables["SourceDataFilePath"].Value = file.FullName;

            if
        (file.Exists)
            {
                Dts.Variables["User::FileExistsFlg"].Value = 1;
            }
            else
            {
                Dts.Variables["User::FileExistsFlg"].Value = 0;
            }
          
            Dts.TaskResult = (int)ScriptResults.Success;

Open in new window


which parses the if statement
but now getting the error
use of unassigned local variable 'file
at " file" in the WHILE statement
0
 
Surendra NathTechnology LeadCommented:
try the below code

  // if (Dts.Variables["User::FileExistsFlg"].Value)!=0)
            if (Dts.Variables["User::FileExistsFlg"].Value.ToString().Trim()!="0")
            //if (Dts.Variables["User::FileExistsFlg"].Value.ToString().Trim())!='0')
           
            while (IsFileLocked(file))
            {
            }
            Dts.Variables["SourceDataFilePath"].Value = file.FullName;

            if
        (file.Exists)
            {
                Dts.Variables["User::FileExistsFlg"].Value = 1;
            }
            else
            {
                Dts.Variables["User::FileExistsFlg"].Value = 0;
            }
          
            Dts.TaskResult = (int)ScriptResults.Success;

Open in new window

0
 
blossomparkAuthor Commented:
still getting the same issue;
use of unassigned local variable 'file
at " file" in the WHILE statement
file error
0
 
Surendra NathTechnology LeadCommented:
change the below two lines
            String[] opicsFiles = Directory.GetFiles(Dts.Variables["SourceDataFileFolder"].Value.ToString(), Dts.Variables["LogicalFileName"].Value.ToString() + "_*");
                   FileInfo file;

Open in new window


to
            String[] opicsFiles = Directory.GetFiles(Dts.Variables["SourceDataFileFolder"].Value.ToString(), Dts.Variables["LogicalFileName"].Value.ToString() + "_*");
                   FileInfo file = new FileInfo(Dts.Variables["SourceDataFileFolder"].Value.ToString());

Open in new window

0
 
blossomparkAuthor Commented:
Hi, Great that's parsing ok now!!
So if a file exists, the file variable
FileInfo file = new FileInfo(Dts.Variables["SourceDataFileFolder"].Value.ToString());

 will be overwritten by the second declaration?
  file = new FileInfo(opicsFiles[fileIndex]);

so file will have the correct information?
0
 
blossomparkAuthor Commented:
Hi Surendra,
thanks for your perseverance with this,,,,it's working perfectly!!
Made my day!!..
Thank you!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 7
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now