SolvedPrivate

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

Posted on 2014-01-29
12
54 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
[X]
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
  • 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
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!

 

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

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
How do I hide MVVM menu items in WPF/XAML? 1 33
SQL Server Shrink hurting performance? 4 38
How to force output to ascii 2 35
Build a string of emails from a gridview 2 14
My client sends a request to me that they want me to load data, which will be returned by Web Service APIs, and do some transformation before importing to database. In this article, I will provide an approach to load data with Web Service Task and X…
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

756 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