CODEWITHSUNDEEP
c#sql-serverssis
A. Luhman
A. Luhman

Reputation: 33

Error being thrown by SSIS script task

I am working on upgrading 2005 SSIS packages to 2016. I have upgraded this package but when I try to run it, it is breaking on the script task in 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 being used to add a CRLF onto the end of the data in the file.I have added breakpoints to the code and I'm seeing it is breaking at the line using (StreamWriter w = file.AppendText(fileName)). I am receiving the following error.

enter image description here

Here are the exception details:

System.ArgumentException was unhandled 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, FileOptions options, SECURITY_ATTRIBUTES secAttrs, String msgPath, Boolean bFromProxy, Boolean useLongPath, Boolean checkHost) at System.IO.FileStream..ctor(String path, FileMode mode, FileAccess access, FileShare share, Int32 bufferSize, FileOptions options, String msgPath, Boolean bFromProxy, Boolean useLongPath, Boolean checkHost) at System.IO.StreamWriter.CreateFile(String path, Boolean append, Boolean checkHost) at System.IO.StreamWriter..ctor(String path, Boolean append, Encoding encoding, Int32 bufferSize, Boolean checkHost) at System.IO.StreamWriter..ctor(String path, Boolean append) at System.IO.File.AppendText(String path) at ST_9752d9eb585d4a4d97a334ef01ccf313.ScriptMain.Main() in c:\Users\aluhman\AppData\Local\Temp\2\Vsta\5c1672d48682401d852b1b44649f951b\ScriptMain.cs:line 31 InnerException:

This was all working in 2005 and this is a new error I'm seeing now in 2016.

Upvotes: 3

Views: 947

Answers (1)

Dave C
Dave C

Reputation: 7402

You can't open every file at once, instead you have to iterate over them one by one:

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();
            }
     }
}

Upvotes: 2

Related Questions