Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
SolvedPrivate

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

Posted on 2014-01-29
12
Medium Priority
?
59 Views
Last Modified: 2016-02-10
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
Comment
Question by:blossompark
  • 7
  • 5
12 Comments
 
LVL 16

Expert Comment

by:Surendra Nath
ID: 39817562
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
 

Author Comment

by:blossompark
ID: 39817790
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
 
LVL 16

Expert Comment

by:Surendra Nath
ID: 39818110
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:blossompark
ID: 39818289
same issue
conversion
0
 
LVL 16

Expert Comment

by:Surendra Nath
ID: 39818701
oops i got it..
it is an extra parenthesis

try the below one

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

Author Comment

by:blossompark
ID: 39820171
Now error is
Operator '!=' cannot be applied to operands of type 'string' and 'char'
0
 

Author Comment

by:blossompark
ID: 39820185
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
 
LVL 16

Expert Comment

by:Surendra Nath
ID: 39820264
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
 

Author Comment

by:blossompark
ID: 39820324
still getting the same issue;
use of unassigned local variable 'file
at " file" in the WHILE statement
file error
0
 
LVL 16

Accepted Solution

by:
Surendra Nath earned 2000 total points
ID: 39820436
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
 

Author Comment

by:blossompark
ID: 39820544
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
 

Author Closing Comment

by:blossompark
ID: 39820864
Hi Surendra,
thanks for your perseverance with this,,,,it's working perfectly!!
Made my day!!..
Thank you!!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
I have a large data set and a SSIS package. How can I load this file in multi threading?
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…

571 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