SolvedPrivate

Interpreting SQL Server 2008 SSIS C# script

Posted on 2014-01-27
7
25 Views
Last Modified: 2016-02-10
Hi,
I have the following C# script that   I need to make a "small" change to,
 and I need to understand what it is doing before I proceed.

I have begun trying to interpret it and here is what I have come up with so far. Beginning at "public void Main ()"
a string array named DEPOSFiles is created  and populates with all FileNames beginning with CMGDEPOS_ found at the location stored in the source variable SourceDataFileFolder,
if any filenames are  present in the array, it replaces the name DEPOS in the title with Corpos.
a String Array named opicsFiles is created and is  populated from the source SourceDataFileFolder with any files titles with the value located in the variable LogicalFileName
(The value at variable LogicalFileName is CMGCorpos)

If  the opicsFiles array does not contain exactly 1 value, the "Try" commences

This is as far as I have got so far,  I find the next bit  confusing, I will muddle through and post what I find but if you can help in any way I would greatly appreciate it,
Thanks

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_e72b194f12ee4fbab7090f4187b09a3c.csproj
{
    [System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {

        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };

        private Int32 fileIndex;

        public void Main()
                {
            String[] DEPOSFiles = Directory.GetFiles(Dts.Variables["SourceDataFileFolder"].Value.ToString(), "CMGDEPOS_*");
            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;
            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;
                        }
                    }
                    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");
                    }
                }
                catch
                {
                    throw new ArgumentException("No source data files exist that match the LogicalFileName(" + Dts.Variables["LogicalFileName"].Value.ToString() + ")", "LogicalFileName");
                }
            }
            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");
                }
            }
            while (IsFileLocked(file))
            {
            }
            Dts.Variables["SourceDataFilePath"].Value = file.FullName;
            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

0
Comment
Question by:blossompark
  • 6
7 Comments
 
LVL 16

Accepted Solution

by:
Surendra Nath earned 500 total points
ID: 39813659
ok, from 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
 

Author Comment

by:blossompark
ID: 39814408
Hi Surendra Ganti,
Thank you for your excellent explanation, it is greatly appreciated.

So my understanding is , the script is  looking for a single file name and if that file name exists, it will store it in the variable named  file?
One Question, what happens if the file does not exist?

The amendment I need to make to this script is to set a variable depending on whether or not this file exists.
The code I am thinking of adding is
if (
                  
        (File.Length != 0))
              {
                  Dts.Variables["User::FileExistsFlg"].Value = 1;
              }
              else
              {
                  Dts.Variables["User::FileExistsFlg"].Value = 0;

Open in new window

and I will position this code
just before the IsFileLocked declaration?
          Dts.Variables["SourceDataFilePath"].Value = file.FullName;
if (
                  
        (File.Length != 0))
              {
                  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: 39814462
OK, that amendment code will not work, getting the Message

SYSTEM.IO.File does not contain a definition for Length
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

Author Comment

by:blossompark
ID: 39814476
also getting error with
 (File.Exists)
            {
                Dts.Variables["User::FileExistsFlg"].Value = 1;
            }
            else
            {
                Dts.Variables["User::FileExistsFlg"].Value = 0;
            }

Open in new window


Error
Cannot convert method group 'Exists' to non-delegate type 'bool'
0
 

Author Comment

by:blossompark
ID: 39814488
does not fix the issue, but the  "F" should be lower case
0
 

Author Comment

by:blossompark
ID: 39814492
This parses successfully
 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 Closing Comment

by:blossompark
ID: 39817154
Thankyou for your  excellent and detailed explanation...if i have any other issues i will open a new question
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Lessons learned during ten years of interviewing for SQL Server Integration Services (SSIS) and other Extract-Transform-Load (ETL) contract roles and two years of staff manager interviewing contractors.
Performance in games development is paramount: every microsecond counts to be able to do everything in less than 33ms (aiming at 16ms). C# foreach statement is one of the worst performance killers, and here I explain why.
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…

895 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

16 Experts available now in Live!

Get 1:1 Help Now