Error while executing SSIS script task
I am working on updating SSIS 2005 packages to 2016. I have updated this package, but when I try to run it, it breaks down into a script task on the control flow.
#region Namespaces
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.IO;
#endregion
namespace ST_9752d9eb585d4a4d97a334ef01ccf313
{
[Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{
public void Main()
{
string fileName;
fileName = Dts.Variables["User::Source_File_And_Path"].Value.ToString();
using (StreamWriter w = File.AppendText(fileName))
{
w.Write("\r\n");
w.Close();
}
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
}
}
This script task is used to add CRLF to the end of the data in the file. I added breakpoints to the code and I can see that it breaks to a line using (StreamWriter w = file.AppendText(fileName))
. I am getting the following error.
Here's the details of the exception:
System.ArgumentException was not handled by user code HResult = -2147024809 Message = Illegal characters in path. Source = mscorlib StackTrace:
at System.Security.Permissions.FileIOPermission.EmulateFileIOPermissionChecks (String fullPath) at System.IO.FileStream.Init (String path, FileMode mode, FileAccess access, Int32 rights, Boolean useRights, FileShare share, Int32 bufferSize options, FileOptionATs, SECURITY_Init secAttrs, String msgPath, Boolean bFromProxy, Boolean useLongPath, Boolean checkHost) to System.IO.FileStream..ctor (String path, FileMode mode, FileAccess access, FileShare share, Int32 bufferSize options, FileOptions options, String msgPath, Boolean bFromProxy, boolean useLongPath, boolean checkHost) in System.IO.StreamWriter.CreateFile (String path, Boolean append, Boolean checkHost) in System.IO.StreamWriter..ctor (String path, Boolean append, Encoding encoding, Int32 bufferSize, Boolean checkHost) in System.IO.StreamWriter ..ctor (String path, Boolean append) to System.IO.File.AppendText (String path) to ST_9752d9eb585d4a4d97a334ef01ccf313.ScriptMain.Main () to c: \ Users \ aluhman \ AppData \ Local \ Temp \ 2 \ Vsta \ 5c1672d4864 cs: line 31 InnerException:
This all worked in 2005, and this is a new bug I see now in 2016.
source to share
You cannot open every file at once, instead, you have to iterate over them one at a time:
Something like:
string fileName = Dts.Variables["User::Source_File_And_Path"].Value.ToString();
string [] fileEntries = Directory.GetFiles(Path.GetFullPath(fileName));
foreach (string f in fileEntries)
{
if (Path.GetExtension(f).ToUpper()==".TXT".ToUpper() && f.StartsWith("Customers_")==true)
{
using (StreamWriter w = File.AppendText(f))
{
w.Write("\r\n");
w.Close();
}
}
}
source to share