November 16, 2015 at 3:08 pm
Hi all,
I have a script task in y SSIS package and I'm trying to check if the excel file is opened or not using below code. But it's not working fine -(always give me file is opened)
can anyone help me why ? thank you so much!
In the script task configuration:
ReadOnlyVariables ---Filename,FilePath,Sourcefile
ReadWriteVariables ---Filecount
public void Main()
{
Boolean FileLocked = true;
Boolean ShowLockWarning = true;
int count = 0;
String dirname = Dts.Variables["User::varMRRFilePath"].Value.ToString();
//MessageBox.Show(Dts.Variables["User::SourcePath"].Value.ToString());
string[] fileEntries = Directory.GetFiles(Dts.Variables["User::varMRRFilePath"].Value.ToString());
foreach (string fileName in fileEntries)
{
var file = fileName.Replace(dirname, "");
file = file.Replace("\\", "");
Dts.Variables["User::varMRRFileName"].Value = file.ToString();
MessageBox.Show(" Mine:" + file.ToString());
break;
}
String filePath = Dts.Variables["User::varMRRFilePath"].Value.ToString();
String fileNames = Dts.Variables["User::varMRRFileName"].Value.ToString();
String sourceFile = Path.Combine(filePath, fileNames);
//get the source file and path
Dts.Variables["User::varMRRSourceFile"].Value = fileNames;
//MessageBox.Show(Dts.Variables["User::varSourceFile"].Value.ToString());
//Dts.TaskResult = (int)ScriptResults.Success;
// Check if the file isn't locked by an other process
try
{
// Try to open the file. If it succeeds, set variable to false and close stream
FileStream fs = new FileStream(Dts.Variables["User::varMRRSourceFile"].Value.ToString(), FileMode.Open);
FileLocked = false;
fs.Close();
Dts.TaskResult = (int)ScriptResults.Success;
MessageBox.Show("File is closed");
}
catch (IOException ex)
{
// If opening fails, it's probably locked by an other process. This is the exact message:
// System.IO.IOException: The process cannot access the file 'D:\example.csv' because it is being used by another process.
// Log locked status (once)
if (ShowLockWarning)
{
Dts.Events.FireWarning(0, "File Lock Check", "File locked: " + ex.Message, string.Empty, 0);
}
ShowLockWarning = false;
count = 1;
MessageBox.Show("File is opened");
Dts.Variables["User::varMRRFileOpenCount"].Value = count;
// Wait two seconds before rechecking
Thread.Sleep(2000);
}
catch (Exception ex)
{
// Catch other unexpected errors and break the while loop
Dts.Events.FireError(0, "File Lock Check", "Unexpected error: " + ex.Message, string.Empty, 0);
Dts.TaskResult = (int)ScriptResults.Failure;
}
Dts.TaskResult = (int)ScriptResults.Success;
}
#region ScriptResults declaration
/// <summary>
/// This enum provides a convenient shorthand within the scope of this class for setting the
/// result of the script.
///
/// This code was generated automatically.
/// </summary>
enum ScriptResults
{
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
};
#endregion
November 17, 2015 at 10:37 am
OK, I wrote a simple SSIS package to try this out.
My solution uses the code from this page, without modification.
I added a package parameter called FilePath, which I set to the path of a local Excel file (i:\temp\Garbage.xlsx).
Next I created a Script Task. Add the following row to your Namespaces section:
using System.IO;
Change your Main() function as follows:
public void Main()
{
string path = (string)Dts.Variables["$Package::FilePath"].Value;
FileInfo f = new FileInfo(path);
if (IsFileLocked(f))
{
MessageBox.Show("File Locked");
}
else
{
MessageBox.Show("File not Locked");
}
Dts.TaskResult = (int)ScriptResults.Success;
}
And add the IsFileLocked() function so that it appears directly after Main()
So you have
public void Main()
{
}
protected virtual bool IsFileLocked(FileInfo file)
{
}
And then run your package. Seems to work OK.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
November 18, 2015 at 9:04 am
thanks for your apply!
Can you provide the code for IsFileLocked functioin ?
November 18, 2015 at 9:08 am
caojunhe24 (11/18/2015)
thanks for your apply!Can you provide the code for IsFileLocked functioin ?
Follow the link in my post and you can take it from there.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
November 18, 2015 at 9:19 am
I using this code based on your suggestion. But always give me file is open error.
public void Main()
{
int count = 0;
string path = (string)Dts.Variables["User::varMRRSourceFile"].Value;
FileInfo f = new FileInfo(path);
if (IsFileLocked(f))
{
count = 1;
Dts.Variables["User::varMRRFileOpenCount"].Value = count;
MessageBox.Show("File is open");
}
else
{
count = 0;
MessageBox.Show("File is closed");
}
Dts.TaskResult = (int)ScriptResults.Success;
}
protected virtual bool IsFileLocked(FileInfo file)
{
FileStream stream = null;
try
{
stream = file.Open(FileMode.Open, FileAccess.Read, FileShare.None);
}
catch (IOException)
{
//the file is unavailable because it is:
//still being written to
//or being processed by another thread
//or does not exist (has already been processed)
return true;
}
finally
{
if (stream != null)
stream.Close();
}
//file is not locked
return false;
}
November 18, 2015 at 9:42 am
Error? Or message box?
I tested it this morning and it worked perfectly.
Try with an Excel file on your local hard disk. Maybe you have some network or permissions issues going on.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
November 19, 2015 at 9:47 am
I already fix the problem thank you for your help.
1 more question here.
I'm trying to schedule my SSIS package and already deployed my as package deployment model into SQL.
after I schedule this package and click" start job at step"
then it give me the error-Message
Executed as user: MEDASSURANT\BPA_UAT_DS. Microsoft (R) SQL Server Execute Package Utility Version 11.0.3436.0 for 32-bit Copyright (C) Microsoft Corporation. All rights reserved. Started: 10:38:10 AM Error: 2015-11-19 10:38:17.30 Code: 0x00000001 Source: FileCount Description: Exception has been thrown by the target of an invocation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 10:38:10 AM Finished: 10:38:17 AM Elapsed: 6.926 seconds. The package execution failed. The step failed.
-this error message tell me my package run failed at filecount task but when I try my package it works fine. Do you know what's the problem? Filecount task is a script task which is contain system.data name space is this error regarding access permission or something?
November 19, 2015 at 10:08 am
if the script task is counting files on some file share like \\servername\sharename, does the account[MEDASSURANT\BPA_UAT_DS] have access to that share? is that a domain account?
when you run it locally, it's your account credentials, but when it's a job, it's not the same person,so not the same permissions.
Lowell
November 19, 2015 at 11:24 am
everyone has access permission on that folder and if I create a job then will use my account credential to run that job anyway I guess. And like I said when I run this package it's working fine
November 20, 2015 at 5:43 am
caojunhe24 (11/19/2015)
everyone has access permission on that folder and if I create a job then will use my account credential to run that job anyway I guess. And like I said when I run this package it's working fine
I don't believe that is true.the error message says the user [MEDASSURANT\BPA_UAT_DS] does not have access. can you double check that account?
Lowell
November 20, 2015 at 6:12 am
Can you confirm that you are using a UNC path (\\server\share\...) and NOT a drive letter (X:\data\...) ?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
November 20, 2015 at 7:13 am
in my command line : /SQL "\"\Driver\"" /SERVER 1k53rw1 /X86 /CHECKPOINTING OFF /REPORTING E
November 20, 2015 at 7:16 am
And I saw some people had same issue with job agent and they solved problem by making folder as shared folder. But the point is my folder is in the public drive which means this is already a shared folder.
November 20, 2015 at 7:24 am
caojunhe24 (11/20/2015)
And I saw some people had same issue with job agent and they solved problem by making folder as shared folder. But the point is my folder is in the public drive which means this is already a shared folder.
My question did not relate to your command line.
When you reference the shared folder in your package, are you using a UNC path?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
November 20, 2015 at 7:25 am
caojunhe24 (11/20/2015)
And I saw some people had same issue with job agent and they solved problem by making folder as shared folder. But the point is my folder is in the public drive which means this is already a shared folder.
shared folders are only shared to logins in the domain. local accounts will not have access, which is my point. typically a share go to the AD group mydomain\[Users] or mydomain\[Registered Users], for example .
if your domain is named MEDASSURANT, i would agree, but i got the feeling MEDASSURANT was a machine name, and thus excluded form any shares.
Lowell
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply