SSIS 2008 Script Task VB.NET - Removing header/Footer & special character in cvs files

Question

Hi Dear Fellows,

I created a package in which I have a script task that would remove the header, footer and special character in cvs files dynamically. This task in inside of a for each loop container which will process every file from a directory. When I run the package the script task throw an error message as below.

Does anyone have any idea why the script can not find the file? Please let me know what I have done wrong in the code. I truly appreciate your help on this.

This is the code:

      Public Sub Main()

            '

        ‘Dim oFile As System.IO.File

        Dim oRead As System.IO.StreamReader

        Dim oWrite As System.IO.StreamWriter

        Dim strCurrentLine, InputOutputFileName As String

       

InputOutputFileName = Dts.Variables("User::UncompressedFileName").Value.ToString

        oRead = IO.File.OpenText(InputOutputFileName)

        oWrite = IO.File.CreateText(InputOutputFileName & "1")

        Do While oRead.EndOfStream <> True

            strCurrentLine = oRead.ReadLine

            strCurrentLine = Replace(strCurrentLine, Chr(34) & "," & Chr(34), Chr(34) & "|" & Chr(34))

            strCurrentLine = Replace(strCurrentLine, "---", "")

If InStr(strCurrentLine, "Report Fields") = 0 Then

                oWrite.WriteLine(strCurrentLine)

            End If

        Loop

        oRead.Close()

        oWrite.Close()

        Dim fi As New IO.FileInfo(InputOutputFileName & "1")

        Dim fo As New IO.FileInfo(InputOutputFileName)

        fo.Delete()

        fi.MoveTo(InputOutputFileName)

            '

            Dts.TaskResult = ScriptResults.Success

      End Sub

Error Message:

            SSIS package "MyNewPackage(1).dtsx" starting.

Error: 0x1 at delete footer and replace text2: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.IO.FileNotFoundException: Could not find file 'C:\Program Files (x86)\Microsoft Visual Studio 9.0\Common7\IDE\MyFileName_2012-05-17-12-50-27_35271665.csv'.

File name: 'C:\Program Files (x86)\Microsoft Visual Studio 9.0\Common7\IDE\MyFileName_2012-05-17-12-50-27_35271665.csv'

   at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath)

   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, String msgPath, Boolean bFromProxy)

   at System.IO.FileStream..ctor(String path, FileMode mode, FileAccess access, FileShare share, Int32 bufferSize, FileOptions options)

   at System.IO.StreamReader..ctor(String path, Encoding encoding, Boolean detectEncodingFromByteOrderMarks, Int32 bufferSize)

   at System.IO.StreamReader..ctor(String path, Boolean detectEncodingFromByteOrderMarks)

   at System.IO.File.OpenText(String path)

   at ST_b5b2e0f0d38c4c2d89ac7d74729584da.vbproj.ScriptMain.Main()

   --- End of inner exception stack trace ---

   at System.RuntimeMethodHandle._InvokeMethodFast(Object target, Object[] arguments, SignatureStruct& sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner)

   at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture, Boolean skipVisibilityChecks)

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

Task failed: delete footer and replace text2

Warning: 0x80019002 at Foreach Loop Container: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED.  The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

SSIS package "OrenciaDFADaily (1).dtsx" finished: Failure.

The program '[8788] MyNewPackage(1).dtsx: DTS' has exited with code 0 (0x0).

Thanks so much!

Joe

Answer 1

Hi Joe,

It seems the .csv file is not successfully created, please make sure Dts.Variables("User::UncompressedFileName").Value.ToString will return a valid path. Moreover, we have a forum SQL Server Integration Services(http://social.msdn.microsoft.com/Forums/en-AU/sqlintegrationservices/threads) which should be better for this question.

If I misunderstood anything, please feel free and let me know.

Best regards,

---

Shanks Zen
MSDN Community Support | Feedback to us