SolvedPrivate

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

Posted on 2014-01-29
12
46 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
 

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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 

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 500 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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Suggested Solutions

Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

759 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now