simplyfemales
asked on
SSIS Script Task Error
SSIS Script Task.
I am using a ForEach Container to get the file as a variable (User::foundFiles). I am using it in a previously built DataFlow Task so I know it works just fine for that.
Within this Script it's throwing an error saying "Empty path name is not legal"
What am I missing? Can't figure out why it's not working.
Error:
Error: System.Reflection.TargetIn vocationEx ception: Exception has been thrown by the target of an invocation. ---> System.ArgumentException: Empty path name is not legal.
at System.IO.FileStream.Init( String path, FileMode mode, FileAccess access, Int32 rights, Boolean useRights, FileShare share, Int32 bufferSize, FileOptions options, SECURITY_ATTRIBUTES secAttrs, String msgPath, Boolean bFromProxy)
at System.IO.FileStream..ctor (String path, FileMode mode, FileAccess access, FileShare share, Int32 bufferSize, FileOptions options)
at System.IO.StreamWriter.Cre ateFile(St ring path, Boolean append)
at System.IO.StreamWriter..ct or(String path, Boolean append, Encoding encoding, Int32 bufferSize)
at System.IO.StreamWriter..ct or(String path, Boolean append, Encoding encoding)
at System.IO.File.WriteAllTex t(String path, String contents, Encoding encoding)
at ST_c71263698669469292bfc5b 447ef5fa6. csproj.Scr iptMain.Ma in()
--- End of inner exception stack trace ---
at System.RuntimeMethodHandle ._InvokeMe thodFast(O bject target, Object[] arguments, SignatureStruct& sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner)
at System.RuntimeMethodHandle .InvokeMet hodFast(Ob ject target, Object[] arguments, Signature sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner)
at System.Reflection.RuntimeM ethodInfo. Invoke(Obj ect obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture, Boolean skipVisibilityChecks)
at System.Reflection.RuntimeM ethodInfo. Invoke(Obj ect obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
at System.RuntimeType.InvokeM ember(Stri ng name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams)
at System.Type.InvokeMember(S tring name, BindingFlags invokeAttr, Binder binder, Object target, Object[] args, CultureInfo culture)
at Microsoft.SqlServer.Dts.Ta sks.Script Task.VSTAT askScripti ngEngine.E xecuteScri pt()
Code:
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Ru ntime;
using System.Windows.Forms;
namespace ST_c71263698669469292bfc5b 447ef5fa6. csproj
{
[System.AddIn.AddIn("Scrip tMain", Version = "1.0", Publisher = "", Description = "")]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Ta sks.Script Task.VSTAR TScriptObj ectModelBa se
{
#region VSTA generated code
enum ScriptResults
{
Success = Microsoft.SqlServer.Dts.Ru ntime.DTSE xecResult. Success,
Failure = Microsoft.SqlServer.Dts.Ru ntime.DTSE xecResult. Failure
};
#endregion
public void Main()
{
string fileContent = System.IO.File.ReadAllText (Dts.Varia bles["foun dFiles"].V alue.ToStr ing());
string delimiterInfile = fileContent.Substring(103, 1); //103 because it's zero-based
fileContent = fileContent.Replace(delimi terInfile, "*");
string delimiterInfile2 = fileContent.Substring(104, 1); //104 because it's zero-based
fileContent = fileContent.Replace(delimi terInfile2 , ":");
System.IO.File.WriteAllTex t(Dts.Vari ables["Use r::OutputF ile"].Valu e.ToString (), fileContent);
Dts.TaskResult = (int)ScriptResults.Success ;
}
}
}
I am using a ForEach Container to get the file as a variable (User::foundFiles). I am using it in a previously built DataFlow Task so I know it works just fine for that.
Within this Script it's throwing an error saying "Empty path name is not legal"
What am I missing? Can't figure out why it's not working.
Error:
Error: System.Reflection.TargetIn
at System.IO.FileStream.Init(
at System.IO.FileStream..ctor
at System.IO.StreamWriter.Cre
at System.IO.StreamWriter..ct
at System.IO.StreamWriter..ct
at System.IO.File.WriteAllTex
at ST_c71263698669469292bfc5b
--- End of inner exception stack trace ---
at System.RuntimeMethodHandle
at System.RuntimeMethodHandle
at System.Reflection.RuntimeM
at System.Reflection.RuntimeM
at System.RuntimeType.InvokeM
at System.Type.InvokeMember(S
at Microsoft.SqlServer.Dts.Ta
Code:
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Ru
using System.Windows.Forms;
namespace ST_c71263698669469292bfc5b
{
[System.AddIn.AddIn("Scrip
public partial class ScriptMain : Microsoft.SqlServer.Dts.Ta
{
#region VSTA generated code
enum ScriptResults
{
Success = Microsoft.SqlServer.Dts.Ru
Failure = Microsoft.SqlServer.Dts.Ru
};
#endregion
public void Main()
{
string fileContent = System.IO.File.ReadAllText
string delimiterInfile = fileContent.Substring(103,
fileContent = fileContent.Replace(delimi
string delimiterInfile2 = fileContent.Substring(104,
fileContent = fileContent.Replace(delimi
System.IO.File.WriteAllTex
Dts.TaskResult = (int)ScriptResults.Success
}
}
}
Are you sure the 'OutputFile' variable's value is not empty and has correct path with file name?
ASKER
At this point I'm not sure of anything and am a relative newb in Scripting. The Script was supplied by someone else. It builds just fine, but the error comes from the package execution.
What would you suggest the value be and how do I assign the path name to it?
Within the ForEach Container I have the Collection set to the proper folder path and it's instructed to collect *.txt and also include the fully qualified filename.
This is then mapped to the variable User::foundFiles (Package, String, no value)
OutputFile has been set up as User::OutputFile (Package, String, no value)
As indicated before, the foundFiles variable was previously used for a DataFlow task and worked just fine with multiple files in the directory (it goes through the directory and addresses each file independently without problem). The intention of this script is to read each file in the directory (which should be identified as the User::foundFiles variable and looped through via the ForEach Container), replace the characters located at position 103 and 104 and then output the file so that they are then available to the DataFlow Task which previously used the User::foundFiles variable but has now been reassigned to use the User:OutputFile variable.
The package is failing at the Script so it's not even getting to the output.... or at least it does not appear to be making it that far.
FYI. When looking at the Variable Mappings within the ForEach Loop Container, the Variable indicates User::foundFiles and the Index shows the number 0 (zero).
What would you suggest the value be and how do I assign the path name to it?
Within the ForEach Container I have the Collection set to the proper folder path and it's instructed to collect *.txt and also include the fully qualified filename.
This is then mapped to the variable User::foundFiles (Package, String, no value)
OutputFile has been set up as User::OutputFile (Package, String, no value)
As indicated before, the foundFiles variable was previously used for a DataFlow task and worked just fine with multiple files in the directory (it goes through the directory and addresses each file independently without problem). The intention of this script is to read each file in the directory (which should be identified as the User::foundFiles variable and looped through via the ForEach Container), replace the characters located at position 103 and 104 and then output the file so that they are then available to the DataFlow Task which previously used the User::foundFiles variable but has now been reassigned to use the User:OutputFile variable.
The package is failing at the Script so it's not even getting to the output.... or at least it does not appear to be making it that far.
FYI. When looking at the Variable Mappings within the ForEach Loop Container, the Variable indicates User::foundFiles and the Index shows the number 0 (zero).
ASKER
New info. When I comment out the line
System.IO.File.WriteAllTex t(Dts.Vari ables["Use r::OutputF ile"].Valu e.ToString (), fileContent);
the script runs successfully.
What is occurring with the OutputFile line and how to fix it?
System.IO.File.WriteAllTex
the script runs successfully.
What is occurring with the OutputFile line and how to fix it?
You need to add a value to that variable, so the script can write the files to.
try setting a local path and see what would happen.
try setting a local path and see what would happen.
ASKER
Huslayer. I set a local path on the User::OutputFile variable c:\localpath\1.txt and it works just fine.
Now, this is supposed to write to a variable, not to a fixed location. The variable is then supposed to be available to a DataFlow task. How do I make that work? I obviously don't want it to write to a new file, rather I want it available as the variable inside the Loop.
Now, this is supposed to write to a variable, not to a fixed location. The variable is then supposed to be available to a DataFlow task. How do I make that work? I obviously don't want it to write to a new file, rather I want it available as the variable inside the Loop.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
huslayer. A little bit confused. If I commit the results of the script to a variable, then that variable should be available without it being defined as a "source" by a script, shouldn't it?
I'm using this in an EDI workflow, and more specifically, using the CozyRoc EDI Source within the DataFlow task. It has the ability to utilize a variable source and so I have set it to use the User:OutputFile variable as it's source.
Obviously there's a disconnect occurring where the ControlFlow Script is not placing the information anywhere, even though it seems that it's being told to place it in the OutputFile Variable? I'm not sure why it's not committing this value anywhere? My assumption is that this Variable is supposed to just be random memory that gets committed and is available for use as a global variable but it isn't being populated by the script for some reason.
Anyways. Excellent link and I have bookmarked it for future use, but as the EDI Source component can't receive any inputs in the normal fashion, I can't connect it to a Script Source. I strictly have to use the variable.
I'm using this in an EDI workflow, and more specifically, using the CozyRoc EDI Source within the DataFlow task. It has the ability to utilize a variable source and so I have set it to use the User:OutputFile variable as it's source.
Obviously there's a disconnect occurring where the ControlFlow Script is not placing the information anywhere, even though it seems that it's being told to place it in the OutputFile Variable? I'm not sure why it's not committing this value anywhere? My assumption is that this Variable is supposed to just be random memory that gets committed and is available for use as a global variable but it isn't being populated by the script for some reason.
Anyways. Excellent link and I have bookmarked it for future use, but as the EDI Source component can't receive any inputs in the normal fashion, I can't connect it to a Script Source. I strictly have to use the variable.
how big is the contents of the file?
I'm not familiar with the cozyroc but you could ask for their support or read on what type of variable is supported: string, ojbect or even text
I'm not familiar with the cozyroc but you could ask for their support or read on what type of variable is supported: string, ojbect or even text
ASKER
file contents are variable. 1K up to 300K or so.
Sent an email to cozyRoc, but not sure anything gets past the point that I assume the code of this line -----
System.IO.File.WriteAllTex t(Dts.Vari ables["Use r::OutputF ile"].Valu e.ToString (), fileContent);
is telling the system to write the values within fileContent to the Variable OutputFile, correct?
If it's a variable, it should not need a fixed location, right?
If it's a variable, it's committed to memory rather than a physical location?
I'm missing something here, but I just don't know enough to know what it is. I am stuck on the thought though that the variable is not a fixed value and should not need to be.
Sent an email to cozyRoc, but not sure anything gets past the point that I assume the code of this line -----
System.IO.File.WriteAllTex
is telling the system to write the values within fileContent to the Variable OutputFile, correct?
If it's a variable, it should not need a fixed location, right?
If it's a variable, it's committed to memory rather than a physical location?
I'm missing something here, but I just don't know enough to know what it is. I am stuck on the thought though that the variable is not a fixed value and should not need to be.
Some explanation:
System.IO.File.WriteAllTex t(Dts.Vari ables["Use r::OutputF ile"].Valu e.ToString (), fileContent);
That line is creating a new file on the file system. The content of that file is the content of the fileContent variable. The path and location of the file is provided through the OutputFile package variable.
To be able to make the output file name dynamic, you should put only the path as value for the package variable, and pass another value into the script that can help you create a filename. The User::foundFiles which you use in your loop would make a good candidate.
Possibly you'll need to do some further string manipulation, depending on how you set up the loop: "name only", "fully qualified", "name and extension"?
Assuming "name only", you could do this:
string outFile = Dts.Variables["User::Outpu tFile"].Va lue.ToStri ng() + "\\" + Dts.Variables["User::found Files"].Va lue.ToStri ng() + ".txt";
System.IO.File.WriteAllTex t(outFile, fileContent);
The above assumes your OutputFile package var does not end in a backslash and that foundFiles contains the name of the file without extension.
System.IO.File.WriteAllTex
That line is creating a new file on the file system. The content of that file is the content of the fileContent variable. The path and location of the file is provided through the OutputFile package variable.
To be able to make the output file name dynamic, you should put only the path as value for the package variable, and pass another value into the script that can help you create a filename. The User::foundFiles which you use in your loop would make a good candidate.
Possibly you'll need to do some further string manipulation, depending on how you set up the loop: "name only", "fully qualified", "name and extension"?
Assuming "name only", you could do this:
string outFile = Dts.Variables["User::Outpu
System.IO.File.WriteAllTex
The above assumes your OutputFile package var does not end in a backslash and that foundFiles contains the name of the file without extension.
ASKER
turns out the source needs to reference a fixed file and not a variable. I dumped the variable to a fixed temp file and it overwrites each time as the loop works its way through.
Thanks for the help
Thanks for the help