SolvedPrivate

Interpreting SQL Server 2008 SSIS C# script

Posted on 2014-01-27
7
24 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
Comment Utility
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
Comment Utility
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
Comment Utility
OK, that amendment code will not work, getting the Message

SYSTEM.IO.File does not contain a definition for Length
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:blossompark
Comment Utility
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
Comment Utility
does not fix the issue, but the  "F" should be lower case
0
 

Author Comment

by:blossompark
Comment Utility
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
Comment Utility
Thankyou for your  excellent and detailed explanation...if i have any other issues i will open a new question
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Suggested Solutions

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…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

744 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

8 Experts available now in Live!

Get 1:1 Help Now