Check if File exist - Script Task
-
I am trying to write a logic within SSIS 2014 using script task (script lang:Microsoft Visual C# 2012) where it looks if text file exist or not and returns a value (1,0) in varibale : User::FileExistFlg
Below is the code that i have used and i am not sure why its giving me an error.
#region Namespaces
using System;
using System.IO;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
#endregion
namespace ST_d30d883df9b74e2ca208991af5660938
{
public void Main()
{
// TODO: Add your code here
String Filepath = Dts.Variables["User::FileLocation"].Value.ToString() + Dts.Variables["User::FileName"].Value.ToString();
if (
File.Exists(Filepath))
{
Dts.Variables["User::FileExistFlg"].Value = 1;
}
MessageBox.Show(Filepath);
MessageBox.Show(Dts.Variables["User::FileExistFlg"].Value.ToString());
Dts.TaskResult = (int)ScriptResults.Success;
}
And below is the error i am getting
at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor)
at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments)
at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams)
at Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTATaskScriptingEngine.ExecuteScript()
Thank you for the help in advance!
-
Appending strings together to make a filename is tricky; if you are not careful/consistent with ending slashes, you'll get invalid paths.
i would suggest using Path.combine to handle that issue for you.
public void Main()
{
// TODO: Add your code here
--System.IO.Path.Combine
String Filepath = System.IO.Path.Combine(Dts.Variables["User::FileLocation"].Value.ToString() , Dts.Variables["User::FileName"].Value.ToString());
if (
File.Exists(Filepath))
{
Dts.Variables["User::FileExistFlg"].Value = 1;
}
MessageBox.Show(Filepath);
MessageBox.Show(Dts.Variables["User::FileExistFlg"].Value.ToString());
Dts.TaskResult = (int)ScriptResults.Success;
}
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Thank you for the quick response! I am still getting error as below:
at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor)
at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments)
at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams)
at Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTATaskScriptingEngine.ExecuteScript()
Attached is the code: (edited: screenshot is not working, added code)
public void Main()
{
// TODO: Add your code here
String Filepath = System.IO.Path.Combine(Dts.Variables["User::FileLocation"].Value.ToString(), Dts.Variables["User::FileName"].Value.ToString());
if (
File.Exists(Filepath))
{
Dts.Variables["User::FileExistFlg"].Value = 1;
}
MessageBox.Show(Filepath);
MessageBox.Show(Dts.Variables["User::FileExistFlg"].Value.ToString());
Dts.TaskResult = (int)ScriptResults.Success;
}
Not sure, if its an access issue . if you see on the top of the screen shot attached i cant even play the debug button.
-
Just checking to see if you remembered to put the variable names you're using in your script into the appropriate READ ONLY or READ/WRITE variable list on the General tab of the dialog that comes up when you Edit the Script Task?
EDIT: I'm specifically referring to "User::FileLocation" and "User::FileName" going into the READ ONLY box, and "User::FileExistFlg" going into the READ/WRITE box.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) -
Thanks for your response! Yes i did and trust me i have checked it 1000 times !:)
What does this error mean? Thanks!
-
put your code in a try catch, and explicitly bubble up any errors that might occur;
you'll get a better error description than you are currently receiving:
public void Main()
{
[highlight="#ffff11"]try
{[/highlight]
// TODO: Add your code here
--System.IO.Path.Combine
String Filepath = System.IO.Path.Combine(Dts.Variables["User::FileLocation"].Value.ToString() , Dts.Variables["User::FileName"].Value.ToString());
if (
File.Exists(Filepath))
{
Dts.Variables["User::FileExistFlg"].Value = 1;
}
MessageBox.Show(Filepath);
MessageBox.Show(Dts.Variables["User::FileExistFlg"].Value.ToString());
Dts.TaskResult = (int)ScriptResults.Success;
[highlight="#ffff11"]}
catch (Exception ex)
{
Dts.Events.FireError(-1, "Main", ex.Message, "", 0);
Dts.TaskResult = (int)ScriptResults.Failure;
}[/highlight]
}
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Thank You so much and it helped!! It gave me the exact error and very embarrassed to admit it - it was typo in variable name :blush:
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply